"IF" formula gives wrong answer

[yhoy]

The formula is pretty simple -- if(A-b-c=0, "OK", "ERROR"). Use it on a
column and around 3-5% gives "ERROR" as result even though the statement is
true. Urgent help needed!

 

[Niek Otten]

If you format a cell wit =A-b-c to display 15 digits you'll probably find that isn't not exactly zero.

Look here for a possible explanation

http://support.microsoft.com/kb/78113

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| The formula is pretty simple -- if(A-b-c=0, "OK", "ERROR"). Use it on a
| column and around 3-5% gives "ERROR" as result even though the statement is
| true. Urgent help needed!

 

[Ron Rosenfeld]

The formula is pretty simple -- if(A-b-c=0, "OK", "ERROR"). Use it on a
column and around 3-5% gives "ERROR" as result even though the statement is
true. Urgent help needed!

Try

if(round(a-b-c,5) = 0, "OK","ERROR")

Change the 5 to some value that gives you sufficient precision for your
calculation.

Look up "rounding errors" in this NG and also in the MSKB
--ron

 

[yhoy]

Out of curiosity, I followed your instruction below and display 30 digits
and it's still all zero. Does that sound right?

 

[Niek Otten]

No. Follow Ron's advice

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| Out of curiosity, I followed your instruction below and display 30 digits
| and it's still all zero. Does that sound right?
|
|
| "Niek Otten" wrote:
|
| > If you format a cell wit =A-b-c to display 15 digits you'll probably find that isn't not exactly zero.
| >
| > Look here for a possible explanation
| >
| > http://support.microsoft.com/kb/78113
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | The formula is pretty simple -- if(A-b-c=0, "OK", "ERROR"). Use it on a
| > | column and around 3-5% gives "ERROR" as result even though the statement is
| > | true. Urgent help needed!
| >
| >
| >

 

[David Biddulph]

Try multiplying the result by 1E16 or something, and see what you get then.