Footing rounded numbers

  • Thread starter Thread starter G-man
  • Start date Start date
G

G-man

I am using the round function round(A$1*B1, 2) for B1 to B10.
sum(B1:B10)*A1 does not equal the sum of round(A$1*B1to 10, 2)
It is off about $0.03. Is there a function that will round up or down to get
the correct total?
 
Try:

=SUM(ROUND(B1:B10,2))*A1

Which is an array formula and must be entered with Ctrl + Shift + Enter not
just Enter.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
First, which is correct?

If you have, say:

C1: =ROUND(A$1*B1,2)
C2: =ROUND(A$1*B2,2)
....

then

C11: =SUM(C1:C10)

will give you the "correct" sum of the results in C1:C10.

If you're looking for something else, you'll need to specify how XL
should determine what's correct. See

http://www.mcgimpsey.com/excel/pennyoff.html
 
The "correct" answer would be left side
sum(B1:B10)*A1 <> sum(round(B1 to B10*A$1,2))

for example
C1: =ROUND(A$1*B1,2)
C2: =ROUND(A$1*B2,2)
..
..
Sum (C1:C10) = sum(round(B1 to B10*A$1,2))

The rounding "error" is $.03. Is there a formula that will spread the error
equally amoung all obervations (C1:C10)?
 
How do you propose spreading $0.03 evenly among 10 cells (other than by
not rounding)? Which cells should get the excess? Should the spread be
based on position? or should it be weighted?
 
Back
Top