Golf Scoring Formula - possibly IF statement

thebushwoodgolfclub

New Member
Joined
Feb 6, 2019
Messages
5
Hi everyone,

I'd appreciate some help putting together a formula for my golf club scoring sheet. I've used this forum before and the help was magnificent. I'll first outline how the scoring works, and then explain the spreadsheet.

The format is called "Stableford", and rather than putting down the amount of strokes you hit on a hole, each hole is allocated points, based on it's difficulty, and the ability of the golfer.

Golf handicaps range from 0-36. Depending on the difficulty of a hole (the index), and ability of the golfer, they are allocated "shots" on each hole. The index ranges from 1 as the most difficult hole, through to 18 as the least difficult. For example, if i had a handicap of 9, I would be allocated a "shot" for each of the 9 hardest holes, and no "shots" for the 9 easiest (nb: The Par column has no bearing. I just added it to be consistent with my spreadsheet)

HOLE - PAR - INDEX - SHOTS
1 - 4 - 13 - 0
2 - 4 - 7 - 1
3 - 4 - 5 - 1
4 - 5 - 10 - 0

If my handicap was 18, i would get a shot on every hole. If my handicap was 27, I would get a shot on all 18, and then an extra shot on the 9 hardest

HOLE - PAR - INDEX - SHOTS
1 4 13 1
2 4 7 2
3 4 5 2
4 5 10 1

What i am looking to do with the spreadsheet in Column E is calculate the shots for ALLEN, based on the Index of the hole (column D), and the handicap (cell M1). That is a handicap 18 or less. For Allen, he gets a shot on the 9 hardest holes. I think i have that, as i put =IF(D5<=M1,1,0) and it appeared to work. However...

I also need to calculate those that have a handicap 19-36, so, in Column E, calculate the shots for BELL (column E), based on the Index of the hole (column D), and the handicap (cell M20). For Bell, he gets one shot for each hole, and get two shots for the 9 hardest holes.

Hopefully that makes sense


Cheers
Damian
 

Attachments

  • Bushwood Scoring.PNG
    Bushwood Scoring.PNG
    51 KB · Views: 67

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
Cross posted Golf Scoring Formula assistance required

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,537
Office Version
  1. 365
Platform
  1. Windows
You will generally get much faster help if you give us the sample with XL2BB so we can copy/paste? Otherwise there is an awful lot of manual typing to set that up to test. ;)

Anyway if a handicap is in cell M20 and Index numbers in D24 down and N24 down then in E24, copied down try
Excel Formula:
=(D24<=$M$20)+(D24<=$M$20-18)
This can then be copied to column O.
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,537
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks for the cross-post link - but did you try my formula? ;)
 

thebushwoodgolfclub

New Member
Joined
Feb 6, 2019
Messages
5
You will generally get much faster help if you give us the sample with XL2BB so we can copy/paste? Otherwise there is an awful lot of manual typing to set that up to test. ;)

Anyway if a handicap is in cell M20 and Index numbers in D24 down and N24 down then in E24, copied down try
Excel Formula:
=(D24<=$M$20)+(D24<=$M$20-18)
This can then be copied to column O.
Simple yet effective Peter. Thanks very much for your help.

If you get an opportunity, would you be able to type a few quick notes about how you got to this formula ?

Many Thanks
Damian
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,537
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Simple yet effective Peter. Thanks very much for your help.
You're welcome. Thanks for the follow-up.


would you be able to type a few quick notes about how you got to this formula
(D24<=$M$20)
If the hole index is less than or equal to the handicap this is True, otherwise False

(D24<=$M$20-18)
If the hole index is less than the handicap less 18 then this would be True otherwise False

When you add together these True/False values, True converts to 1 and False converts to 0
The only possibilities are
False + False = 0 shots
True + False = 1 + 0 = 1 shot
True + True = 1 + 1 = 2 shots

Perhaps it would make more sense if I framed it as
If the index is less than or equal to your handicap you get 1 shot
If you add 18 to the index and it is still less than or equal to your handicap you get an extra shot.
In formula terms it would just put that second term in a slightly different order.
Excel Formula:
=(D24<=$M$20)+(D24+18<=$M$20)


If this has solved your question and you have not received a solution at the cross-post it would be polite to post there to say that you have a solution so members there know that you are not still waiting (though of course they can still answer there if they want).
 
Solution

thebushwoodgolfclub

New Member
Joined
Feb 6, 2019
Messages
5
You're welcome. Thanks for the follow-up.



(D24<=$M$20)
If the hole index is less than or equal to the handicap this is True, otherwise False

(D24<=$M$20-18)
If the hole index is less than the handicap less 18 then this would be True otherwise False

When you add together these True/False values, True converts to 1 and False converts to 0
The only possibilities are
False + False = 0 shots
True + False = 1 + 0 = 1 shot
True + True = 1 + 1 = 2 shots

Perhaps it would make more sense if I framed it as
If the index is less than or equal to your handicap you get 1 shot
If you add 18 to the index and it is still less than or equal to your handicap you get an extra shot.
In formula terms it would just put that second term in a slightly different order.
Excel Formula:
=(D24<=$M$20)+(D24+18<=$M$20)


If this has solved your question and you have not received a solution at the cross-post it would be polite to post there to say that you have a solution so members there know that you are not still waiting (though of course they can still answer there if they want).
Thank you very much for your assistance and very detailed reply. I really do appreciate it

Cheers
Damian
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,537
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,820
Messages
5,638,520
Members
417,031
Latest member
SEAN004

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top