1

Excel screenshot

In the screenshot above in Excel I have opened a CSV file of money transactions from my bank. The transaction date is in column A, the amount in B, and the bank's computed balance in D. I'm computing my own running balance in column F (=SUM($A$2:An)) and column H provides a highlight to indicate any discrepancies (=IF( Dn = "", TRUE, Fn=Dn ), unfortunately as the screenshot shows, Excel is saying that £898.15 does not equal £898.15.

I wanted to see if there was a rounding-error going on, so in column K I set the formula =Dn-Fn and then expanded the number of decimal places shown. Which shows a minute error of -0.00000000000272848.

Question 1: Where is this insignificant error coming from, and why does Excel suddenly consider the values different in row 206?

Question 2: Other rows have an identical discrepancy (e.g. 188 and 184 vs. row 206) so why does Excel complain all of a sudden?

Question 3: Why isn't Excel treating currency values as integral values and not floating-point, so there shouldn't be any error at all.

  • Are all three columns containing monetary values formatted as "currency" in Excel? – Kinnectus Mar 18 '15 at 8:05
  • @BigChris Correct, they're all formatted as Currency. – Dai Mar 18 '15 at 8:41
1

When you enter numbers in Excel, they are converted to an internal binary representation, for instance, 8.25 is translated into 1000.01, but 8.26 becomes 1000.01000010100011110101110000101000.

When you start adding numbers in Excel, the decimal numbers that you enter are converted to binary, the sum is made in binary, then translated to decimal back again. And that's the moment when you get some inaccurate numbers, such as the -0.00000000000272848 that you get in discrepancies between your numbers and Excel's. You will need to multiply that amount by billions to get a different amount in cents.

So, the test should be =IF(ABS(Calculated-Real)<0.01,"OK","Not Equal"). Until we get a decimal computer, you will get this mistake in any binary digital computer.

|improve this answer|||||
  • Just to let you know, check here for more detailed information about this issue. – Eddie Saltzman Apr 2 '15 at 20:07
0
  1. insignificant error is probably due to rounding. You see two decimal digits only in the columns from the bank, but maybe there are more, try to increase number of decimals there too.
  2. minimum difference to be identified by excel is not an absolute value, but it's related to the magnitude of numbers compared. You can read more here
  3. Reason behind using floating point numbers is generally the bigger flexibility of range covered, but I don't think it's important now as we can't change it.
|improve this answer|||||
  • I opened the original CSV files in plain-text editor and verified that all of the values have exactly 2 decimal places. I worked-around the problem by wrapping the arguments of SUM in ROUND( n, 6 ), but it feels like a hack. – Dai Mar 18 '15 at 8:42
0

This is a classic example of floating-point arithmetic error. It is caused by how Excel is interpreting the numbers, both as a causality of the sum function and as how you did it manually. More than likely the result is stored as a floating-point number after the sum function finishes, which (as you can see) doesn't perfectly match the actual manual calculation.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.