USGA Golf Handicap Formula

[VinceF]

Greetings and thank you for any assistance with this formula.
The United States Golf Association uses a handicapping formula that I'd like to implement in a spreadsheet that I've created.
To obtain a "Course Handicap" you use the following...
Handicap Index multiplied by the Slope Rating divided by 113 plus Course Rating minus Par.
For example, The following course Slope Rating is 136, Par is 70.
Player A's index is 9.7
To obtain a course handicap for player A is as follows...
(9.7 multiplied by 136 divided by 113) plus (73.6 minus 70) = 15 handicap for Player A for that particular course
Some exceptional golfers have what is called + handicaps which means that they regularly shoot under par and established a "+ handicap".
I'd like my spread sheet to incorporate a formula where it recognizes a "+ handicap" index.

In my spreadsheet you'll notice that I have to put Jane Doe's index in as (-4 index) to obtain a (-1 handicap). Both of these numbers I'd like to show as "+"
(+4 multiplied by 136 divided by 113) plus (73.6 minus 70) = +1 handicap for Player A for that particular course)

I hope I've explained this well enough,

Thanks
VinceF
Excel 2016

8-13 SKINS FINAL INDIANWOOD.xlsm
	G	H	I	J	K
11	JOHN DOE		9.7	15
12	JANE DOE		-4.0	-1
13
Main

Cell Formulas
Range	Formula
J11:J13	J11	=IF(I11="","",(I11*$R$3/113)+$P$3-$AI$8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G11	Expression	=ISTEXT($G$11)	text	NO
G11:H11	Expression	=ISTEXT($N$3)	text	NO
I11	Expression	=ISNUMBER($I$11)	text	NO
I11	Expression	=ISTEXT($G$11)	text	NO
J12,J14,J16,J18,J20,J22,J24,J26,J28,J30,J32,J34,J36,J38,J40,J42,J44,J46,J48,J50	Cell Value	contains "FALSE"	text	NO
J11,J13,J15,J17,J19,J21,J23,J25,J27,J29,J31,J33,J35,J37,J39,J41,J43,J45,J47,J49	Cell Value	contains "FALSE"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G11:H13	List	='Players list'!$B$5:$B$101

 

[Fluff]

How about

=IF(I12="","",(abs(I12)*$R$3/113)+$P$3-$AI$8)

 

[VinceF]

How about

=IF(I12="","",(abs(I12)*$R$3/113)+$P$3-$AI$8)

Hello Fluff,
Thank you for your attempt, you've been very helpful in the past.

However the formula you suggested results in "8" (See Jane Doe). the ABS function is turning the negative number into a positive number giving the result as 8.
What I'm looking to accomplish is that I enter "+4" in the players index cell and based on the course information as given in the 1st message it would result in (+1) course handicap.
The USGA's website allows or calculates this...I only wish I could see what formula they are using.
To help clarify this a little better and for this course which is a par 70, a "4" handicap player would average 77 for 18 holes, whilst a "+4" handicap player would average 71 for 18 holes.

Hope this helps,

VinceF

8-13 SKINS FINAL INDIANWOOD.xlsm
	G	H	I	J	K
11	JOHN DOE		4.0	8
12	JANE DOE		-4.0	8
13
14
15
Main

Cell Formulas
Range	Formula
J11,J13:J15	J11	=IF(I11="","",(I11*$R$3/113)+$P$3-$AI$8)
J12	J12	=IF(I12="","",(ABS(I12)*$R$3/113)+$P$3-$AI$8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G11	Expression	=ISTEXT($G$11)	text	NO
G11:H11	Expression	=ISTEXT($N$3)	text	NO
I11	Expression	=ISNUMBER($I$11)	text	NO
I11	Expression	=ISTEXT($G$11)	text	NO
J12,J14,J16,J18,J20,J22,J24,J26,J28,J30,J32,J34,J36,J38,J40,J42,J44,J46,J48,J50	Cell Value	contains "FALSE"	text	NO
J11,J13,J15,J17,J19,J21,J23,J25,J27,J29,J31,J33,J35,J37,J39,J41,J43,J45,J47,J49	Cell Value	contains "FALSE"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G11:H15	List	='Players list'!$B$5:$B$101

 

[VinceF]

Hello Fluff,
Thank you for your attempt, you've been very helpful in the past.

However the formula you suggested results in "8" (See Jane Doe). the ABS function is turning the negative number into a positive number giving the result as 8.
What I'm looking to accomplish is that I enter "+4" in the players index cell and based on the course information as given in the 1st message it would result in (+1) course handicap.
The USGA's website allows or calculates this...I only wish I could see what formula they are using.
To help clarify this a little better and for this course which is a par 70, a "4" handicap player would average 77 for 18 holes, whilst a "+4" handicap player would average 71 for 18 holes.

Hope this helps,

VinceF

8-13 SKINS FINAL INDIANWOOD.xlsm
	G	H	I	J	K
11	JOHN DOE		4.0	8
12	JANE DOE		-4.0	8
13
14
15
Main

Cell Formulas
Range	Formula
J11,J13:J15	J11	=IF(I11="","",(I11*$R$3/113)+$P$3-$AI$8)
J12	J12	=IF(I12="","",(ABS(I12)*$R$3/113)+$P$3-$AI$8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G11	Expression	=ISTEXT($G$11)	text	NO
G11:H11	Expression	=ISTEXT($N$3)	text	NO
I11	Expression	=ISNUMBER($I$11)	text	NO
I11	Expression	=ISTEXT($G$11)	text	NO
J12,J14,J16,J18,J20,J22,J24,J26,J28,J30,J32,J34,J36,J38,J40,J42,J44,J46,J48,J50	Cell Value	contains "FALSE"	text	NO
J11,J13,J15,J17,J19,J21,J23,J25,J27,J29,J31,J33,J35,J37,J39,J41,J43,J45,J47,J49	Cell Value	contains "FALSE"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G11:H15	List	='Players list'!$B$5:$B$101

 

[Fluff]

Misunderstood what you wanted, if you are just trying to get rid of the - sign in the results, then use

=IF(I12="","",abs((I12*$R$3/113)+$P$3-$AI$8))

 

[VinceF]

Misunderstood what you wanted, if you are just trying to get rid of the - sign in the results, then use

=IF(I12="","",abs((I12*$R$3/113)+$P$3-$AI$8))

Thanks again Fluff but I actually want to use the "+", for example "+4" would be entered into the players "Index", the result would be a course handicap of "+1"
This is a picture of the USGA's handicap calculator...whatever formula that they use recognizes the "+".

 

[Fluff]

If you have a + sign in the cell then it is no longer a number but text.
Did the 2nd formula do what you initially asked for?