If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Bit confused by your formula, but in principle you can use
=SUMPRODUCT(--(A10:A27=C10),--(A10:A27C11),B10:B27)/SUMPRODUCT(--(A10:A27 =C10),--(A10:A27C11)) which (may) translate to =SUMPRODUCT(--(A1:A5000=time1),--(A1:A5000time2),C1:C5000)/SUMPRODUCT(--(A 1:A5000=time1),--(A1:A5000time2)) 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:A27C11),B10:B27)/SUMPRODUCT(--(A10:A27 =C10),--(A10:A27C11)) which (may) translate to =SUMPRODUCT(--(A1:A5000=time1),--(A1:A5000time2),C1:C5000)/SUMPRODUCT(--(A 1:A5000=time1),--(A1:A5000time2)) 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Average function | Karen | Worksheet Functions | 35 | July 12th, 2004 05:16 AM |
MATCH function with text - confused | genzu | Worksheet Functions | 1 | June 21st, 2004 12:00 AM |
Match Function | Joe Gieder | Worksheet Functions | 3 | February 18th, 2004 08:45 PM |
Match function problem | Tom | Worksheet Functions | 0 | September 22nd, 2003 09:05 PM |