How do I get Excel to correctly add a column of numbers that have.
 

How do I get Excel to correctly add a column of numbers that have each been
rounded to 2 decimals? It seems to always remember the unrounded figures;
therefore, the sum of the column of figures is never correct. It's always off
a few decimal points. This seems an amazing short-coming for a spreadsheet
program. Is there a fix for this problem?

I doubt the values have been rounded, if you mean that have formatted the
values to show
2 decimals, that is hardly the same. Rounded would mean a formula like
=ROUND(A2,2)

For an explanation and a possible solution

http://www.mcgimpsey.com/excel/pennyoff.html

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"ljontheroad" wrote in message
...
How do I get Excel to correctly add a column of numbers that have each
been
rounded to 2 decimals? It seems to always remember the unrounded figures;
therefore, the sum of the column of figures is never correct. It's always
off
a few decimal points. This seems an amazing short-coming for a spreadsheet
program. Is there a fix for this problem?

Are the numbers actually rounded off using the ROUND
function or are the cells just formatted to show 2 decimal
places?

Lee

-----Original Message-----
How do I get Excel to correctly add a column of numbers
that have each been
rounded to 2 decimals? It seems to always remember the
unrounded figures;
therefore, the sum of the column of figures is never
correct. It's always off
a few decimal points. This seems an amazing short-coming
for a spreadsheet
program. Is there a fix for this problem?
.

If I read it correctly, the rounding of the underlying data appears only in
the cell formatting, ie the way that the numbers are displayed. The
underlying data has not been rounded but remains computed or entered to a
greater precision. The function that you are using to total the figures
acts on the underlying data not the displayed data.

I can think of three ways around the problem. There may be more.

Option 1:
From the menu bar, select Tools/Options/Calculation and check(tick) the box
"Precision as displayed".
I dislike this option, because it may act on cells to which it was not
intended, and you have to be permanently conscious of this setting when
doing anything with the workbook. That is just a personal view and it may
be fine for you.

Option 2:
In the cell containing the total enter as an array formula
=SUM(ROUND(range,2))
where "range" is the address or name of the range to be SUMmed
To enter it as an array formula, hold down the Control+Shift keys while
hitting the Enter key.
In the formula bar the result will be displayed surrounded by {Curly
brackets} to confirm that it has been array entered.
The effect of this will be to round the data in each element of range to 2
decimal places (ie consistent with the way that it is displayed).

Option 3:
Use the =ROUND(expression,2) function in each cell where the data is
calculated to more than 2 decimal places but displayed as two decimal
places, and then use =SUM(range) as normal to get the total. This simulates
option 1 above but provides more control over which cells are affected.
You may still want to round the total, ie
=ROUND(SUM(range),2)
because errors in the 15th (or so) decimal place can creep in as a result of
the fact that some numbers which are accurate to 2 decimal places expressed
in base 10 can result in recurring binary places when converted to binary
(being the base in which the mathematical operations are carried out).

HTH

--
Return email address is not as DEEP as it appears

"ljontheroad" wrote in message
...
How do I get Excel to correctly add a column of numbers that have each
been
rounded to 2 decimals? It seems to always remember the unrounded figures;
therefore, the sum of the column of figures is never correct. It's always
off
a few decimal points. This seems an amazing short-coming for a spreadsheet
program. Is there a fix for this problem?

ljontheroad wrote:
How do I get Excel to correctly add a column of numbers that have each been
rounded to 2 decimals? It seems to always remember the unrounded figures;
therefore, the sum of the column of figures is never correct. It's always off
a few decimal points. This seems an amazing short-coming for a spreadsheet
program. Is there a fix for this problem?

Amazingly enough, having used Excel extensively over the last ten years
or so for Management Accounting purposes, I've never noticed this! Must
be something to do with your workbook!

--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk