Excel if statement will not recognize a formula result in another cell - Microsoft Community

hi there, i've researched past few days on web beginning think may quirk of excel.

i cannot if statement formula in column b of picture below work correctly.

what trying is:

if difference amount (cell c21) not zero, word bad should display in column b rows.
otherwise if different amount zero, word should display in column b rows.

the problem if statement not recognizing difference value if not zero..and the
note shows "good" when should displaying "bad".

the values in column d formulas. column d, e, f , g data format number 4 decimal places.

here's checked:
a) have auto calculate formula turned on
b) there's no text in column d thru g

here fomulas displayed:

any appreciated!

thanks,

accessuser wrote:

if difference amount (cell c21) not zero, word bad should display in column b rows.
otherwise if different amount zero, word should display in column b rows.

-----

first, you wrote formula backwards.  formula is, essentially:

if(int(d21)<>0, "good", "bad")

it displays "good" when int(d21) __not__ zero, not when __is__ zero.

so first example seems right you ("good") because thought (0.0000) truncate exact zero, when in fact truncates -1.

likewise, second example truncates -1. looks wrong ("good" instead of "bad") because formula incorrect.  should be, essentially:

=if(int(d21)=0, "good", "bad")

-----

second, looks can deceiving due formatting.

i suspect misinterpreting "$ (0.0000)" exact zero.

but in accounting format, negative value rounds 0 when displaying 4 decimal places.

(it should round -0.0001. unrelated issue.)

in accounting format, "$ -" (dollar dash) exact zero.

so both examples show non-zero negative value in d21.

and should expect int(d21) same value in both examples.

ergo, should expect same result if() expression.

if want round or truncate toward 0 negative positive values, should use round(d21,0) or trunc(d21).

-----

third, remarkable expect d21 exact 0 in the first example, if indeed do, follow-up comment suggests, imho.

note formatting affects __appearance__ of cell value. not affect __actual__ cell value.

(unless set option "precision displayed", __not__ recommend.)

note sum(d11:d20) 7.00075 when formatted 5 decimal places, same column e.

so should expect difference of -0.00005 sum(d10:d20) in first example, , difference of -0.00015 in second example.

-----

finally, reason why d21 not display -0.0001 , -0.0002 when rounded 4 decimal places quirk of computer binary arithmetic.

if format d21 number 19 decimal places, see d21 -0.0000499999999981071 in first example, not -0.00005.

this because excel uses 64-bit binary floating-point represent numerical values internally. consequently, decimal fractions cannot represented exactly; , approximate representation of same decimal fraction might vary depending on magnitude of integer part.

this causes infinitesimal anomalies (differences our expectations based on decimal arithmetic) when calculations decimal fractions , when result of calculations has decimal fraction.

for reason, when expect calculation accurate number of decimal places (5 in example), should explicitly round that number of decimal places.  example, round(sum(d10:d20),5).

Office / Excel / Windows other / Office 2010