'auto find average of 4 fastest times'

[Ditchy]

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

 

[Ditchy]

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

 

[Mike H]

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]

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.






- Show quoted text -

Thanks Mike, much appreciated.
Is there also a way if there are less than 4 time entered?
regards
Ditchy