Summing Formula Results

  • Thread starter Thread starter Wilbur
  • Start date Start date
W

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?
 
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
 
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)
 
Back
Top