Getting 3 lowest golf scores - Help

[duplinguy]

I am keeping score for my company’s golf league. There are 6 teams with around 10 players on each team. Team scores need to be the AVERAGE of the 3 lowest scores per team (average of 36,37,38 for example below).
Player 1 = 36
Player 2 = 37
Player 3 = 38
Player 4 = 38

I use this formula to determine which scores to use: '=IF(C16="","",IF(RANK(C16,C14:C25,1)<4,"Use Me!","-")). I then use a SUMIF and COUNTIF to determine the average Team Score. I run into problems when 2 or more players have the same score. The RANK formula gives Player 3 and Player 4 the rank of 3 thus counting both their scores. Is there a way to average the 3 lowest scores, even if some or all are the same?

I know this sounds confusing, I will send my file to clear up any questions.

 

[Mark W.]

=AVERAGE(SMALL(C14:C25,{1,2,3}))
This message was edited by Mark W. on 2002-04-26 08:37

 

[duplinguy]

Outstanding!!! Works great! Thank you for the help.