I want to use the MATCH function with the AVERAGE function but I .
 

I have two columns one is time and the other force values. I want to look for
two diferent times and get the average of the forces in that range of time. I
thought I could generalize this for several spreedsheets that I have. I
believe if I use the MATCH function to get the row position of the different
times and then simply use the average function with MATCH may work straight
away. I get a problem because the format average somehow does not recognice
MATCH. For instance my formule looks like:
forceaverage=AVERAGE(RMATCH(time1,R1C1:R5000C1,0)C 3:RMATCH(time2,R1C1:R5000C1,0)C3).
time1 and time2 are my time values and R is row and C is column (the
absolute format used by excel). C3 is the column were the forces are. C1 is
the column of the time.R5000 is the last row value.

Bit confused by your formula, but in principle you can use

=SUMPRODUCT(--(A10:A27=C10),--(A10:A27<C11),B10:B27)/SUMPRODUCT(--(A10:A27
=C10),--(A10:A27<C11))

which (may) translate to

=SUMPRODUCT(--(A1:A5000=time1),--(A1:A5000<time2),C1:C5000)/SUMPRODUCT(--(A
1:A5000=time1),--(A1:A5000<time2))

in your example
--
HTH

Bob Phillips

"Miguel" wrote in message
...
I have two columns one is time and the other force values. I want to look
for
two diferent times and get the average of the forces in that range of
time. I
thought I could generalize this for several spreedsheets that I have. I
believe if I use the MATCH function to get the row position of the
different
times and then simply use the average function with MATCH may work
straight
away. I get a problem because the format average somehow does not
recognice
MATCH. For instance my formule looks like:

forceaverage=AVERAGE(RMATCH(time1,R1C1:R5000C1,0)C 3:RMATCH(time2,R1C1:R5000C
1,0)C3).
time1 and time2 are my time values and R is row and C is column (the
absolute format used by excel). C3 is the column were the forces are. C1
is
the column of the time.R5000 is the last row value.

THANKS,
I DID NOT KNOW THAT FUNCTION BUT IT WORKS JUST RIGHT
CHEERS

"Bob Phillips" wrote:

Bit confused by your formula, but in principle you can use

=SUMPRODUCT(--(A10:A27=C10),--(A10:A27<C11),B10:B27)/SUMPRODUCT(--(A10:A27
=C10),--(A10:A27<C11))

which (may) translate to

=SUMPRODUCT(--(A1:A5000=time1),--(A1:A5000<time2),C1:C5000)/SUMPRODUCT(--(A
1:A5000=time1),--(A1:A5000<time2))

in your example
--
HTH

Bob Phillips

"Miguel" wrote in message
...
I have two columns one is time and the other force values. I want to look
for
two diferent times and get the average of the forces in that range of
time. I
thought I could generalize this for several spreedsheets that I have. I
believe if I use the MATCH function to get the row position of the
different
times and then simply use the average function with MATCH may work
straight
away. I get a problem because the format average somehow does not
recognice
MATCH. For instance my formule looks like:

forceaverage=AVERAGE(RMATCH(time1,R1C1:R5000C1,0)C 3:RMATCH(time2,R1C1:R5000C
1,0)C3).
time1 and time2 are my time values and R is row and C is column (the
absolute format used by excel). C3 is the column were the forces are. C1
is
the column of the time.R5000 is the last row value.