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

M

Miguel

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)C3: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.
 
B

Bob Phillips

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top