Excel function for golf average

[michaelt721]

Hi,


I run a golf league and have a score sheet in excel to help figure out our handicaps.

I am looking for a function or formula that will look back at the last 6 weeks scores (not counting the zero's OR blanks) and drop the highest 2, then calculate an average of the other 4.


Example: (also sample data file attached)


--A----B---C---D---E---F---G---H---I---J---K---L---M---N---O---P---Q---R---S

Mike--44--41--43--39--43--0--44--45--41-46--41


If we were to drop weeks I and K, the average score would be 43+44+41+41=169 169/4=42.25


We golf 18 weeks, so I'd like the formula to include all 18 weeks so I don't have to keep adjusting the formula each week.


Thanks in advance!

Mike

michaelt721@yahoo.com

 

[barry houdini]

You can use this array formula in T2

=AVERAGE(LARGE(IF(COLUMN(B2:S2)>=LARGE(IF(ISNUMBER(B2:S2)*(B2:S2>0),COLUMN(B2:S2)),6),B2:S2),{6,5,4,3}))

confirm with CTRL+SHIFT+ENTER and copy down column.

There will need to be at least 6 valid scores otherwise you will get an error - do you want the formula to handle 5 scores (discarding the highest?) or 3 scores (averaging them all?).

This version will do the following:

If there are no qualifying scores - returns a blank
If there are 1 to 4 qualifying scores - averages all qualifying scores
If there are 5 qualifying scores - averages the 4 lowest of those 5
If there are 6 or more qualifying scores - averages the 4 lowest of the latest 6 as per your original request

=CHOOSE(MATCH(COUNTIF(B2:S2,">0"),{0,1,5,6}),"",AVERAGEIF(B2:S2,">0"),AVERAGE(LARGE(B2:S2,{5,4,3,2})),AVERAGE(LARGE(IF(COLUMN(B2:S2)>=LARGE(IF(ISNUMBER(B2:S2)*(B2:S2>0),COLUMN(B2:S2)),6),B2:S2),{6,5,4,3})))

If you want the rules to be different I can adjust accordingly

 

[Hui]

Mike,


The orange cells are averaged, Pink ignored

In T2:
=AVERAGE(LARGE(AVERAGEIFS($B2:$S2,$B$1:$S$1,LARGE(IF($B2:$S2>0,$B$1:$S$1),{1,2,3,4,5,6})),{3,4,5,6})) Ctrl+Shift+Enter
Copy down

to understand this have a read of:
http://chandoo.org/wp/2013/10/03/formula-forensics-no-035-average-the-last-3-values-greater-than-0/

See attached file:

Instead of putting .. into cells just leave them blank

 

[michaelt721]

Thank you so much for your help and time, however, after copying the formula into cell T2, then pressing Ctrl+Shift+Enter, I get #Value in that cell.

Any thoughts?

 

[barry houdini]

You need to select the formula first (you can press F2 to do that) and then hold down CTRL and SHIFT while pressing ENTER, if you do it correctly you get curly braces like { and } around the formula in the formula bar.

 

[michaelt721]

That did it!

Thanks!