Golf Handicap

[afchris]

Being that I am (1) too cheap to fork out $30 for software that doe
this for you, and (2) interested in learning more about Excel, I hav
been trying to create a spreadsheet that will calculate my gol
handicap. The calculation for getting your handicap calls for usin
the handicap differential of your 20 most recent rounds of golf, and o
those 20, averaging the 10 lowest and then multiplying that average b
0.96. I've attached the spreadsheet that I have created and would b
eternally grateful to anyone who can help.

So far, this is what I have: =AVERAGE(N2:N35)*0.96

But I need to filter the Date to the 20 most recent, and then from tha
range take the 10 lowest handicap diffentials.

Thanks

Attachment filename: handicap.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=46544

 

[Jason Morin]

=0.96*AVERAGE(SMALL(OFFSET(N2,COUNT(N2:N1000)-1,,-20),ROW
(INDIRECT("1:10"))))

Insert this and press ctrl/shift/enter (array formula).

HTH
Jason
Atlanta, GA

 

[Bernie Deitrick]

afchris,

If you have dates associated with your scores, say in column M of the same
row, then the array formula (entered with Ctrl-Shift-Enter)

=AVERAGE(IF(IF(M2:M35>=LARGE(M2:M35,20),N2:N35)<=SMALL(IF(M2:M35>=LARGE(M2:M
35,20),N2:N35),10),IF(M2:M35>=LARGE(M2:M35,20),N2:N35)))*0.96

All that should be on one line, so watch any line feed characters inserted
by newsreaders.

If you don't have dates, but just put your latest score at the bottom, then
you can change this to (still array entered):

=AVERAGE(IF(OFFSET(N2,COUNTA(N:N)-21,0,20,1)<=SMALL(OFFSET(N2,COUNTA(N:N)-21
,0,20,1),10),OFFSET(N2,COUNTA(N:N)-21,0,20,1)))*0.96

HTH,
Bernie
MS Excel MVP

 

[Frank Kabel]

Hi
if your column N stores your handicap differentials you may use the
following formulas:
Note: The resulting cells have to be placed in a different column than
column N!

1. To get the range with the 20 most recent rounds (note: this will
work only if you have at least 20 entries)
=OFFSET($N$2,COUNTA($N$2:$N$1000)-1,0,-20)

2. To get the sum of the 10<lowest numbers try
=SUMPRODUCT(--(OFFSET($N$2,COUNTA($N$2:$N$1000)-1,0,-20)<SMALL(OFFSET($
N$2,COUNTA($N$2:$N$1000)-1,0,-20),11)),OFFSET($N$2,COUNTA($N$2:$N$1000)
-1,0,-20))

3. To get the average for these 10 numbers use
=SUMPRODUCT(--(OFFSET($N$2,COUNTA($N$2:$N$1000)-1,0,-20)<SMALL(OFFSET($
N$2,COUNTA($N$2:$N$1000)-1,0,-20),11)),OFFSET($N$2,COUNTA($N$2:$N$1000)
-1,0,-20)) / 10

4. for the handicap use
=(SUMPRODUCT(--(OFFSET($N$2,COUNTA($N$2:$N$1000)-1,0,-20)<SMALL(OFFSET(
$N$2,COUNTA($N$2:$N$1000)-1,0,-20),11)),OFFSET($N$2,COUNTA($N$2:$N$1000
)-1,0,-20)) / 10)*0.96

 

[Guest]

http://www.elmoreusa.com/cj/files.htm

 

[David McRitchie]

Off topic reply concerning background of HTML page.

I'm sure the information on your spreadsheet will be useful to a lot
of people and they will be able to find it with a Google Groups
search for "Golf Handicap"..

I find the background images make the web page very hard to
read so there are two solutions the quickest is
Ctrl+A to select all which makes it easier to read

The other is to wipe out background images or the wipe out
presentation effects with a bookmarklet (JavaScript).
http://www.mvps.org/dmcritchie/ie/bookmarklets.htm

Since it is your page and you are into effects, you could
include a link on your own page to eliminate the effects
right on your page. So that you have both the effects and
the ability to remove them. (after the first </h3> )

<center><A HREF="javascriptfunction(){var
H=[%22bgcolor%22,%22bgColor%22,%22background%22,%22color%22,%22align%22,%22text%22,%22alink%22,%22vlink%22],Y={FONT:1,CENTER:1},d=[]
,p; function R(N){var a,x,i,t; if(t=N.tagName){ t=t.toUpperCase(); for (i=0;a=H;++i)if(N.getAttribute(a))N.removeAttribute(a);
for(i=0;x=N.childNodes;++i)R(x); if (Y[t])d.push(N); } } R(document.documentElement); for (i=0;N=d;++i) { p=N.parentNode;
while(N.firstChild)p.insertBefore(N.firstChild,N); p.removeChild(N); } })()">zap presentational html</A></center>

The above would be from Jesse Ruderman at Squarefree.com

Don't know why I do not see the original message, and it is
probably too early to look at Google Groups yet (wait 12 hours) at
http://google.com/groups?threadm=65237C09-0296-4FAA-9D30-7A3B7B3D2EE7@microsoft.com