Excel rounding problem

Hello, sorry my english is not too good, hopefully somebody understands this?

Problem with rounding, Excel 2003:

A...........B
11325 11
11814 12
11769 12
11552 12
46460 47

Column B is column A rounded to thousands, the last row is sum of the numbers above. Obviously, the rounded total is not correct.

Question: How to format cells in column B so that despite of rounding, accuracy in calculation is maintained and the result would be rounded to 46?

Problem: Even if the accurary is maintained, the results seems wrong on display, when looking just the column B: 11+12+12+12<>46. How to solve this? It seems to me that the only way would be to round e.g. the last number 11552 slightly incorrectly to 11: 11+12+12+11=46. How to tell this to Excel, to round numbers incorrectly so that accuracy on display would be achieved, beginning from the number, where the original rounding error would be smallest?

Many thanks in advance!

Hi

Amend B1 formula to:

=A1

and copy down. Chnage the number format (Format>Cells>Number tab) to Custom:

0,;-0,;0

Only the display is thus rounded, and not the actual numbers themselves.

Richard

Hello,

Somehow this doesn't seem to work, with custom format "0,;-0,;0" e.g. number

"85 323 819,31"

displays as:

"85323819,"

?

Why do you have a comma between your ten's place and your hundreds instead of between hundreds and thousands? When you type in your number are you leaving spaces as you did in this forum? If so, it's going to format your entry as text and not as a number. Please clarify.

ChemistB

Hello !

I type in the number as usual without spaces, like this:

85323819,31

Excel shows space as a thousand separator and comma as decimal separator:

85 323 819,31

I guess this is more like european way of showing large numbers, american would look like this:

85,323,819.31

?

Ahhhh, it must be then that the formatting that Richard gave you would be the American way to have Excel begin at the thousands place. In

0,;-0,;0

the commas represent the thousands place holder.

Try looking in the Help under "Create a Custom Number format" to see how it is done in your system.

ChemistB