numbers accuracy as displayed

No, you would need to round each value yourself using one of the rounding functions.

When you have values to sum, what you see in the cell is not necessarily the true contents.

If a cell contains a formula whose result is 12.3456
if the cell's format is set to 'display two decimals' you will see 12.35.
Assuming that you have ten cells with this value,
the true sum 123.456 displayed with the format 'display two decimals'the sum will appear as : 123.46 .

And of course, as you are seing ten cells displaying 12.35 you assumed that you will get 123.50.

This why I wrote many times : *_it's our duty to apply the correct rounding function to numbers which must be summed_*

Yvan KOENIG (VALLAURIS, France) dimanche 23 mai 2010 19:29:34

Yvan,

Excel has a preference setting, which applies only to the spreadsheet under which it is chosen, to set precision as displayed. In other words, it will round the values without requiring the ROUND function.

As far as I know, this forum is dedicated to Numbers.

I don't remember how many times I described the difference between what we see and what is stored in the cells.

When I write :

This is why I wrote many times : it's our duty to apply the correct rounding function to numbers which must be summed

I'm writing about Numbers !

Excel behaviour isn't my concern.

If someone want a spreadsheet behaving like Excel, Numbers is not the tool to use !

Yvan KOENIG (VALLAURIS, France) dimanche 23 mai 2010 23:33:34

Hi Badunit;

I have both Numbers '09 and Excel 2008. I really don't care which program I use, both are good. I just need a solution to this rounding issue as I use the spreadsheet to write a single cheque after calculating percentages from a list of numbers. Both programs seem to round down; additionally the total of the columns does not equal the actual total of each cell if added on an adding machine. Help! Is there an easy solution to this or do I have to resort to a laborious adding machine to achieve this seemingly simple task?

Seymourloo

Whenever there is the possibility of there being digits past the second decimal place, use one of the rounding functions to round the result to two decimal places. Usually you would use ROUND but there are other choices depending on how you want the rounding to occur. There is the possibility of extra decimal places whenever a percentage is calculated (calculating the amount of tax, for example), when doing division, and, due to how floating point math is done in a computer, whenever adding negative and positive floating point numbers together (i.e., such as dollars and cents) which can sometimes result in avoid minuscule errors on the order of 1E-17. To be safe you should probably round all calculations on currency. That is the solution. You can be the judge on whether that is "easy" or not.

seymourloo wrote:
Both programs seem to round down;

A quick check starting with 1.113 and incrementing by 0.001 shows Numbers to round the display of any number with a value ending .xx0 to .xx4 down, BUT to round the actual value (using =ROUND(number,2) ) down for any number in the range .xx0 to .xx5. To make the values ending x.xx5 round up, it's necessary to add a tiny amount to the value before rounding. Tiny as in 0.00000000001.

I don't have Excel on my Macs, but NeoOffice (and I suspect, OpenOffice.org) rounds up at x.xx5 and higher, down for values less than x.xx5, which agrees with what I was taught (and what I taught my elementary students) in school.

additionally the total of the columns does not equal the actual total of each cell if added on an adding machine. Help! Is there an easy solution to this or do I have to resort to a laborious adding machine to achieve this seemingly simple task?

As Yvan explained earlier, numbers DISPLAYED with two decimal places may represent actual values that have more than two decimal places, and what's in those places may make the actual value larger or smaller that what you see. It's the actual value, not the displayed value, that is used in calculations.

If you want the displayed value to be the actual value in the cell, the only way to ensure that is to ROUND the actual value to the same number of decimals places as you are displaying. When you've done that, the spreadsheet's addition and subtraction should agree with the adding machine's results.

Regards,
Barry