golf handicap

[Guest]

I'm trying to set up an excel spreadsheet to calculate my handicap. The
tricky part, for me anyway, is I need to average the 10 best scores out of
the last 20 played. Any idea? Thanks.

 

[Dave O]

This is a little chunky, but here goes. Use the LARGE() function,
which allows you to pick out the Nth largest number in an array. So if
your 20 scores are in A1:A20, it would look like this:
=AVERAGE(LARGE(A1:A20,1)+LARGE(A1:A20,2)+LARGE(A1:A20,3)+
....LARGE(A1:A20,10))

 

[Dave O]

Mistake! The AVERAGE function doesn't work in that context. Divide by
10 instead.

 

[Ron Rosenfeld]

I'm trying to set up an excel spreadsheet to calculate my handicap. The
tricky part, for me anyway, is I need to average the 10 best scores out of
the last 20 played. Any idea? Thanks.

There are several ways.

Assume your scores are in A2:An.

In A21, enter the formula:

=IF(A21="","",AVERAGE(SMALL(A2:A21,ROW(INDIRECT("1:10")))))

This is an array formula so must be entered by holding down <ctrl><shift> as
you hit <enter>.

Then copy/drag the formula down as far as necessary.


--ron

 

[Biff]

Hi!

How is the data layed out? Down a column? Across a row? Are there any blank
cells in the range? Any zeros? What if there aren't 20 scores?

Biff