Summing Formula Results

[Wilbur]

I am sure there is a simple commonly known solution but..
If the result of a formula is 6 decimal places rounded to
2 in accounting format what has to happen to Sum the
rounded result (printed number) rather than the formula
result that is then rounded to something other than the
total of the printed numbers?

 

[Peo Sjoblom]

What you have to understand is that formatting with 2 decimal places is not
the same as
round to 2 decimal places, if you round 1.123456 to 2 decimal places
=ROUND(1.123456.2)
the result is 1.12 but if you format it with 2 decimal places it only
changes the display while the
underlying value is still 1,123456. You can either use rounding in a help
column or
do tools>options>calculation and check precision as displayed. There are
drawbacks with that though.

See:

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

for a rather pedagogical explanation

 

[Bob Phillips]

Notwithstanding what Peo said, you can simply sum rounded values with this
formula

=SUM(ROUND(I1:I5,2))

It is an array formula, so you need to enter with Ctrl-Shift-Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)