Excel if statement will not recognize a formula result in another cell if the result is not zero

Question

Hi there, I've researched for the past few days on the web but am beginning to think that this may be a quirk of Excel.

I cannot get my IF statement formula in column B of the picture below to work correctly.

What I am trying to do is:

If the Difference amount (cell C21) is NOT ZERO, then the word BAD should display in column B for all rows.

OTHERWISE if the different amount IS ZERO, then the word GOOD should display in column B for all rows.

The problem is that my if statement IS NOT recognizing the difference value if it is NOT zero..and the

note shows as "GOOD" when it SHOULD BE displaying as "BAD".

The values in column D are from FORMULAS. Column D, E, F and G data format is NUMBER with 4 decimal places.

Here's what I already checked:

a) I have auto calculate formula turned ON

b) There's no text in column D thru G

Here are the fomulas displayed:

Any help would be greatly appreciated!

Thanks,

Answer 1

AccessUser wrote:

If the Difference amount (cell C21) is NOT ZERO, then the word BAD should display in column B for all rows.

OTHERWISE if the different amount IS ZERO, then the word GOOD should display in column B for all rows.

---

First, you wrote your formula backwards.  Your formula is, essentially:

IF(INT(D21)<>0, "good", "bad")

It displays "good" when INT(D21) is __not__ zero, not when it __is__ zero.

So your first example seems right to you ("good") only because you thought (0.0000) would truncate to exact zero, when in fact it truncates to -1.

Likewise, the second example truncates to -1. It looks wrong to you ("good" instead of "bad") only because your formula is incorrect.  It should be, essentially:

=IF(INT(D21)=0, "good", "bad")

---

Second, looks can be deceiving due to formatting.

I suspect that you are misinterpreting "$ (0.0000)" as exact zero.

But in the Accounting format, it is a negative value that rounds to zero when displaying 4 decimal places.

(It should round to -0.0001. But that is an unrelated issue.)

In the Accounting format, "$ -" (dollar dash) is exact zero.

So both examples show a non-zero negative value in D21.

And you should expect INT(D21) to be the same value in both examples.

Ergo, you should expect the same result from the IF() expression.

If you want to round or truncate toward zero for negative as well as positive values, you should use ROUND(D21,0) or TRUNC(D21).

---

Third, it is remarkable that you expect D21 to be exact zero in the first example, if indeed you do, as your follow-up comment suggests, IMHO.

Note that formatting only affects the __appearance__ of a cell value. It does not affect that __actual__ cell value.

(Unless you set the option "Precision as displayed", which I do __not__ recommend.)

Note the SUM(D11:D20) is 7.00075 when formatted to 5 decimal places, the same as column E.

So you should expect a difference of -0.00005 for SUM(D10:D20) in the first example, and a difference of -0.00015 in the second example.

---

Finally, the reason why D21 does not display -0.0001 and -0.0002 when rounded to 4 decimal places is a quirk of computer binary arithmetic.

If you format D21 as Number with 19 decimal places, you will see that D21 is -0.0000499999999981071 in the first example, not -0.00005.

This is because Excel uses 64-bit binary floating-point to represent numerical values internally. Consequently, most decimal fractions cannot be represented exactly; and the approximate representation of the same decimal fraction might vary depending on the magnitude of the integer part.

This causes infinitesimal anomalies (differences from our expectations based on decimal arithmetic) when we do calculations with decimal fractions and when the result of calculations has a decimal fraction.

For that reason, when we expect a calculation to be accurate to some number of decimal places (5 in your example), we should explicitly round to that number of decimal places.  For example, ROUND(SUM(D10:D20),5).

Answer 2

AU,

re: formulas

The Int function rounds a number down to the nearest integer.

So:   (0.0001) which is a negative number is rounded down to -1 (which is not zero).

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)

Answer 3

Thanks Jim,

I actually tried that 1st without INT. Even without INT, I still have the same problem :(

Answer 4

AU,

re:  more formulas

Try...  =IF(C20="","",IF(D21<>0,"Good","Bad"))

You also need to be aware of differences between the Decimal and Binary (computer) math calculations.

A zero value in Decimal is not always a zero value in Binary.

See...

Floating Point Calculation...

http://www.cpearson.com/excel/rounding.htm

Quotes...

"There may be times that the value that you see on an Excel worksheet does not equal the value that you believe that it should be."...

"It is important to note that these errors and limitations on fractional numbers are not really errors at all.

Nor are they "bugs" in the programs.

These are well-known and well-documented limitations of the floating point arithmetic systems in almost every software package and hardware device."

'---

Jim Cone

Answer 5

Wow, first off, thanks for taking the time to write this detailed explanation. Being new to using INT, yes it's clear I did not understand how this would work and also that as you said what appears on the screen isn't the value. I will go back and remove the INT and change the rounding as you suggest.

Thanks so much for the help!