Handicap auto stablefood formula

brynlee147

New Member
Joined
Jun 20, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I've created a spreadsheet that has a gold course holes 1-18 each with their stroke index, also got coloums with amount of shots taken and points, I've managed to get the points and shots to add up automatically at the end, but put these in manually one by one, what I would like is for example if a golfer is a 22 handicap and he plays a hole which is index 2, he will get 2 shots extra on that and when I type the shots taken in, I would like it to automatically calculate the points awarded etc.. Same for an 18 handicap who would get 1 shot on every hole, anything under 18 then involes no shots etc

screenshot_20200621_093002-jpg.16686
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,224
Office Version
  1. 2013
Platform
  1. Windows
@brynlee147
Cannot see your image but here is an example of how you can approach this.

Book1
BCDEFGH
1
2PlayerFredH/Cap22
3HoleParSIGrossPoints
414943
5231733
6341352
744144
854561
9631542
1075771
1184353
12941134
13Out353819
141031842
151141243
16125263
17134834
181431451
19155454
201631651
21175644
221851062
23In374224
24Out353819
25Total728043
Sheet1
Cell Formulas
RangeFormula
G13, H13G13=SUM(G4:G11)
H4:H12, H14:H21H4=IF(G4-($E$2>=F4)-($E$2-18)>=F4<1,0,E4-L4+2)
H22H22=IF(G22-($E$2>=F22)+(($E$2-18)>=F22)<1,0,E22-L22+2)
G23:H23G23=SUM(G14:G22)
G24:H24G24=G13
G25:H25G25=SUM(G23:G24)


Hope that helps.
 

brynlee147

New Member
Joined
Jun 20, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
@brynlee147
Cannot see your image but here is an example of how you can approach this.

Book1
BCDEFGH
1
2PlayerFredH/Cap22
3HoleParSIGrossPoints
414943
5231733
6341352
744144
854561
9631542
1075771
1184353
12941134
13Out353819
141031842
151141243
16125263
17134834
181431451
19155454
201631651
21175644
221851062
23In374224
24Out353819
25Total728043
Sheet1
Cell Formulas
RangeFormula
G13, H13G13=SUM(G4:G11)
H4:H12, H14:H21H4=IF(G4-($E$2>=F4)-($E$2-18)>=F4<1,0,E4-L4+2)
H22H22=IF(G22-($E$2>=F22)+(($E$2-18)>=F22)<1,0,E22-L22+2)
G23:H23G23=SUM(G14:G22)
G24:H24G24=G13
G25:H25G25=SUM(G23:G24)


Hope that helps.
Thankyou for your advice and help, I shall try this later today when I get to sit down, really appreciate your williness to help. I've attached my orginal start of it before your help, it was work in progress and a shout for help lol... I'm a novice with excel formulas
Regards
Bryn
 

Attachments

  • Screenshot_20200621_093002.jpg
    Screenshot_20200621_093002.jpg
    156 KB · Views: 11

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,788
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
@Snakehips your formulas reference column L but not shown in graphic also I would add in $E$2-36 into the scenario for larger handicaps
i have a similar spreadsheet from many years ago but can’t locate it at the moment but it’s on the same lines as what you have suggested
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,224
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

@jimrward Thanks. I had just spotted that myself and will correct and re-post shortly.
I must have picked up off a test column and not edited appropriately.
I'm not hung over, so it must be an age thing!! ;)
 

brynlee147

New Member
Joined
Jun 20, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
@brynlee147
Cannot see your image but here is an example of how you can approach this.

Book1
BCDEFGH
1
2PlayerFredH/Cap22
3HoleParSIGrossPoints
414943
5231733
6341352
744144
854561
9631542
1075771
1184353
12941134
13Out353819
141031842
151141243
16125263
17134834
181431451
19155454
201631651
21175644
221851062
23In374224
24Out353819
25Total728043
Sheet1
Cell Formulas
RangeFormula
G13, H13G13=SUM(G4:G11)
H4:H12, H14:H21H4=IF(G4-($E$2>=F4)-($E$2-18)>=F4<1,0,E4-L4+2)
H22H22=IF(G22-($E$2>=F22)+(($E$2-18)>=F22)<1,0,E22-L22+2)
G23:H23G23=SUM(G14:G22)
G24:H24G24=G13
G25:H25G25=SUM(G23:G24)


Hope that helps.
Thankyou for your advice and help, I shall try this later today when I get to sit down, really appreciate your williness to help. I've attached my orginal start of it before your help, it was work in progress and a shout for help lol... I'm a novice with excel for
Regards
Bryn
@jimrward Thanks. I had just spotted that myself and will correct and re-post shortly.
I must have picked up off a test column and not edited appropriately.
I'm not hung over, so it must be an age thing!! ;)
Thanks guys...... I can defo relate to an age thing on some things lol
 

brynlee147

New Member
Joined
Jun 20, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thankyou for your advice and help, I shall try this later today when I get to sit down, really appreciate your williness to help. I've attached my orginal start of it before your help, it was work in progress and a shout for help lol... I'm a novice with excel for
Regards
Bryn

Thanks guys...... I can defo relate to an age thing on some things lol
I would like it cover from 0 handicap to 36 as I will use it against various players with various handicaps..
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,224
Office Version
  1. 2013
Platform
  1. Windows
Sorry for the delay.

Try this.....
Book1
ABCDE
1
2PlayerHcap
3Brynlee21.5
4IndexHoleParGrossPoints
5111351
612572
773443
854480
9155351
10136571
1137352
12178452
1399544
14210590
151411461
161812342
17413361
181214351
19615452
201016461
211617443
22818342
239929
Sheet1 (2)
Cell Formulas
RangeFormula
E5:E22E5=IF(D5<1,0,MAX(C5-(D5-((ROUND($E$3,)>=A5)+((ROUND($E$3,)-18)>=A5)))+2,0))
D23:E23D23=SUM(D5:D22)
 

brynlee147

New Member
Joined
Jun 20, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Sorry for the delay.

Try this.....
Book1
ABCDE
1
2PlayerHcap
3Brynlee21.5
4IndexHoleParGrossPoints
5111351
612572
773443
854480
9155351
10136571
1137352
12178452
1399544
14210590
151411461
161812342
17413361
181214351
19615452
201016461
211617443
22818342
239929
Sheet1 (2)
Cell Formulas
RangeFormula
E5:E22E5=IF(D5<1,0,MAX(C5-(D5-((ROUND($E$3,)>=A5)+((ROUND($E$3,)-18)>=A5)))+2,0))
D23:E23D23=SUM(D5:D22)
Hi, once again thankyou, there is no need for apology, as I mentioned I'm a novice with excel formulas, so just trying to learn... I will try what you have sent with Great appreciation. Would what you done work for any handicap, or is there a aspect I would need to change for what ever the handicap may be, because mine could change from 22 to let's just say 16 for example..
Much apprection
Kind regards
Bryn
Sorry to ask so much, but it's only for me to try and understand and learn G5
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,224
Office Version
  1. 2013
Platform
  1. Windows
Bryn, you are most welcome.

If you change the handicap value entered in cell E3, the points scored will change accordingly.

Below, I have made a small formula change( E$3 rather than $E$3) Thus I was able to copy A2:E23 and paste it to G2:K23 to give a second scorecard for your opponent.

NB: Click the BB2XL Copy icon, just above the 2 in my response table and you should then be able to paste it all into your spreadsheet.

Book1
ABCDEFGHIJK
2PlayerHcapPlayerHcap
3Brynlee21.5Shakey16
4IndexHoleParGrossPointsIndexHoleParGrossPoints
5111351111360
61257212571
77344373452
85448054452
9155351155342
10136571136544
113735237333
12178452178442
139954499562
14210590210571
1514114611411461
1618123421812341
174133614133100
1812143511214333
19615452615443
2010164611016461
2116174431617443
22818342818342
2399299233
Sheet1 (2)
Cell Formulas
RangeFormula
E5:E22, K5:K22E5=IF(D5<1,0,MAX(C5-(D5-((ROUND(E$3,)>=A5)+((ROUND(E$3,)-18)>=A5)))+2,0))
D23:E23, J23:K23D23=SUM(D5:D22)
 

Watch MrExcel Video

Forum statistics

Threads
1,132,646
Messages
5,654,565
Members
418,140
Latest member
ahepple86

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