Forum Discussion
Excel shows an incorrect answer
Excel is showing an incorrect answer. I have a simple formula: =IF(N15=N44,"ok","oops") Accordingly, if the number in cell N15 is the same as the number in cell N44, the answer should be "ok." However, it is not. Even if when the numbers in the two cells are exactly the same, the answer is "oops."
This problem has persisted since a previous version of Excel (I recently updated it, but the problem continues).
Further information: when I use the Formula Builder to enter the formula, whatever cell number I put in after the = sign is red in color. Here, the N44 is red. However, if I reverse the numbers and put in N44=N15, the N15 would be red.
There are no other error messages and there are no circular references. N15 is an input number. N44 is the sum of two other cells (neither of which refer to N15).
What the heck is going on?
Perhaps it is a rounding error. What is the result if you change the formula to
=IF(ROUND(N15-N44,4)=0,"ok","oops")
6 Replies
For the test to be meaningful both N15 and N44 need to be integers. Otherwise the result will depend upon minute rounding errors that are an inherent property of all computer calculations. A standard approach is to test
= IF(ABS(N15-N44)<eps, "OK", "Oops")where 'eps' is larger than any likely rounding error but very small relative to N15 and N44. Something you should not do, is to round the values themselves because this will simply introduce new errors that could swamp your calculations.
- I formatted both N15 and N44 to eliminate the decimals, so both cells had integers (whole numbers, as i understand it). The error is still there.
The different answer is because, excel accurately compares the values up to 14 decimal points (floating point values). Any calculation (floating point calculation) in excel gives solution upto 14 decimal accuracy. Hence the solution (you require) lies in the accuracy you specify for comparison. I have made a video explaining the same. Hope this helps in understanding excel better. Cheers. Ramakrishnan V
https://youtu.be/ikp66l_54JA
Perhaps it is a rounding error. What is the result if you change the formula to
=IF(ROUND(N15-N44,4)=0,"ok","oops")
- Interesting! I have no idea why it would be a rounding error because (1) the red (which I think indicates a problem) shows up in the formula without regard to the numbers entered in the cells, and (2) the numbers to match have two decimals (e.g. 5,610.13) which are not rounded from other numbers.
However, changing the formula as you suggested fixed the problem!!
So, I still have no idea why this problem occurred (it clearly should not have), but I will go with the fix! Hopefully, it will continue to work as further columns that use the same formula are populated with data.
Thanks!- OH, WAIT!!! That wasn't the answer. If I changed the numbers to the right of the decimal, I still got an "ok" answer even if it should have said "oops."
For example, 5,610.14 in cell N15 with 5,610.13 in cell N44 should have said "oops" (because they are different numbers), but they still said "ok." That's wrong.
Back to the drawing board.
