# Handicap auto stablefood formula

#### brynlee147

##### New Member
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

### Excel Facts

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

#### Snakehips

##### Well-known Member
@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
@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
156 KB · Views: 11

#### jimrward

##### Well-known Member
@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

@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
@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

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
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
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
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)

Replies
8
Views
909
Replies
9
Views
268
Replies
6
Views
212
Replies
8
Views
106
Replies
7
Views
133

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.

### Which adblocker are you using?

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

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