Iferror miscalculation

[alanshep]

Interesting one for you all - Excel 365.

If I use a simple formula to subtract 1-1 I get, as you would expect a 0 (Row 2)

However, if I nest this inside an iferror formula I get a very strange answer (yellow cell). (Row 3)

Anyone any thoughts on why this is happening?

 

[Joe4]

Yep, that is the famous floating-point arithermetic error.
See here: Floating-point arithmetic may give inaccurate result in Excel - Office

I usually get around it by using the Round function, as described in that article.

 

[alanshep]

Interesting one for you all - Excel 365.

If I use a simple formula to subtract 1-1 I get, as you would expect a 0 (Row 2)

However, if I nest this inside an iferror formula I get a very strange answer (yellow cell). (Row 3)

Anyone any thoughts on why this is happening?

Yep, that is the famous floating-point arithermetic error.
See here: Floating-point arithmetic may give inaccurate result in Excel - Office

I usually get around it by using the Round function, as described in that article.

Thanks Joe, for the quick answer!
But my poor head is a bit confused. 1-1 is a simple 0, there should be no decimal places floating or otherwise.
Why is this only happening inside the iferror formula and not when the calculation is not nested? There still should be no rounding or data truncation.

 

[Alex Blakenburg]

I am using 365 and I am not getting that.

Book3
	A	B	C	D
1	Input	Result	Formula used	Test
2	1	0	=1-A2
3	1	0.000000000000000000000000000000	=IFERROR(1-A3,0)	TRUE
Sheet1

Cell Formulas
Range	Formula
B2	B2	=1-A2
C2:C3	C2	=FORMULATEXT(B2)
B3	B3	=IFERROR(1-A3,0)
D3	D3	=ROUND(B3,2)=B3

 

[Joe4]

Thanks Joe, for the quick answer!
But my poor head is a bit confused. 1-1 is a simple 0, there should be no decimal places floating or otherwise.
Why is this only happening inside the iferror formula and not when the calculation is not nested? There still should be no rounding or data truncation.

Note that you are NOT doing 1-1.
You are doing 1-A3.

So you are not subtracting the same thing from itself.
You are subtracting two different objects (which appear to be the same, but are not for the reasons described in the article).