Casting Error Check - returning false when it should be true

[Mtyler]

Hi

I'm using some simple true/false checks to ensure that items are casting/adding properly in my spreadsheets, eg =P49=P50 (true when total casts correctly, false when not).

However it is giving me an answer of false, when it appears it should be true. In the above example, =P49-P50 gives me 0. And this is actually zero (formatted as "-") rather than 0.0000001. Yet my error checking formula still gives me a FALSE outcome.

I've had this problem before and have never been able to get to the bottom of it - just wondering whether this is an Excel 2003 bug?

Thanks

Matt

 

[villy]

as far as I know 0 is different for 0.0000001. it might cause you to get false because they are not really equal..
or if it is somehting else maybe you could give more clear example to be checked
Thanks

 

[Mtyler]

Villy - that's my point. My variance is actually 0.0 (recurring) rather than 0.00001. I've based that conclusion on the formatting options (+ve;-ve;zero), and it's returning the format for zero.

 

[Peter_SSs]

Matt

My guess is that you are suffering from the fact that Excel does not always calculate exactly. References:
http://support.microsoft.com/kb/78113
http://blogs.office.com/b/microsoft-excel/archive/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers.aspx

I suggest that you change your formula to something like:
=ABS(P49-P50)<0.00001

 

[villy]

Thanks Peter firs time I saw this. great.

 

[Mtyler]

Matt

My guess is that you are suffering from the fact that Excel does not always calculate exactly. References:
http://support.microsoft.com/kb/78113
http://blogs.office.com/b/microsoft-excel/archive/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers.aspx

I suggest that you change your formula to something like:
=ABS(P49-P50)<0.00001

Thanks Peter - I wondered whether something like this existed.