Calculating Golf Handicap - Question

[PTT, Inc.]

I am putting together an Excel spreadsheet to calculate my golf scores
(personal use only). I know how to do the simple calculation to get the
scores and averages needed for a handicap, but I don't know the one tough
part:

"I need to be able to select the 10 lowest of the last 20 scores for my
calculation. I have no idea how to go about this and figured it must have
been asked here before." Not sure if this is an OFFSET (which I have used
before for dynamic charting) or INDEX. My function knowledge is rather
limited.

Any ideas? Any links? TIA!

Bill

 

[Jason Morin]

Assuming your scores start in A1 and move down column A,
you could use something like:

=SMALL(OFFSET(A1,COUNT(A:A)-1,,-20),ROW(INDIRECT("1:10")))

to create an array of the 10 smallest numbers of last 20.
When using this, say, with AVERAGE, be sure to press
ctrl/shift/enter.

=AVERAGE(SMALL(OFFSET(A1,COUNT(A:A)-1,,-20),ROW(INDIRECT
("1:10"))))

Also note that this formula will error out if there are
less than 20 scores.

HTH
Jason
Atlanta, GA

 

[PTT, Inc.]

Thanks Jason, I'll try it out.

Bill

Assuming your scores start in A1 and move down column A,
you could use something like:

=SMALL(OFFSET(A1,COUNT(A:A)-1,,-20),ROW(INDIRECT("1:10")))

to create an array of the 10 smallest numbers of last 20.
When using this, say, with AVERAGE, be sure to press
ctrl/shift/enter.

=AVERAGE(SMALL(OFFSET(A1,COUNT(A:A)-1,,-20),ROW(INDIRECT
("1:10"))))

Also note that this formula will error out if there are
less than 20 scores.

HTH
Jason
Atlanta, GA

 

[Steve S.]

It isn't pretty. . .but I've made a bit of a workaround.
I'm sure someone else will have a cleaner function for it
shortly. But here goes. ..
Insert a column before your scores and put the following
function next to last 20 scores: =RANK(C12,$C$12:$C$31)
Above your scores, the following will do the sum of scores
which rank below 11. =SUMIF(B12:B31,"<11",C12:C31)
I'm not sure how you wish to work with the sum from
there. . .but I hope this helps for the meanwhile.

 

[Biff]

Hi Bill,

Check the USGA site for specific details on handicapping.

http://www.usga.org/handicap/index.html

The actual calculations are relatively easy but
determining what criteria to use is somewhat complicated.

Biff

 

[L. Howard Kittle]

Hi Bill,

Using phony golf scores of 1 through 20 in A1:A20, this formula returns
55... the sum of 1 through 10 which are the 10 lowest scores.

=SUM(SMALL(A1:A20,{1,2,3,4,5,6,7,8,9,10}))

If the procedure is average those 10 scores then these formulas will do
that, returning 5.5.

=SUM(SMALL(A1:A20,{1,2,3,4,5,6,7,8,9,10}))/10
=AVERAGE(SMALL(A1:A20,{1,2,3,4,5,6,7,8,9,10}))

HTH
Regards,
Howard

 

[PTT, Inc.]

Thanks Jason, this worked great. Now I'd like to throw a wrench into the
calculation and try this:

Take the lowest 10 of the last 20 scores (just as this one does), throw out
the lowest 1 of those 10, then average the remaining 9!

Possible?

Bill

 

[L. Howard Kittle]

Hi Bill,

Again using phony golf scores of 1 through 20 in A1:A20, this formula
returns
54... the sum of 2 through 9.

=SUM(SMALL(A1:A20,{2,3,4,5,6,7,8,9,10}))

To average those 9 scores then these formulas will do
that, returning 6.

=SUM(SMALL(A1:A20,{2,3,4,5,6,7,8,9,10}))/9
=AVERAGE(SMALL(A1:A20,{2,3,4,5,6,7,8,9,10}))

HTH
Regards,
Howard

 

[PTT, Inc.]

Howard,

Thanks for the reply, but I need this to be dynamic since I will continue to
add more scores. That is why I decided on using (from Jason):

=AVERAGE(SMALL(OFFSET(A1,COUNT(A:A)-1,,-20),ROW(INDIRECT("1:10"))))

Is this possible? TIA!

Bill

 

[Biff]

Hi Bill,

Just a slight change in the formula. Change ROW(INDIRECT
("1:10") to ROW(INDIRECT("2:10")

Biff

 

[PTT, Inc.]

That's too easy! I should have figured that one out.

THANKS!

Bill

 

[Kevin S.]

Wow! Did I get lucky. I'm doing exactly the same thing as PTT but will be doing it for a group of around 10 guys. Since I'm very new to Excel, PTT posted pretty much the exact question I was going to post, and you guys answered it perfectly. Thanks for posting such great answers. Saved me a headache !

 

[EGolfer4]

I like the formula Jason posted
=AVERAGE(SMALL(OFFSET(A1,COUNT(A:A)-1,,-20),ROW(INDIRECT
("1:10"))))

However my data is in rows rather than columns and I'm having a har
time changing the formula to get it to work. My starts in column "C
through "L" and then extends to "AD" as we add scores to the league.

Any help?

PS What exactly is the OFFSET command doing in the formula