Forum Discussion
End of IF statement causing issue, what am I doing wrong?
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