As I discussed in Crossfooting, numbers in Excel can have slight inaccuracies. This is due to the binary-decimal conversions that take place. In most cases, you won’t ever notice that there’s a problem because the type of operation you’re doing won’t show a problem. If, for instance, you’re just summing some numbers and those numbers look like 147.65000000001 but only show 147.65, the results of your sum will still be correct – at least what you see as the result.

One place where this really can bite you is when you’re comparing two numbers. Kurt supplies an example where the numbers come from a CSV file. A formula is used to make sure the CSV file (which doesn’t contain formulas) actually works as if it contained formulas.

The numbers in A:D are imported numbers and are all two decimal places. The formula in E is

In E4 where it’s false, I check the calculations. F4 is =B4-C4, G4 is =D4-D3 and H4 and I4 are the results of a Copy – Paste Special – Values from F4 and G4. You can see I4 has a little precision problem.

The way to fix this is to modify the formula that checks for equality. The first step is to determine how much precision you need. If you’re dealing with currency, you only need two decimal places. You would modify the formula to read:

Rounding to two decimal places gives you the precision you need and ignores those little errors. Only you can determine how much precision you need, but you can round to whatever that is.