Adding pops to an Excel golf scorecard

[69jjoseph69]

I have found a formula to use in an Excel golf scorecard worksheet here on Mr. Excel but it is a very old thread and is locked so I can't ask a question there. I have been using this formula for quite a few years now and its worked well until we had a gentleman join our golf group with a plus handicap. I need a formula that will work on both plus and minus handicaps. I've uploaded an example worksheet that I have been using and you will see that the formula that I have been using doesn't work for the golfer with a -4 handicap. Any help would be appreciated.

Example.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1					Sterling Grove Golf & CC Traveling Game Scorecard
2
3			Hole		1	2	3	4	5	6	7	8	9	Out		Initials		10	11	12	13	14	15	16	17	18	In		Total
4	White Tees		67.3 / 114		362	272	139	511	395	243	176	391	446	2935				327	471	417	120	303	343	248	179	497	2905		5840
5			Handicap		9	15	1	17	7	13	3	5	11					14	18	8	2	12	6	4	16	10
6	Mark S.		-4													MS
7	Kevin G.		9		*		*		*		*	*				KG				*	*		*	*
8	Bob B.		7				*		*		*	*				BB					*		*	*
9	Norm J.		11		*		*		*		*	*	*			NJ				*	*		*	*		*
10			Par		4	4	5	3	4	3	5	4	4	36				4	3	4	5	4	4	5	3	4	36		72
11							Skins
12					Front Nine			Back Nine			Total
13	Mark S.
14	Kevin G.
15	Bob B.
16	Norm J.
17
Sterling Grove Golf & CC

Cell Formulas
Range	Formula
O4,AB10,O10,AB4	O4	=SUM(F4:N4)
AD4,AD10	AD4	=O4+AB4
F6:N9,S6:AA9	F6	=REPT("*",SUMPRODUCT(--($D6>=F$5+{0,18,36})))
Q6:Q9	Q6	=LEFT(B6)&IF(ISNUMBER(FIND(" ",B6)),MID(B6,FIND(" ",B6)+1,1),"")&IF(ISNUMBER(FIND(" ",B6,FIND(" ",B6)+1)),MID(B6,FIND(" ",B6,FIND(" ",B6)+1)+1,1),"")
B13:B16	B13	=IF(B6<=0,"",B6)

 

[69jjoseph69]

Works like a charm. Thank you very much.

 

[Fluff]

You're welcome & thanks for the feedback.