"IF" formula gives wrong answer

Y

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!
 
N

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!
 
R

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!
Click to expand...

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
 
Y

yhoy

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

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!
| >
| >
| >
 
Top