Formula Showing #Value

[ianharper68]

Hi All ....

I have the following formula and it works fine except in blank cells can anybody help please:

=IF(Q8=0,"-",INDEX(Calculations!$C$13:$E$13,XMATCH(Q8,Calculations!$C$12:$E$12,-1))*(IF(Q8<2,1,Q8)))*[@[Chargeable Days]]

 

[Asbestos_Jen]

Is Q8 actually blank, or is a formula returning a result of "-" or ""?
If so, I'd change the beginning of your IF statement like so:

=IF(or(Q8=0,Q8="-",Q8=""),"-",INDEX(Calculations!$C$13:$E$13,XMATCH(Q8,Calculations!$C$12:$E$12,-1))*(IF(Q8<2,1,Q8)))*[@[Chargeable Days]])

 

[ianharper68]

Is Q8 actually blank, or is a formula returning a result of "-" or ""?
If so, I'd change the beginning of your IF statement like so:

=IF(or(Q8=0,Q8="-",Q8=""),"-",INDEX(Calculations!$C$13:$E$13,XMATCH(Q8,Calculations!$C$12:$E$12,-1))*(IF(Q8<2,1,Q8)))*[@[Chargeable Days]])

I have just tested it and unfortunately it doesn't work

 

[Asbestos_Jen]

We can't see your column and row labels with the picture. Can you upload some of your data using XL2BB?

 

[Alex Blakenburg]

@ianharper68 I looked at your previous post and you are creating a compounding problem for yourself.
You don't want to be inserting a "-" in what is intended to be a numeric data column (field).

Go back to your Total Kgs column G in your previous thread and change the column format to something like Custom > General;[Red] General ;"-"
or #,##0.##;[Red] - #,##0.## ;"-" (put in as many ## after the decimal as you think you will need).
Change to dashes to 0 (the custom format will display it as "-")
Change the formula to

=IF(OR(G5=0,G5=""),0,INDEX(H5:O5,XMATCH(G5,$H$4:$O$4,-1))*(IF(G5<50,1,G5)))

In the above change the format of both columns to Custom > _-[$£] * #,##0.00_-;-[$£] * #,##0.00_-;_- * "-"??_-;_-@_-
In Domestic Handling In charges, if you have a formula in that column returning "" change that to 0 (without any quotes). If you have manually types in "-" then change it to 0.

In the calculation column change it to

=IF(Q8=0,0,INDEX(Calculations!$C$13:$E$13,XMATCH(Q8,Calculations!$C$12:$E$12,-1))*(IF(Q8<2,1,Q8)))*[@[Chargeable Days]]

 

[ianharper68]

XL2BB says the range is too big to post on here

 

[Alex Blakenburg]

Highlight the area with the data, click on XL2BB > Mini Sheet.
Then paste it into a post.

 

[ianharper68]

So here is the main columns that I have, there are more but XL2BB wouldn't allow me to copy more

New EDCR Master Worksheet FINAL.xlsx
	L	M	N	O	P	Q	R	S	T			W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
44	1	71	60	36	0.15	0.17	11.70	11.70	NO						37	30/9/24	10/4/24	3	1	$ 20.00	$ 3.00	$ 4.00	$ 1.50
45	1	120	80	170	1.63	0.39	209.00	209.00	NO					J240317/23	38	30/9/24	10/4/24	3	1	$ 62.70	$ 9.41	$ 14.63	$ 1.50
46	1	120	80	168	1.61	0.39	183.00	183.00	NO						39	30/9/24	10/4/24	3	1	$ 54.90	$ 8.24	$ 12.81	$ 1.50
47	1	120	80	162	1.56	0.39	190.00	190.00	NO						40	30/9/24	10/4/24	3	1	$ 57.00	$ 8.55	$ 13.30	$ 1.50
48					0	0						-					10/4/24	3		-	-	-	#VALUE!
49					0	0						-					10/4/24	3		-	-	-	#VALUE!
50					0	0						-					10/4/24	3		-	-	-	#VALUE!
51					0	0						-					10/4/24	3		-	-	-	#VALUE!
52					0	0						-					10/4/24	3		-	-	-	#VALUE!
53					0	0						-					10/4/24	3		-	-	-	#VALUE!
54					0	0						-					10/4/24	3		-	-	-	#VALUE!
55					0	0						-					10/4/24	3		-	-	-	#VALUE!
56					0	0						-					10/4/24	3		-	-	-	#VALUE!
57					0	0						-					10/4/24	3		-	-	-	#VALUE!
MASTER SHEET

Cell Formulas
Range	Formula
P44:P57	P44	=M44*N44*O44/1000000
Q44:Q57	Q44	=(M44*N44/2.45)/10000
AB44:AB57	AB44	=Dashboard!$Q$27
AC44:AC57	AC44	=$AC$3
AD44:AD57	AD44	=IF(AA44="","",AB44-AA44-AC44)
AE44:AE57	AE44	=IF(T44="NO",IF(S44=0,"-",INDEX(Calculations!$C$5:$J$5,XMATCH(S44,Calculations!$C$4:$J$4,-1))*(IF(S44<50,1,S44))),IF(S44=0,"-",INDEX(Calculations!$C$28:$J$28,XMATCH(S44,Calculations!$C$27:$J$27,-1))*(IF(S44<50,1,S44))))
AF44:AF57	AF44	=IF(S44=0,"-",AE44*Calculations!$M$5)
AG44:AG57	AG44	=IF(T44="NO",IF(S44=0,"-",INDEX(Calculations!$C$9:$J$9,XMATCH(S44,Calculations!$C$8:$J$8,-1))*(IF(S44<50,1,S44))),IF(S44=0,"-",INDEX(Calculations!$C$32:$J$32,XMATCH(S44,Calculations!$C$31:$J$31,-1))*(IF(S44<50,1,S44))))
AH44:AH56	AH44	=IF(Q44=0,0,INDEX(Calculations!$C$13:$E$13,XMATCH(Q44,Calculations!$C$12:$E$12,-1))*(IF(Q44<2,1,Q44)))*[@[Chargeable Days]]
AH57	AH57	=IF(Q57=0,0,INDEX(Calculations!$C$13:$E$13,XMATCH(Q57,Calculations!$C$12:$E$12,-1))*(IF(Q57<2,1,Q57)))*Tabela1[@[Chargeable Days]]
W48:W57	W48	=V48*0.15+V48

Cells with Data Validation
Cell	Allow	Criteria
T44:T56	List	YES,NO

 

[Alex Blakenburg]

Which column is "Chargeable Days" on a line that has the #Value error what is in that column as the value ?
Is it "-" ?
I would change the formula in those columns to return 0 instead of "-" and use custom formatting to make it look like "-".

To try and prove that is the issue you could try this:

=IF(Q44=0,0,INDEX(Calculations!$C$13:$E$13,XMATCH(Q44,Calculations!$C$12:$E$12,-1))*(IF(Q44<2,1,Q44)))*N([@[Chargeable Days]])

(wrapping Chargeable days in N() will force it to be zero if it is not numeric.)

 

[ianharper68]

Chargeable days column is Z

That works fine and the error in the empty cells disappears . However I have noticed I made and initial error in the original calculation. I have two charges based on ADR and Non ADR (Dangerous Goods). In the Collection Charges and Handling Charges the formulas I have work perfect. The Storage charges however needs to reflect the ADR (Column T) then if the is No there is one charge (Calculations!$C$13:$E$13,XMATCH(Q8,Calculations!$C$12:$E$12) non ADR and then Yes (Calculations!$C$36:$E$36,XMATCH(Q8,Calculations!$C$35:$E$35) for ADR then it needs to times the Chargeable Days (Column Z)

I am useless with formulas and can't work it out

New EDCR Master Worksheet FINAL.xlsx
	L	M	N	O	P	Q	R	S	T			W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
7	COLLIE	L	W	H	CBM	LDM	NTW	GRW	ADR			POD	COC	Our Collection Order Ref:	Label Number	Date Goods In	Date Goods out	Free Days	Chargeable Days	Domestic Collection Charges	FSC %	Domestic Handling In Charges	Domestic Storage Charges
8	1	47	33	33	0.05	0.06	16.50	16.50	YES			Yes	Yes	J240317/01	1	26/8/24	10/4/24	3	36	$ 30.00	$ 4.50	$ 6.00	$ 54.00
9	1	187	38	29	0.21	0.29	19.00	19.00	NO			Yes	Yes	J240317/02	2	27/8/24	10/4/24	3	35	$ 20.00	$ 3.00	$ 4.00	$ 52.50
10	1	24	24	17	0.01	0.02	6.75	6.75	NO			Yes	Yes	J240317/03	3	27/8/24	10/4/24	3	35	$ 20.00	$ 3.00	$ 4.00	$ 52.50
11	1	80	60	73	0.35	0.20	477.00	477.00	NO				Yes		4	27/8/24	10/4/24	3	35	$ 133.56	$ 20.03	$ 28.62	$ 52.50
12	1	80	60	73	0.35	0.20	477.00	477.00	NO				Yes		5	27/8/24	10/4/24	3	35	$ 133.56	$ 20.03	$ 28.62	$ 52.50
13	1	235	226	73	3.88	2.17	421.00	421.00	NO			Yes	Yes	J240317/04	6	27/8/24	10/4/24	3	35	$ 117.88	$ 17.68	$ 25.26	$ 197.27
14	1	110	100	80	0.88	0.45	926.00	926.00	NO			Yes	Yes	J240317/05	7	27/8/24	10/4/24	3	35	$ 240.76	$ 36.11	$ 46.30	$ 52.50
15	1	24	24	17	0.01	0.02	3.00	3.00	NO				Yes		8	29/8/24	10/4/24	3	33	$ 20.00	$ 3.00	$ 4.00	$ 49.50
16	1	32	32	19	0.02	0.04	17.00	17.00	NO			Yes	Yes	J240317/06	9	29/8/24	10/4/24	3	33	$ 20.00	$ 3.00	$ 4.00	$ 49.50
17	1	125	55	82	0.56	0.28	180.00	180.00	NO			Yes	Yes	J240317/07	10	29/8/24	10/4/24	3	33	$ 54.00	$ 8.10	$ 12.60	$ 49.50
18	1	100	100	50	0.50	0.41	65.00	65.00	NO			Yes	Yes	J240317/08	11	29/8/24	10/4/24	3	33	$ 20.80	$ 3.12	$ 5.20	$ 49.50
19	1	150	80	220	2.64	0.49	106.00	106.00	NO				Yes		12	29/8/24	10/4/24	3	33	$ 31.80	$ 4.77	$ 7.42	$ 49.50
20	1	150	80	220	2.64	0.49	106.00	106.00	NO				Yes		13	1/9/24	10/4/24	3	30	$ 31.80	$ 4.77	$ 7.42	$ 45.00
21	1	237	235	182	10.14	2.27	915.00	915.00	NO			Yes	Yes	J240317/09	14	2/9/24	10/4/24	3	29	$ 237.90	$ 35.69	$ 45.75	$ 171.40
22	1	117	102	95	1.13	0.49	500.00	500.00	NO			Yes	Yes	J240317/10	15	3/9/24	10/4/24	3	28	$ 130.00	$ 19.50	$ 25.00	$ 42.00
MASTER SHEET

Cell Formulas
Range	Formula
P8:P22	P8	=M8*N8*O8/1000000
Q8:Q22	Q8	=(M8*N8/2.45)/10000
AB8:AB22	AB8	=Dashboard!$Q$27
AC8:AC22	AC8	=$AC$3
AD8:AD22	AD8	=IF(AA8="","",AB8-AA8-AC8)
AE8:AE22	AE8	=IF(T8="NO",IF(S8=0,"-",INDEX(Calculations!$C$5:$J$5,XMATCH(S8,Calculations!$C$4:$J$4,-1))*(IF(S8<50,1,S8))),IF(S8=0,"-",INDEX(Calculations!$C$28:$J$28,XMATCH(S8,Calculations!$C$27:$J$27,-1))*(IF(S8<50,1,S8))))
AF8:AF22	AF8	=IF(S8=0,"-",AE8*Calculations!$M$5)
AG8:AG22	AG8	=IF(T8="NO",IF(S8=0,"-",INDEX(Calculations!$C$9:$J$9,XMATCH(S8,Calculations!$C$8:$J$8,-1))*(IF(S8<50,1,S8))),IF(S8=0,"-",INDEX(Calculations!$C$32:$J$32,XMATCH(S8,Calculations!$C$31:$J$31,-1))*(IF(S8<50,1,S8))))
AH8:AH22	AH8	=IF(Q8=0,0,INDEX(Calculations!$C$13:$E$13,XMATCH(Q8,Calculations!$C$12:$E$12,-1))*(IF(Q8<2,1,Q8)))*N([@[Chargeable Days]])

Cells with Data Validation
Cell	Allow	Criteria
T8:T22	List	YES,NO