Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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. |
#2
![]() |
|||
|
|||
![]()
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. |
#3
![]() |
|||
|
|||
![]()
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Using The Average Function if a cell has NA | Excel Worksheet Functions | |||
Using the average function | Excel Worksheet Functions | |||
Match Function arguments | Excel Worksheet Functions | |||
How do I nesting subtotal function within average function in Exc | Excel Worksheet Functions | |||
Using Average function when number is zero | Excel Worksheet Functions |