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)

 

[Fluff]

Hi & welcome to MrExcel.
What should the result be & why?

 

[69jjoseph69]

If you look at the golfer with the -4 handicap there should be an asterisk on the 15, 16, 17 and 18th handicap holes. Why, because these are the easiest holes, therefore a negative handicap golfer adds a stroke on each one of these holes.

 

[Fluff]

there should be an asterisk on the 15, 16, 17 and 18th handicap holes

Is that in relation to the hole numbers on row 3 or the handicap numbers on row 5?

Please be aware that not everyone here plays golf, or understands what your handicaps represent. So a slightly better explanation would help rather than

because these are the easiest holes

 

[69jjoseph69]

That is in relation to row 5

 

[Fluff]

Can you have a -21 handicap & if so what should happen?
A clear explanation & reason would help.

 

[69jjoseph69]

Even the best golfers in the world would never have a -21 handicap, but if one did, there would be two asterisks on the three easiest holes which would be the 16, 17 and 18th handicap holes.

 

[Fluff]

Ok, how about

=REPT("*",IF($D6<0,SUMPRODUCT(--($D6<=F$5-18)),SUMPRODUCT(--($D6>=F$5+{0,18,36}))))

 

[69jjoseph69]

The formula you sent works great but for one exception. It adds one more than what is needed. In other words if I type -6 in cell D6 the formula will will put seven asterisks on the card in the appropriate places where it only should put six.

 

[Fluff]

Oops, it should be < rather than <=