Forum Discussion
Strange error when "if" function is used
I'm fighting whit a problem when I use "IF" function in a file like to the one attached...
If I write a simple formula like the ones in coloumn "E", the results are ever correct.
But, if i use the "IF" function to drive in different manner the same formula, like in coloumn "D", sometimes Excel return a strange error, like the ones in the cell written in red...
What is happening? What's wrong?
Thank you.
6 Replies
The number will be shown as zero, but actually that will be another (small) number. If compare to zero result will be FALSE.
Nothing goes wrong, but some of your calculations cause so-called floating point errors. E.g. your formula in D5 returns "0.00000000000001421085" which equals zero up to 13th decimal. By the way, your calculations don't seem very meaningful, but if you insist to do them you need to wrap them in a ROUND statement, e.g. like =ROUND(<your formula>,0)
Thank you for your answer.
OK, the file attached is only a simple exemple, but the issue is the same for every file that contain an "IF" function driving any other calculation.
Anyway, I can see that the error is very small and could be "managed" with round function, but in my mind is quite strange that a simple formula like a*100/a-100 return a result different from zero...
You could be surprised, but
=1*(0.5-0.4-0.1)also doesn't return zero. And that's not Excel only specific. More about this is here
Floating-point arithmetic may give inaccurate results in Excel
Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?”
