Footing rounded numbers
 

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?

Footing rounded numbers
 

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


Replace @mailinator.com with @tiscali.co.uk


"G-man" wrote in message
...
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?

Footing rounded numbers
 

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





In article ,
G-man wrote:

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?

Footing rounded numbers
 

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

"JE McGimpsey" wrote:

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





In article ,
G-man wrote:

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?

Footing rounded numbers
 

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?



In article ,
G-man wrote:

The rounding "error" is $.03. Is there a formula that will spread the error
equally amoung all obervations (C1:C10)?