IF(ISERROR

[ozbeachbum]

I have a formula [=IF(ISERROR((B3/B4)),0,((B4-B3)/B4))] that returns the correct percentages for Columns B & F, but not D.
I need to adjust it so that results in B & F show as is and where D4 equals ZERO no matter what the amount in D3 is, the percentage for D2 would always be 100%.

EG: B 0.00% D 100.00% F 50.00%

Thanks in advance for any assistance.

241106 if error.xlsx
	B	C	D	E	F
2	0.00%		0.00%		50.00%
3	$ -		$ 0.50		$ 0.50
4	$ -		$ -		$ 1.00
5
6			100.00%
Blank

Cell Formulas
Range	Formula
B2,F2,D2	B2	=IF(ISERROR((B3/B4)),0,((B4-B3)/B4))

 

[Phuoc]

Try this:

Book1
	B	C	D	E	F
2	0%		100%		50%
3			$0.50		$0.50
4					$1.00
Sheet1

Cell Formulas
Range	Formula
B2,F2,D2	B2	=IF(B4=0,IF(B3=0,0,1),(B4-B3)/B4)

 

[ozbeachbum]

Hi Phuoc,
Thanks for your formula it works perfectly.
On entering it into the actual worksheet, I realised that there was one other situation I needed to account for, where both rows 3 & 4 have positive amounts and row 3 is greater than row 4.
Trusting that it is ok to ask if you could work it into your formula.
Cheers,
Dave.

241106 if error.xlsx
	B	C	D	E	F	G	H
2	0.00%		100.00%		50.00%		200.00%
3	$ -		$ 0.50		$ 0.50		$ 3.00
4	$ -		$ -		$ 1.00		$ 1.00
Blank

Cell Formulas
Range	Formula
B2,F2,D2	B2	=IF(B4=0,IF(B3=0,0,1),(B4-B3)/B4)

 

[Peter_SSs]

What about ..

=IF(B4,ABS(B4-B3)/B4,--(B3<>""))

 

[ozbeachbum]

Hi Peter,
Thanks as always for your suggestions.
As you can see on the mini sheet, it is giving the correct answers for D F & H, but B is not correct.

241106 if error.xlsx
	B	C	D	E	F	G	H
2	100.00%		100.00%		50.00%		200.00%
3	$ -		$ 0.50		$ 0.50		$ 3.00
4	$ -		$ -		$ 1.00		$ 1.00
5
6	0.00%
Blank

Cell Formulas
Range	Formula
B2,H2,F2,D2	B2	=IF(B4,ABS(B4-B3)/B4,--(B3<>""))

 

[Scott Huish]

Maybe:
=IF(AND(B4,B4+B3),(B4-B3)/B4,--OR(B3))

 

[Peter_SSs]

it is giving the correct answers for D F & H, but B is not correct.

Yes, sorry, I overlooked the currency formatting for 0
Just a small change I believe

24 11 07.xlsm
	B	C	D	E	F	G	H
2	0.00%		100.00%		50.00%		200.00%
3	0		0.5		0.5		3
4	0		0		1		1
Sheet5

Cell Formulas
Range	Formula
B2,H2,F2,D2	B2	=IF(B4,ABS(B4-B3)/B4,--(B3<>0))

Maybe:
=IF(AND(B4,B4+B3),(B4-B3)/B4,--OR(B3))

Scott, that doesn't produce the correct result for column H per posts 3 & 5.

 

[Scott Huish]

How does it not I got 2 which is 200%

0.00%		100.00%		50.00%		200.00%
0		0.5		0.5		3
0		0		1		1

 

[Peter_SSs]

How does it not I got 2 which is 200%

Don't know how you got 2 when when row 4 minus row 3 is negative, then divide by positive.
Here is my sheet with your formula

24 11 07.xlsm
	A	B	C	D	E	F	G	H
1
2		0.00%		100.00%		50.00%		-200.00%
3		0		0.5		0.5		3
4		0		0		1		1
Sheet5 (2)

Cell Formulas
Range	Formula
B2,H2,F2,D2	B2	=IF(AND(B4,B4+B3),(B4-B3)/B4,--OR(B3))

 

[Scott Huish]

OK. Then how about:
=IF(AND(B4,B4+B3),ABS((B4-B3)/B4),--OR(B3))

Book1
	B	C	D	E	F	G	H
2	100.00%		100.00%		50.00%		200.00%
3	1		0.5		0.5		3
4	0		0		1		1
Sheet1

Cell Formulas
Range	Formula
B2,H2,F2,D2	B2	=IF(AND(B4,B4+B3),ABS((B4-B3)/B4),--OR(B3))