Why do different columns with same numbers produce different sum?

English
,
#1

I have two different currency amounts accruing that should equal to a certain amount. In Column A I have inputted the amount that the 2 amounts should equal to. In Column B and Column C I have entered the 2 amounts that should add up to Column A. In Column D, I inputted a formula to sum the Column B & C amounts. I can then compare the amounts listed in Column A & Column D to ensure that they match. In Row 4/Column A and Row 4 Column D, I entered a formula to sum Column A & D. THE PROBLEM: despite each column containing the same currency amounts, the summation formula is producing a different sum. WHY?

Spreadsheet has following:
Column A has data in cells 5 thru 40. The column cells are configured for currency.

Column B has data in cells 5 thru 40. The column cells are configured for currency.
Column C has data in cells 5 thru 40. The column cells are configured for currency.

Column D has the following formula for each respective cell 5 thru 40: =(B5+C5)*0.7 [for row 5]; =(B6+C6)*0.7 [for row 6] and so forth for each cell 5 thru 40. So in summation the column displays the sum of columns B & C. The column cells are configured for currency.

In Row 4 (Column A), I have the following formula: =SUM(A5:A40). The summed result = $10,051.51
In Row 4 (Column D), I have the following formula: =SUM(D5:D40). The summed result = $10,051.17

I have painstakingly confirmed the accuracy of every single number presented in Column D (i.e., the currency number in Column D looks exactly the same as that showing in Column A). I have used an independent calculator to add up each amount of each cell in Column A and the correct sum is $10,051.51. So why is the sum formula in Column D producing a bad number? Is there a way to fix this error?

#2

Well, you have 34 or 35 data rows and you are off by 34 cents. That could imply off by one penny in rounding error for each row. I have used spreadsheet programs to balance drawers before, and everything has to be wrapped in ROUND(…,2) in those cases. Remember, cell formatting is just for viewing, the values don’t actually round for calculation.

I suspect here you are multiplying a deviation with the .7, and based on your actual values (about $30 on average) that is throwing you off by a penny per row.

1 Like
#3

Can you suggest how I might modify the formula or how I input the data to solve this? You said to wrap in ROUND. Could you be more explicit in how I do that? I thought using Currency format would prevent this sort of problem.

#4

Formatting never changes the value in the cell. If you like change formatting from currency to date, than back to currency for example. No rounding to “days” will be done.

#5

For example change your
=(B5+C5)*0.7 to
`=ROUND((B5+C5)*0.7; 2)

1 Like
#6

Given there is already a previous calculation result in columns B and C, I might expect the rounding error to have occurred there, possibly pasted as truncated? Or as approximation of markup?

#7

Or even one more step and round everything, at least to see if the problem goes away, then back off and keep testing. Round every – single – operation for testing.

=ROUND(ROUND(B5+C5;2)*.7;2)

If I had to create a durable and correct currency system in a spreadsheet, I would round everything and put row/column sums and set up an automated cross check with column/row sums if there were any multiplication/division at all. Add down, then across, and across, then down and take the difference in a conditionally formatted cell that lights up if not zero. That also helps spot cells accidently damaged from errant mouse clicks, etc.

#8

@ Wanderer

Thank you so much!

using the formula pattern you exampled solved the problem.

#9

For background you might find Frequently asked questions - Calc: Accuracy problem - The Document Foundation Wiki useful reading.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.