golf handicap
 

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.

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))

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

On Sun, 3 Jul 2005 18:27:02 -0700, "Phineus"
wrote:

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

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

"Phineus" wrote in message
...
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.