troubleshooting Question

Excel 2007 simple formula question

Avatar of RichNH
RichNH asked on
Microsoft Excel
3 Comments1 Solution361 ViewsLast Modified:
Hello, I have a simple situation here and it isn't working for me and I don't know why.
I have the following formula in a cell of a worksheet
=IF(E37-E38<=0.000000000000001,"FAIL","PASS")

This formula evaluates to "FAIL".

The values e37 and e38 are identical, if I just put in the formula =e37-e38 it equals zero, even if I expand the decimal places to the max of 30 decimal places.  The two cells are different in that one is the result of a formula and one is a static value entered in.  But they both evaluate to 0 no matter how many decimal places I show.

Yet on my original formula,
If I compare to 0.000000000000001 I get a "FAIL"
If I compare to 0.0000000000000001 I get a "PASS".

I have tried duplicating the situation in another worksheet and it works as expected.

Why does my original spreadsheet (which is fairly large and complicated in its entirety and which I would not like to recreate) not work and the simple example I put up work?  What other factors could be at play here that could affect the calculation of this simple formula that when I go from 15 to 16 decimal places the results of the IF statement change?

Rich
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros