Golf Handicap Formula help

[Gulfcourse]

I took over the league this year and was given a CD for a golf program that worked with Windows Caveman so I decided to create my own since it is such a small league of old guys!
To establish a handicap for this season I manually enterd 5 scores in Columns I through N, in every row, to get a handicap to reflect the one they finished with last season.
I have been entering their scores twice a week since April and am currently in Column BB.
Not everyone plays every time so there are gaps in some players rows.

I have had a working formula to take the last 5 scores to establish the golfers handicap but it has gone wonky on me.
The formula listed below is in column CA5 through CA28.
The number from Column CA is returned to the corresponding player in Column D and then the other simple formulas, in Columns D, E, & F kick in to figure the handicap.
The problem is the totals in Column CA were no longer consistent, some were only totaling up the last 4 scores instead of 5.
The original formula is:
=IFERROR(SUM(OFFSET($H10,0,LARGE(IF(I10:BY10<>"",COLUMN(I10:BY10))-8,$BZ$3),1,(45-LARGE(IF(I10:BY10<>"",COLUMN(I10:BY10))-8,$BZ$3)))),"NOT ENOUGH")
but for some rows it is only counting the last 4 and I've had to go to those rows and change the -8's in the formula to 9's or 10's to get 5 scores and I have no idea why.

The players are in the rows and the course scores are in the columns.
I have a drop down to pick a value in BZ3 and leave it at 5, this enables me to change the number of scores I want to use but I have not changed it from 5.

Some folks have more rounds than others but I don't think that's the problem since, for example, Petro was correct in CA22 and one row down Roderick CA23 was incorrect until I changed Roderick's 8's to 9's.
The formula (the original) in for Petro that returns 5 is
=IFERROR(SUM(OFFSET($H22,0,LARGE(IF(I22:BY22<>"",COLUMN(I22:BY22))-8,$BZ$3),1,(45-LARGE(IF(I22:BY22<>"",COLUMN(I22:BY22))-8,$BZ$3)))),"NOT ENOUGH")

The formula I had to change to get 5 scores for Roderick is:
=IFERROR(SUM(OFFSET($H23,0,LARGE(IF(I23:BY23<>"",COLUMN(I23:BY23))-9,$BZ$3),1,(45-LARGE(IF(I23:BY23<>"",COLUMN(I23:BY23))-9,$BZ$3)))),"NOT ENOUGH")

I don't want to have to constanly change the formulas so any suggestions would be greatly appreciated!

 

[Peter_SSs]

Unfortunately we cannot copy from your image to test but see if the formula below, which also avoids the volatile function OFFSET, is any use for summing the last 5.

For the future, have a look at XL2BB for providing sample data that we can easily use.

24 08 08.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Sum Last 5
2	426	81	110	83	78	70	105	96	72	76	99	79	93	104	74	76
3	473	92	89	78	89	107	94	110	74	107	88
4	NOT ENOUGH					77			84
5	412			105	108	75				77	87			76	97
6	NOT ENOUGH
Last 5

Cell Formulas
Range	Formula
E2:E6	E2	=LET(f,TAKE(FILTER(F2:Z2,F2:Z2>0),,-5),IF(COUNT(f)=5,SUM(f),"NOT ENOUGH"))

 

[Gulfcourse]

Test_Golf_file.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	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA		BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC
3	LAST	HNCP	TOTAL	AVG	PAR	DIV BY	ROUNDS	MAN	MAN	MAN	MAN	MAN	MAN	SCORE	SCORE	SCORE	SCORE	SCORE	SCORE	4/9	4/16	4/18	4/23	4/25	4/30	5/2	5/7	5/9	5/14	5/16	5/21	5/23	5/28	5/30	6/4	6/6	6/11	6/13	6/18	6/20	6/25	6/27	7/2	7/4	7/9	7/11	7/16	7/18	7/23	7/25	7/30	8/1		8/8	8/13	8/15	8/20	8/22	8/27	8/29	9/3	9/5	9/10	9/12	9/17	9/19	9/24	9/26	10/1	10/3	10/8	10/10	10/15	10/17	10/22	10/28	5
4	LAST	HNCP	TOTAL	AVG	PAR	DIV BY	ROUNDS	MAN	MAN	MAN	MAN	MAN	MAN	Ridgetop	Coppertop	SpringV	Echo V	Els Mead	Ironwood	Bunker	Brookledge	Seneca	Springvale	Cosset Creek	Pine Hills	Grantwood	Coppertop	Good Park	Bunker Hill	Eagle Creek	Valleaire	Loyal Oak	Grey Hawk	Ridge Top	Royal Crest	Fox Den	Briarwood	Thunderbird South	Chenoweth	Pine Brook	Spring Hills	Roses Run	Raintree	NO PLAY	Pleasant Valley	Echo Valley	Chippewa	Turkeyfoot	Rawiga	Racoon Hill	Willow Creek	The Pines		Cosset Creek	Windmill Lakes	Ridgewood	Mayfair	Tanglewood	Pine Hills	Chenoweth	Forest Hills	Paradise Lake	Coppertop	Sugarbush	Indian Hollow	Legends of Massilon	Fox Creek	Riceland	Spring Hills	Elsworth Meadows										Should be
5	Bonham	-15.2	265	53	72	5	11	98	84	84	84	84	84								92			83		84	88				86		91				85				82									98		88		84																								265	Bonham	437
6	Cox	8.2	411	82.2	72	5	6	78	77	79	81	85	80																					81	80	87	85		83		76																																					411	Cox	411
7	Dangerfield	-16.6	256	51.2	72	5	26	89	90	91	91	93	92		82	82							97	94		92	94		87	88	97	99	95	88	93		93		94	90	88	98			95		94	89	85	88	83	93		87																								256	Dangerfield	436
8	Davis	21.1	492	98.4	72	5	1	102	104	105	102	101	99				85																																																													492	Davis	511
9	Edmisten	-16.8	255	51	72	5	30	87	87	87	85	84	85	90	85	86			84	84	86		83		92	86	91		82	89	87	85	83	91	87	89	96	87		84		89	86		93	89	82	87			86	92		81																								255	Edminson	428
10	Frederick	-17.6	250	50	72	5	13	83	83	84	84	84	84							92			87			89	79				87						86		88		78		91					77	82			85		84																								250	Frederick	419
11	Funari	11.2	430	86	72	5	3	84	84	83	82	83	84				91						86																								86																															430	Funari	430
12	Gosnell	18.4	475	95	72	5	3	94	95	91	93	95	91							95										91												103																																				475	Gosnell	475
13	Gressell	-17.4	251	50.2	72	5	23	89	85	86	85	84	85		95	87				84					94	95				85	88		93		85		88	90	96	82	84	88			91	87	94	84	79		88	86		92																								251	Gressell	429
14	Hobbs	-15.0	266	53.2	72	5	30	84	84	85	85	84	85			77	90		86	86	92		86		87	91	85		86	94	79		92	83	79	94	92		87	82	81	94	92			90	95	93	91	84	91	87		84																								266	Hobbs	437
15	Keep	-16.8	255	51	72	5	34	87	87	86	90	89	87	91		85	88	86		89	94	91	86	92	90		88		85	80	91		95	94	85	90	92	87	86	90	83	90	85		93	93	91	91	91	83	81	88		84																								255	Keep	427
16	Kilroy	-19.5	238	47.6	72	5	26	84	84	85	85	84	85	80		81	88	86					84	88	94	91	89		85	83	86	83	88	94	83	85	89						92		88	94	80	79			79	83		76																								238	Kilroy	397
17	Lincoln	-12.0	285	57	72	5	25	95	100	100	99	100	98		98	98	97			99		104	90	99	98		95				89	94		95	91	93		91	96	97	87	97	90		101			91	93			92		87																								285	Lincoln	464
18	Moderick	18.4	475	95	72	5	2	96	96	94	94	96	95						95																														95																													475	Moderick	475
19	Nassar	-11.2	290	58	72	5	33	92	92	92	92	92	92	94	85		92	95	100	96	96		98	92		95	96		95	98	97	93	98	98	89	96	102	97	98	94	89	99	95		98	96		94	99	97		94		91																								290	Nassar	475
20	Nigro	1.6	370	74	72	5	27	84	84	84	83	83	82	88	86	82	82				95	95	87		96	93	88			88	91	87	93	89	84	91			95	90	91	99			97	96	94	94	86					88																								370	Nigro	458
21	Paladino	13.4	444	88.8	72	5	2	86	86	86	87	86	86			87											98																																																			444	Paladino	444
22	Petro	1.9	372	74.4	72	5	28	90	90	90	90	90	89	92	90	90		87	87	95	87	96	86	97		98	93					98	96	90	87	93	99		95	91	84				97	93	96		88	94	94			87																								372	Petro	459
23	Roderick	-19.5	238	47.6	72	5	38	79	79	79	79	79	78	81	74	74	81	82	81	75	78	80	78	74	83	77	75		78	81	79	82	81	86	75	76	78	75	81	85	73	81	75		81	77	78	74	81	77	80	78		77																								238	Roderick	393
24	Sepic	-14.2	271	54.2	72	5	36	85	85	86	85	88	85	89	82	85	86	90	85	86	91	94	84	87	100	84	93				90	85	93	84	83	91	87	74	86	88	85	87	89		88	87	92	87	88	90	93	89		90																								271	Sepic	450
25	Smigel					5	0	95	95	95	95	95	95																																																																	475	Smigel	475
26	Theberge	-19.5	238	47.6	72	5	22	77	77	77	77	75	74		80		83				88	82	82	90					80	78							83	80	79		75	81	83		84	84	80	80	76	82		80		77																								238	Theberge	395
27	Wallon	-16.5	257	51.4	72	5	28	86	86	86	85	86	86	87	85	81		85	83	91	91	97	89						82	88	90	95		94		88	88	84	86	86	76	98	82				90	82	89	86		90		84																								257	Wallon	431
28	Zeman	-13.9	273	54.6	72	5	29	92	92	90	91	90	91							100	91	101	85		98	91	91		90	89	87	94	96		94	95	92	94	95	91	82	97	94		103	97	100		89	88	96	91		88																								273	Zeman	452
HANDICAP

Cell Formulas
Range	Formula
Z3,AB3,AD3,AF3,AH3,AJ3,AL3,AN3,AP3,AR3,AT3,AV3,AX3,AZ3,BD3,BF3,BH3,BJ3,BL3,BN3,BP3,BR3,BT3,BV3,BX3	Z3	=SUM(X3+7)
AA3,AC3,AE3,AG3,AI3,AK3,AM3,AO3,AQ3,AS3,AU3,AW3,AY3,BA3,BC3,BE3,BG3,BI3,BK3,BM3,BO3,BQ3,BS3,BU3,BW3	AA3	=Y3+7
C5:C24,C26:C28	C5	=SUM(E5-F5)*0.8
D5:D24,D26:D28	D5	=SUM(CA5)
E5:E24,E26:E28	E5	=SUM(D5/G5)
H5:H28	H5	=COUNT(O5:BC5)
CA5:CA28	CA5	=IFERROR(SUM(OFFSET($H5,0,LARGE(IF(I5:BY5<>"",COLUMN(I5:BY5))-8,$BZ$3),1,(45-LARGE(IF(I5:BY5<>"",COLUMN(I5:BY5))-8,$BZ$3)))),"NOT ENOUGH")

Cells with Data Validation
Cell	Allow	Criteria
BZ3	List	4,5,6,7,8,9,10,11,12,13,14,15
BZ4	List	5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44
CA4	List	=$A$5:$A$17

 

[Gulfcourse]

I wasn't quite sre what to do with the formula provided above and figured out how to use the xl2bb.
I've added what the totals should be in Column CC.

Thank you

 

[Peter_SSs]

I don't understand some of the 'Should be' values but see if this helps. I have included two options (columns CC and CD).

24 08 08.xlsm
	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	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD																				BX	BY	BZ	CA	CB	CC	CD	CE
3	MAN	SCORE	SCORE	SCORE	SCORE	SCORE	SCORE	45391	45398	45400	45405	45407	45412	45414	45419	45421	45426	45428	45433	45435	45440	45442	45447	45449	45454	45456	45461	45463	45468	45470	45475	45477	45482	45484	45489	45491	45496	45498	45503	45505		45512	7																				77	45593	5
4	MAN	Ridgetop	Coppertop	SpringV	Echo V	Els Mead	Ironwood	Bunker	Brookledge	Seneca	Springvale	Cosset Creek	Pine Hills	Grantwood	Coppertop	Good Park	Bunker Hill	Eagle Creek	Valleaire	Loyal Oak	Grey Hawk	Ridge Top	Royal Crest	Fox Den	Briarwood	Thunderbird South	Chenoweth	Pine Brook	Spring Hills	Roses Run	Raintree	NO PLAY	Pleasant Valley	Echo Valley	Chippewa	Turkeyfoot	Rawiga	Racoon Hill	Willow Creek	The Pines		Cosset Creek	Windmill Lakes																											Should be
5	84								92			83		84	88				86		91				85				82									98		88		84																								265	Bonham	437	437	437
6	80																					81	80	87	85		83		76																																					411	Cox	411	411	411
7	92		82	82							97	94		92	94		87	88	97	99	95	88	93		93		94	90	88	98			95		94	89	85	88	83	93		87																								256	Dangerfield	436	436	436
8	99				85																																																													492	Davis	NOT ENOUGH	NOT ENOUGH	511
9	85	90	85	86			84	84	86		83		92	86	91		82	89	87	85	83	91	87	89	96	87		84		89	86		93	89	82	87			86	92		81																								255	Edminson	428	428	428
10	84							92			87			89	79				87						86		88		78		91					77	82			85		84																								250	Frederick	419	419	419
11	84				91						86																								86																															430	Funari	NOT ENOUGH	NOT ENOUGH	430
12	91							95										91												103																																				475	Gosnell	NOT ENOUGH	NOT ENOUGH	475
13	85		95	87				84					94	95				85	88		93		85		88	90	96	82	84	88			91	87	94	84	79		88	86		92																								251	Gressell	429	429	429
14	85			77	90		86	86	92		86		87	91	85		86	94	79		92	83	79	94	92		87	82	81	94	92			90	95	93	91	84	91	87		84																								266	Hobbs	437	437	437
15	87	91		85	88	86		89	94	91	86	92	90		88		85	80	91		95	94	85	90	92	87	86	90	83	90	85		93	93	91	91	91	83	81	88		84																								255	Keep	427	427	427
16	85	80		81	88	86					84	88	94	91	89		85	83	86	83	88	94	83	85	89						92		88	94	80	79			79	83		76																								238	Kilroy	397	397	397
17	98		98	98	97			99		104	90	99	98		95				89	94		95	91	93		91	96	97	87	97	90		101			91	93			92		87																								285	Lincoln	464	464	464
18	95						95																														95																													475	Moderick	NOT ENOUGH	NOT ENOUGH	475
19	92	94	85		92	95	100	96	96		98	92		95	96		95	98	97	93	98	98	89	96	102	97	98	94	89	99	95		98	96		94	99	97		94		91																								290	Nassar	475	475	475
20	82	88	86	82	82				95	95	87		96	93	88			88	91	87	93	89	84	91			95	90	91	99			97	96	94	94	86					88																								370	Nigro	458	458	458
21	86			87											98																																																			444	Paladino	NOT ENOUGH	NOT ENOUGH	444
22	89	92	90	90		87	87	95	87	96	86	97		98	93					98	96	90	87	93	99		95	91	84				97	93	96		88	94	94			87																								372	Petro	459	459	459
23	78	81	74	74	81	82	81	75	78	80	78	74	83	77	75		78	81	79	82	81	86	75	76	78	75	81	85	73	81	75		81	77	78	74	81	77	80	78		77																								238	Roderick	393	393	393
24	85	89	82	85	86	90	85	86	91	94	84	87	100	84	93				90	85	93	84	83	91	87	74	86	88	85	87	89		88	87	92	87	88	90	93	89		90																								271	Sepic	450	450	450
25	95																																																																	475	Smigel	NOT ENOUGH	NOT ENOUGH	475
26	74		80		83				88	82	82	90					80	78							83	80	79		75	81	83		84	84	80	80	76	82		80		77																								238	Theberge	395	395	395
27	86	87	85	81		85	83	91	91	97	89						82	88	90	95		94		88	88	84	86	86	76	98	82				90	82	89	86		90		84																								257	Wallon	431	431	431
28	91							100	91	101	85		98	91	91		90	89	87	94	96		94	95	92	94	95	91	82	97	94		103	97	100		89	88	96	91		88																								273	Zeman	452	452	452
Golf

Cell Formulas
Range	Formula
Z3,AB3,AD3,AF3,AH3,AJ3,AL3,AN3,AP3,AR3,AT3,AV3,AX3,AZ3,BD3,BX3	Z3	=SUM(X3+7)
AA3,AC3,AE3,AG3,AI3,AK3,AM3,AO3,AQ3,AS3,AU3,AW3,AY3,BA3,BC3	AA3	=Y3+7
CC5:CC28	CC5	=LET(f,TAKE(FILTER(O5:BY5,O5:BY5>0),,-BZ$3),IF(COUNT(f)=BZ$3,SUM(f),"NOT ENOUGH"))
CD5:CD28	CD5	=LET(f,TAKE(TOROW(O5:BY5,1),,-BZ$3),IF(COUNT(f)=BZ$3,SUM(f),"NOT ENOUGH"))
CA5:CA28	CA5	=IFERROR(SUM(OFFSET($H5,0,LARGE(IF(I5:BY5<>"",COLUMN(I5:BY5))-8,$BZ$3),1,(45-LARGE(IF(I5:BY5<>"",COLUMN(I5:BY5))-8,$BZ$3)))),"NOT ENOUGH")

 

[Gulfcourse]

Thank you very much Peter, I really appreciate your help but am still having a problem. I want the range to start at the I column so I changed the formula to:
=LET(f,TAKE(FILTER(I5:BY5,I5:BY5>0),,-BZ$3),IF(COUNT(f)=BZ$3,SUM(f),"NOT ENOUGH"))
and pasted it into CD5 and it's returning "NOT ENOUGH"
I see your formula got the correct totals, since they are the same as the should be values, which I added by manually adding the last 5 scores.
I just tried pasting both your formulas into CD5 and got the same Not Enough message???

 

[Peter_SSs]

I see your formula got the correct totals,

Since you don't get the correct results with the same formula it suggests that our data is different.
Perhaps your 'blank' cells are not actually completely empty?
In your post #3 mini sheet cell BY5 appears blank. What happens if you put this formula in an empty cell somewhere?

=ISTEXT(BY5)

Another thing that could be happening is that your scores are Text values and not actual Numbers? What happens in the post #3 mini sheet if you put this formula in an empty cell somewhere (noting that cell BC5 appears to contain the Number 84)?

=ISNUMBER(BC5)

As an alternative to my previous formula, what happens if you put this formula in CD5?

=LET(f,TAKE(FILTER(I5:BY5,I5:BY5<>""),,-BZ$3),IF(COUNT(f)=BZ$3,SUM(f),"NOT ENOUGH"))

 

[Gulfcourse]

=LET(f,TAKE(FILTER(I5:BY5,I5:BY5<>""),,-BZ$3),IF(COUNT(f)=BZ$3,SUM(f),"NOT ENOUGH"))

The formula above worked and I am so thankful to you. It is much appreciated since I know enough Excel to be dangerous, but not enough to figure out what the problem was!

 

[Peter_SSs]

You're welcome. Glad it worked for you. Thanks for the confirmation.

I would still be interested to know the results of those two short formulas from my last post so I might understand why the earlier one did not work.

 

[Gulfcourse]

Hello Peter,

I put =ISTEXT(BY5) in and it returned 0.
I changed it to BG10 and got the circular reference message, hit OK and it returned 0. I changed it and pasted it into a few other cells and they returned 0 without the CR message.
I did the same with =ISNUMBER(BC5) in BC5, I did not clear the score out before pasting it in and got a circular reference message, clicked on okay and the cell was 0.
I changed BC5 to BD9, pasted in into that cell and the same thing, circular reference warning, click OK and the cell returned 0.
Before I change BC5 to BD9 I pasted =ISNUMBER(BC5) into BD9 and it returned TRUE.

I hope this helps explain the error in my ways to you, and you may be able to further educate me, since I have no idea why the cells were doing that.
THANK YOU once again!

Steve