Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
'auto find average of 4 fastest times'
Hello
could someone please help me with this 'auto find average of 4 fastest times' question, formula is and works fine. =AVERAGE(IF('previous race results'!$A$1:$A$861=B36,'previous race results'!$B$1:$B$861)) where B36 is the persons name column and A1 to A861 have the times, currently I delete any more than 4 times for each person (very time consuming). Is there a way to do this average of the 4 fastest times regardless of the number of times in A1 to A861 for each person. thanks in anticipation Ditchy Ballarat Australia |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
'auto find average of 4 fastest times'
On Feb 12, 8:05*pm, Ditchy wrote:
Hello could someone please help me with this 'auto find average of 4 fastest times' question, formula is and works fine. =AVERAGE(IF('previous race results'!$A$1:$A$861=B36,'previous race results'!$B$1:$B$861)) where B36 is the persons name column and A1 to A861 have the times, currently I delete any more than 4 times for each person (very time consuming). Is there a way to do this average of the 4 fastest times regardless of the number of times in A1 to A861 for each person. thanks in anticipation Ditchy Ballarat Australia correction to my post B1 to B861 have the times A1 to A861 is the persons name column regards Ditchy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
'auto find average of 4 fastest times'
Hi,
Try this ARRAY formula. see below on how to enter it. Lookup name is in C1 =AVERAGE(SMALL(IF(A1:A861=C1,B1:B861),{1,2,3,4})) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Ditchy" wrote: On Feb 12, 8:05 pm, Ditchy wrote: Hello could someone please help me with this 'auto find average of 4 fastest times' question, formula is and works fine. =AVERAGE(IF('previous race results'!$A$1:$A$861=B36,'previous race results'!$B$1:$B$861)) where B36 is the persons name column and A1 to A861 have the times, currently I delete any more than 4 times for each person (very time consuming). Is there a way to do this average of the 4 fastest times regardless of the number of times in A1 to A861 for each person. thanks in anticipation Ditchy Ballarat Australia correction to my post B1 to B861 have the times A1 to A861 is the persons name column regards Ditchy . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
'auto find average of 4 fastest times'
On Feb 12, 11:53*pm, Mike H wrote:
Hi, Try this ARRAY formula. see below on how to enter it. Lookup name is in C1 =AVERAGE(SMALL(IF(A1:A861=C1,B1:B861),{1,2,3,4})) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Ditchy" wrote: On Feb 12, 8:05 pm, Ditchy wrote: Hello could someone please help me with this 'autofindaverageof4fastest times' question, formula is and works fine. =AVERAGE(IF('previous race results'!$A$1:$A$861=B36,'previous race results'!$B$1:$B$861)) where B36 is the persons name column and A1 to A861 have thetimes, currently I delete any more than4timesfor each person (very time consuming). Is there a way to do thisaverageof the4fastesttimes regardless of the number oftimesin A1 to A861 for each person. thanks in anticipation Ditchy Ballarat Australia correction to my post B1 to B861 have thetimes A1 to A861 is the persons name column regards Ditchy .- Hide quoted text - - Show quoted text - Thanks Mike, much appreciated. Is there also a way if there are less than 4 time entered? regards Ditchy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use Excel to find an average of times? | Excel Discussion (Misc queries) | |||
how to average times in a column | Excel Worksheet Functions | |||
Need to average clock times | Excel Worksheet Functions | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
average of times | Excel Discussion (Misc queries) |