Forum Discussion
Why Simple Formula Adding Two Cells Won't Work?
Hi. Could you please advise, why so simple formula like =E20+G20 won't work in case if number in cell G20 negative, but same value as in E20? In case of other number combinations it works.
Thank you.
Floating point operations usually give side effect around zero result expected, especially with logical operations. Well known example is
=1*(0.5-0.4-0.1)
which returns -2.78E-17. With this
=(1*(0.5-0.4-0.1)=0) returns FALSE however =( (1+1*(0.5-0.4-0.1))=1) returns TRUE
5 Replies
I guess the formula works, result is not as you expected.
As variant that could be floating point error, e.g.
http://www.excel-easy.com/examples/floating-point-errors.html
http://excelunplugged.com/2014/05/19/15-digit-limit-in-excel/
Hi Anastasiya,
Do you mean the result of E20 + G20 is showing up as zero? If yes, then that is simple arithmetic. Assuming E20 has 5 and G20 has -5, E20 + G20 = 5+(-5) = 5-5 = 0.
If you have a different view of what you are asking for, you might want to share a screenshot of an example.Thanks,
Bala..Not, of cause it is not so simple. 0, that what I expected it to be, and there was something strange. And actually may be this is not so simple formula as I called it, as in first cell was formula as well. But I just remembered something from years ago, and figured it.
Floating point operations usually give side effect around zero result expected, especially with logical operations. Well known example is
=1*(0.5-0.4-0.1)
which returns -2.78E-17. With this
=(1*(0.5-0.4-0.1)=0) returns FALSE however =( (1+1*(0.5-0.4-0.1))=1) returns TRUE
