Sometimes Excel does not calculate numbers correctly and gives an “incorrect” result. Avoiding technical jargon, its basically because Excel rounds of the results of its calculation. This typically isn’t a problem because most users will not need the 10th decimal place, and would never notice the problem. However, the problem is quite noticeable for those who are doing some sort of data checks to avoid common mistakes in Excel.
The most common calculation check would be:
=[Cell 1]=[Cell 2]
This type of check is effective, because its easy to use and easy to spot any problems, with some conditional formatting. But in some cases, this type of check breaks down in Excel. (Try this in Excel.)
A1 = =1000000+1/3-1000000 A2 = =1/3 A3 =A1=A2
Cell A3 will actually be false. Now, increase the number of decimals shown in cells A1 and A2 to 15. You will see that Excel did not accurately calculate A1 properly past the first ~11 decimal places. For most users it would not matter, and they would not have encountered this problem before. But for Excel, the “=” means it has to be exact for the cell to return true.
The Solution
Instead of asking for an exact match, ask for something close. For example, if you are dealing with dollars, anything within a 0.000001 error should be fine, no? It’s not even a hundredth of a penny! Below is an example that could replace the cell A3 above. Note that the function abs (absolute value) must be there to change all negative signs to positive (in case A2 turns out better than A1).
=if(abs(A1-A2)