Adding a NR to a formula & changing an IF to an IFS statement.

[Marvo]

I'm after a little help with two formulas. The mini sheet is working out scores for golf competitions. Sometimes a player doesn't complete a hole (No return) and in this instance I used to type in a 9 but as handicaps have risen this can now actually work out to a point for some players so I need to record something else.

If I do a simple SUM formula, 6+5+4+3=18. Then change that to 6+5+NR+3=14 it works. However in the SUM formula on my worksheet in Column A0 it doesn't, gives a wrong data type. Can I fix that?

If I can then the follow up question.

In the formula in Z5,

=IF(Y5="","",MAX(2+Z$75-Y5+(Z$75<=$D5)+(Z$75<=($D5-18))+(Z$75<=($D5-36)),0))

I'd like to add another option where after Y5="","", is added IF(Y5=NR,0)

So I'm saying if Y5 has no data, return zero, if Y5 has NA return zero, otherwise the MAX formula

Any help appreciated.

Midshires Master.xlsm
	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	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1			HOLE	1		2		3		4		5		6		7		8		9		10		11		12		13		14		15		16		17		18		Total	S/R	127
2	PLAYER		PAR		4		4		4		3		4		3		4		3		4		3		5		4		4		4		4		4		3		5	69	C/R	68.8
3			S/I		14		4		8		16		2		18		12		10		6		17		5		7		1		15		3		11		9		13	GROSS	3	PTS
4	Brian ADAMS	High	22																																					0	0	0
5	Linda ADAMS	High	52																					10	0															10	0	0
6	Jeff BATTERBEE	High	35																																					0	0	0
7	Nick BAXTER	Low	17																																					0	0	0
8	Chris BENNETT	Low	20																																					0	0	0
Master

Cell Formulas
Range	Formula
AO2	AO2	=SUM(E2:AN2)
C4:C67	C4	=IF(D4:D67<=$C$72,"Low","High")
D4:D67	D4	=Handicaps!K4:K67
AN4,AN6:AN8,AL4,AL6:AL8,AJ4,AJ6:AJ8,AH4,AH6:AH8,AF4,AF6:AF8,AD4,AD6:AD8,AB4,AB6:AB8,Z4,Z6:Z8,X4,X6:X8,V4,V6:V8,T4,T6:T8,R4,R6:R8,P4,P6:P8,N4,N6:N8,L4,L6:L8,J4,J6:J8,H4,H6:H8,F4,F6:F8	AN4	=IF(AM4="","",MAX(2+AN$2-AM4+(AN$3<=$D4)+(AN$3<=($D4-18))+(AN$3<=($D4-36)),0))
AO4:AO8	AO4	=SUM(ISODD(COLUMN(E4:AN4))*IF(E4:AN4="",0,E4:AN4))
AP4:AP8	AP4	=SUMIFS($E4:$AN4,$E$2:$AN$2,3)
AQ4:AQ8	AQ4	=SUM(ISEVEN(COLUMN(E4:AN4))*IF(E4:AN4="",0,E4:AN4))
AN5,AL5,AJ5,AH5,AF5,AD5,AB5,Z5,X5,V5,T5,R5,P5,N5,L5,J5,H5	AN5	=IF(AM5="","",MAX(2+AN$75-AM5+(AN$75<=$D5)+(AN$75<=($D5-18))+(AN$75<=($D5-36)),0))
F5	F5	=IF(E5="","",MAX(2+F$75-E5+(F$73<=D5)+(F$75<=(D5-18))+(F$75<=(D5-36)),0))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:AR1,B2:AQ2,B3:AZ3,B4:BL67,BB2:BD3,BA1:BE1,BG1:BL3	Expression	=ROW()=CELL("row")	text	NO
F3,H3,J3,L3,N3,P3,R3,T3,V3,X3,Z3,AB3,AD3,AF3,AH3,AJ3,AL3,AN3	Cell Value	between 1 and 3	text	NO

 

[Special-K99]

NR is a string, you can't add strings to numbers.
Just refer to the cell range
6+5+4+3=18
with values 6 5 4 3 in A1 A2 A3 A4 lets say
So
A1+A2+A3+A4=18
or
SUM(A1:A4)=18

If you change the value in A3 (4) to NR the SUM still works but A1+A2+A3+A4 will produce a #VALUE error since A3 (NR) is now a string.
So just use the SUM() formula
otherwise you're gonna have to check each cell for NR and add 0 or the cell value:
=IF(A1="NR",0,A1)+IF(A2="NR",0,A2)+IF(A3="NR",0,A3)+IF(A4="NR",0,A4)


As for follow up question use:
=IF(Y5="","",IF(Y5="NR",0,MAX(2+Z$75-Y5+(Z$75<=$D5)+(Z$75<=($D5-18))+(Z$75<=($D5-36)),0)))

 

[Marvo]

Thank you. I'll try what you suggest and report back.

 

[Marvo]

Yes, all good. Thanks again.