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?

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



On May 4, 8:13*pm, Scott wrote:
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?

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.

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

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.

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



On May 4, 8:13 pm, Scott wrote:
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?

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.





"Fred Smith" wrote:

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.

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

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



"Fred Smith" wrote:

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.

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

I'm assuming you're trying to calculate stableford scores?

If you Google "Stableford Checker Excel" you'll come up with something
to give you a few clues.

Ricky




Scott wrote:
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?