End of IF statement causing issue, what am I doing wrong?

[MFranken]

Trying to create a nested IF statement.  I'm having a problem with the end standard false result.

This formula works:

=IF(G3472="","",IF(40-H3472-I3472<0,0,40-H3472-I3472))

But the exact same formula where I replaced the standard number 40 with a specific cell returns a weird result (3.55271E-15 is what displays in the cell):

=IF(G3472="","",IF(G3472-H3472-I3472<=0,0,G3472-H3472-I3472))

Further trial and error revealed that if the result of G3472-H3472-I3472 = 0 exactly, the weird result noted above appears... if the amount is higher or lower than zero, the formula is working perfectly.

Ultimately, I want the formula cell to display nothing, 0, or the result of two cell values being subtracted from the first.

[#2]

@MFranken 

Firstly, in case you are not aware, that "weird result" is the Excel's text representation of a number in scientific notation.  The formula result is a fraction of 3.55271 divided by 1 trillion quadrillion (10¹⁵).  Why is the result not zero, you wonder?

Although people tend to use whole numbers (integers) for a lot of things, most numbers are real.  And most real numbers cannot be stored exactly in computer memory*; even common numbers such as one-third or one-tenth are not stored exactly. Excel does some extra work in rounding, but that's out at roughly the 15th decimal place. (That 15 should look familiar.)

* technically speaking, using the IEEE floating point number representation, which most computer hardware uses

You might not be comfortable with that inaccuracy, but that's how computers and almost all computer software are designed.

I suggest that you decide how close to zero you can consider the number as equivalent to zero. For example, if one billionth (1 divided by 1 billion, written as 1E-9) is close enough, you could use this Excel formula instead:

=IF(G3472="","",IF(G3472-H3472-I3472<=1E-9,0,G3472-H3472-I3472))
which Excel will likely convert to the harder-to-read exactly (because of all the zeros):
=IF(G3472="","",IF(G3472-H3472-I3472<=0.000000001,0,G3472-H3472-I3472))

Edited to correct the number name

[#3]

@MFranken This is a common problem caused by precision issues with the floating-point arithmetic method used by Excel. Please see: Floating-point arithmetic may give inaccurate results in Excel 

One solution is to use the ROUND function to round the final results to the desired number of decimal places. For example, to round to 2 decimal places, use:

=IF(G3472="", "", IF(G3472-H3472-I3472<0, 0, ROUND(G3472-H3472-I3472, 2)))

Also worth mentioning, the MAX function can be used in this situation to simplify the formula as follows:

=IF(G3472="", "", MAX(ROUND(G3472-H3472-I3472, 2), 0))

Cheers!