addition of currency not accurate in numbers

Question

Level 1

14 points

addition of currency not accurate in numbers

I have a spreadsheet adding currency values. The results are off.

It must be a rounding error. I have found the single value in the series that is the culprit. This value is $400/26. When the result, $15.38 is added to a list of other values, the result is off by 1 cent. But if I overright the formula and replace with $15.36, I get the correct answer.

This seems pretty basic that should not require a work around. How can I get this fixed?

MacBook Pro (15-inch Late 2011), Mac OS X (10.7.3)

Posted on Jan 7, 2014 7:30 PM

Reply

Answer 1

Badunit

Level 7

25,802 points

Jan 7, 2014 8:02 PM in response to odontoid

There is nothing wrong.

400/26 = 15.3846153846154

The value you see displayed in the cell if you have it formatted as currency with 2 decimals is 15.38 . But that is just for display. The value that is actually in the cell is 15.3846153846154 and that is the value used in any calculations. This is the source of the "error" you are seeing but it is not an error, it is correct.

=ROUND(400/26, 2) = 15.38 . 15.38 is the number that will be displayed and it is the actual value in the cell and is the value that will be used in any calculations. You may want to use ROUND. It is not a workaround, it is how spreadsheets work. There is an exception, though. Excel (and maybe the free open source clones of Excel) can be set to "precision as displayed" which does the rounding without using ROUND. It is not the default setting in Excel, you have to go in and change it. Numbers does not have this setting.

I also note that if you replace the 400/26 with 15.36, you will be off by two cents (2.46153846154 cents to be exact). I'm guessing you have more than one cell with the same thing going on.

Reply