Golf Quota Point System Formula Help

[Jonezy47]

Good Afternoon,

I am in dire need of help. I am not to savvy with Excel. I have been struggling and seem to find no answers on how to convert someones score to par, to points in the row below. For example in quota point system, its a lot like Stableford, but ones handicap is subtracted from 36, and must obtain that many of points. For instance, if the hole is a par 5, in F11 I would enter someones score, and in F2 it would show there points earned according to par.

Eagle 8
Birdie 4
Par 2
Bogey 1
Double Bogey or worse 0

So if someone got a 6 on the hole it would show 1 in A4, and same for if someone got a 4 it would show a 4 in A4 all depending on what they scored. Hope this makes sense thank you

 

[Fluff]

Hi & welcome to MrExcel.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=CHOOSE(MAX(F7-F11,-2)+3,0,1,2,4,8)

 

[Jonezy47]

THANK YOU!!! Appreciate that. I am not sure what version it is as it is the free version on microsoft online. Does that same formula work, no matter what the par of the hole is? For example if its a par 3 or par 4? Understandably i'll have to change every cell, when i go through each column to do it. Also is there a way to show a 0 in F12 instead of a #VALUE! Again thank you so much for your help

 

[Fluff]

I am not sure what version it is as it is the free version on microsoft online

That's 365

If you are getting a #value error then one of the scores is more than 2 under par.

 

[Jonezy47]

Understood,
Except nothing is put in the cell for anything to be under par.

 

[Dave Patton]

Another alternative
row 13 uses the table for the Lookup
row 14 puts the points information in the formula

T202303a.xlsm
	A	B	C	D	E	F	G	H
1								Golf points
2		-99	8
3		-1	4
4		0	2		Hole	1	2	3
5		1	1		Yards	345	490	170
6		2	0		Handicap	1	3	2
7					Par	5	5	3
8
9
10
11					Scores	5	6	1
13					Points	2	1	8
14					Points	2	1	8
5g

Cell Formulas
Range	Formula
F13:H13	F13	=LOOKUP(F11-F7,$B$2:$C$6)
F14:H14	F14	=LOOKUP(F11-F7,{-99,8;-1,4;0,2;1,1;2,0})

 

[Fluff]

Except nothing is put in the cell for anything to be under par.

You haven't put in how many strokes they took, hence the error.
How about

Fluff.xlsm
	E	F	G	H
1
2
3
4	Hole	1	2	3
5
6
7	Par	5	5	3
8
9
10
11	Strokes	3	10	3
12	Score	8	0	2
Main

Cell Formulas
Range	Formula
F12:H12	F12	=IF(F11="","",SWITCH(F7-F11,-1,1,0,2,1,4,2,8,0))

 

[Jonezy47]

I’ll test that out! Makin dinner at moment. Dinner time here in UK! Really appreciate it! I will let u know how it works! Thanks again! I’m sure I’ll have a lot more questions!

 

[Fluff]

Ok

 

[Jonezy47]

Yes, that worked as well. Didn’t show any error or anything just showed it as blank! Which works too! So I will have to go through every cell 18 cells horizontally for 18 holes and enter that code every time correct ?