Formula errro

[Zubair]

Hi Experts,

What mistake I am doing in formula in cell F5, it is showing #value! instead of zero?

%.xlsx
	A	B	C	D	E	F	G	H
1	Location	Cost
2		2022	2022	2021	Actuals 22 vs plan		Actual 22 vs Actuals 21
3		Actuals	Plan	Actuals	Value	%	Value	%
4	A	(3,146)	(3,651)	(3,000)	505	14%	(146)	-5%
5	B	-	-	(800)	-	#VALUE!	800	100%
Sheet1

Cell Formulas
Range	Formula
E4:E5	E4	=IFERROR(+B4-C4,"")
F4:F5	F4	=-IFERROR(+B4/C4-1,"")
G4:G5	G4	=IFERROR(+B4-D4,"")
H4:H5	H4	=-IFERROR(+B4/D4-1,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F4:H5	Cell Value	<0	text	YES
F4:H5	Cell Value	>0	text	YES
E4:E5	Cell Value	<0	text	YES
E4:E5	Cell Value	>0	text	YES

 

[hrayani]

Hi,
Remove the "-" sign after "="

=IFERROR(+B5/C5-1,"")

 

[Zubair]

Hi,
Remove the "-" sign after "="

=IFERROR(+B5/C5-1,"")

Hi Hrayani - Many thanks for the quick response.

I have applied as you suggested in F4 & F5 and getting the result in F4 negative which as actually a cost saving, and it should appear positive 14%?

%.xlsx
	A	B	C	D	E	F	G	H
1	Location	Cost
2		2022	2022	2021	Actuals 22 vs plan		Actual 22 vs Actuals 21
3		Actuals	Plan	Actuals	Value	%	Value	%
4	A	(3,146)	(3,651)	(3,000)	505	-14%	(146)	-5%
5	B	-	-	(800)	-		800	100%
Sheet1

Cell Formulas
Range	Formula
E4:E5	E4	=IFERROR(+B4-C4,"")
F4:F5	F4	=IFERROR(+B4/C4-1,"")
G4:G5	G4	=IFERROR(+B4-D4,"")
H4:H5	H4	=-IFERROR(+B4/D4-1,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F4:H5	Cell Value	<0	text	YES
F4:H5	Cell Value	>0	text	YES
E4:E5	Cell Value	<0	text	YES
E4:E5	Cell Value	>0	text	YES

 

[Georgiboy]

=IFERROR(1-B4/C4,"")

 

[Zubair]

=IFERROR(1-B4/C4,"")

Thanks, Georgiboy its working.