Attn: Golfers! Need Help Calculating Hole-by-Hole Net Handicaps

[Scott]

This seems like it would be very easy, but apparently it's either not, or I'm
not thinking clearly.

Cells A2:A19 have the Hole # followed by the hole handicap (ranked 1 through
18, 1 being most difficult): So, in this example, let's say that the
following information is true for cells A2:A19:

1- HCP 11
2- HCP 17
3- HCP 3
4- HCP 1
5- HCP 13
6- HCP 7
7- HCP 5
8- HCP 15
9- HCP 9
10- HCP 4
11- HCP 18
12- HCP 2
13- HCP 10
14- HCP 16
15- HCP 8
16- HCP 14
17- HCP 6
18- HCP 12

Now, let's assume that our sample golfer has a 27 handicap, meaning he will
receive 1 shot on holes 1-18, and receive 1 shot on the HCP holes ranked 1-9.
So, in our example, he would receive 1 shot on #1, 1 shot on #2, 2 shots on
#3, 2 shots on #4, and so on...

What formula would I use to calculate the number of shots player would
receive on each hole? Any ideas?

 

[Pete_UK]

If you just had numbers in those cells and did away with the - HCP so
that you have this starting in A1:

Hole Handicap
1 11
2 17
3 3
4 1
5 13
6 7
7 5
8 15
9 9
10 4
11 18
12 2
13 10
14 16
15 8
16 14
17 6
18 12

then you could have this formula in C2:

=IF(B2<=9,2,1)

and copy this down to give you a series of 1 and 2 against each hole.

I'm not a golfer, so I don't know how the handicap system works, but
if someone had a handicap of 24 and this meant they got the extra shot
against the 6 hardest holes, then put your player's handicap in E1 and
change the formula in C2 to this:

=IF(B2<=E$1-18,2,1)

Hope this helps.

Pete

 

[Fred Smith]

Golfer's handicap in A1, descriptions in a2:a19
=INT(A$1/18)+(MOD(A$1,18)>=--RIGHT(A2,2))
Copy down

As an aside, I would put the hole numbers and their handicaps in separate
cells. They would be easier to work with.

Regards,
Fred.

 

[Fred Smith]

Hi Pete,

You actually need the Mod and Int functions, because it's possible to get,
for example, 3 strokes on a hole if your handicap is over 36.

Golf and VBA have a lot in common -- equally difficult for a layman to
understand.

Regards,
Fred.

If you just had numbers in those cells and did away with the - HCP so
that you have this starting in A1:

Hole Handicap
1 11
2 17
3 3
4 1
5 13
6 7
7 5
8 15
9 9
10 4
11 18
12 2
13 10
14 16
15 8
16 14
17 6
18 12

then you could have this formula in C2:

=IF(B2<=9,2,1)

and copy this down to give you a series of 1 and 2 against each hole.

I'm not a golfer, so I don't know how the handicap system works, but
if someone had a handicap of 24 and this meant they got the extra shot
against the 6 hardest holes, then put your player's handicap in E1 and
change the formula in C2 to this:

=IF(B2<=E$1-18,2,1)

Hope this helps.

Pete

 

[Scott]

Thanks. However, this formula doesn't seem to work. It's producing a
"#VALUE!" error. This seems like this would be a simple calculation. Any
other ideas?

And by the way, yes, I have all of the hole #'s and handicaps in separate
cells, and without the "HCP." That was just a visual display for the
discussion boards.

 

[Scott]

Never mind! Thanks, Fred. I think I got this one figured out.