Forum Discussion

Copper Contributor

Mar 14, 2022

Solved

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." Ho...

excel

HansVogelaar
Mar 14, 2022
elhudbox

Perhaps it is a rounding error. What is the result if you change the formula to

=IF(ROUND(N15-N44,4)=0,"ok","oops")

PeterBartholomew1

Silver Contributor

Mar 14, 2022

elhudbox

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.

elhudbox

Copper Contributor

Mar 15, 2022

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.

Ramakrishnan Rukmini
Copper Contributor
Mar 15, 2022
elhudbox
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