Rounding issues

I get lists of dollar amounts that I need to sum. The
actual numbers have four or five digits to the right of
the decimal, like $25.5637 I need to sum only to the
nearest penny, but I want to leave each individual number
as is. Is there a way I can indicate in the summation
formula that I want to use the rounded values for each
number?

One way:

=SUMPRODUCT(ROUND(A1:A10,1))

HTH
Jason
Atlanta, GA

-----Original Message-----
I get lists of dollar amounts that I need to sum. The
actual numbers have four or five digits to the right of
the decimal, like $25.5637 I need to sum only to the
nearest penny, but I want to leave each individual number
as is. Is there a way I can indicate in the summation
formula that I want to use the rounded values for each
number?
.

Sorry - you said penny, not dime. So use:

=SUMPRODUCT(ROUND(A1:A10,2))

instead.

Jason

-----Original Message-----
One way:

=SUMPRODUCT(ROUND(A1:A10,1))

HTH
Jason
Atlanta, GA

-----Original Message-----
I get lists of dollar amounts that I need to sum. The
actual numbers have four or five digits to the right of
the decimal, like $25.5637 I need to sum only to the
nearest penny, but I want to leave each individual
number
as is. Is there a way I can indicate in the summation
formula that I want to use the rounded values for each
number?
.

.

-----Original Message-----
I get lists of dollar amounts that I need to sum. The
actual numbers have four or five digits to the right of
the decimal, like $25.5637 I need to sum only to the
nearest penny, but I want to leave each individual number
as is. Is there a way I can indicate in the summation
formula that I want to use the rounded values for each
number?
.

Hi: in sum cell try =round(sum(cell addresses),2) then
format sum cell to 2 dec places

Thanks! It worked fine. Unfortunately, I also do a sum
elsewhere in the spreadsheet using the same cells in
a "sumif" formula. I may have to change the original
numbers after all :-(

Dino

-----Original Message-----
Sorry - you said penny, not dime. So use:

=SUMPRODUCT(ROUND(A1:A10,2))

instead.

Jason

-----Original Message-----
One way:

=SUMPRODUCT(ROUND(A1:A10,1))

HTH
Jason
Atlanta, GA

-----Original Message-----
I get lists of dollar amounts that I need to sum. The
actual numbers have four or five digits to the right of
the decimal, like $25.5637 I need to sum only to the
nearest penny, but I want to leave each individual
number
as is. Is there a way I can indicate in the summation
formula that I want to use the rounded values for each
number?
.

.

.

Hi Dino,
The SUMPRODUCT function is often used when the SUMIF function comes up short. Say you only wanted to sum the values greater then 10. You could use:

=SUMPRODUCT(ROUND(A1:A10,2)*(A1:A1010))

I have included below a writeup on the SUMPRODUCT function. If you run into trouble, post some more details and I'm sure we can help.

Good Luck,
Mark Graesser

Boston MA

SUMPRODUCT:

The SUMPRODUCT function will create an array of values for each argument. The corresponding components of each array are then multiplied, and these products are added.

The arrays must be of the same size, and in the same direction (vertical or horizontal). However, they do not have to be level. One can use an array of A1:A5 in one argument and an array of B11:B15 in another argument. Arrays of conflicting size will produce a #N/A error.

Also, the arrays must be of a definite size. Full column references (A:A) are not valid and will return a #NUM error.

The use of a single multiplier is also acceptable. SUMPRODUCT( (A1:A5) * 5 )

For conditional arguments the return is a 0 if false and a 1 if true

Non-conditional arguments, values used directly from the spreadsheet, must be numeric. Text arguments will return a #VALUE error.

SAMPLE:

With the following table in A1:C9

A D 1
A E 2
A F 3
B D 4
B E 5
B F 6
C D 7
C E 8
C F 9

And the formula:

=SUMPRODUCT( (A1:A9=�B�) * (B1:B9=�E�) * (C1:C9) )

The resultant arrays produced a

(0,0,0,1,1,1,0,0,0) * (0,1,0,0,1,0,0,1,0,) * (1,2,3,4,5,6,7,8,9)

The products of the corresponding components then produce:

(0*0*1) + (0*1*2) + (0*0*3) + (1*0*4) + (1*1*5) + … =
(0 + 0 + 0 + 0 + 5 + 0 + 0 + 0 + 0)

And the final sum of these products is 5


----- Dino wrote: -----

Thanks! It worked fine. Unfortunately, I also do a sum
elsewhere in the spreadsheet using the same cells in
a "sumif" formula. I may have to change the original
numbers after all :-(

Dino

-----Original Message-----
Sorry - you said penny, not dime. So use:
=SUMPRODUCT(ROUND(A1:A10,2))
instead.
Jason
-----Original Message-----
One way:
=SUMPRODUCT(ROUND(A1:A10,1))
HTH
Jason
Atlanta, GA
-----Original Message-----
I get lists of dollar amounts that I need to sum. The
actual numbers have four or five digits to the right of
the decimal, like $25.5637 I need to sum only to the
nearest penny, but I want to leave each individual
number
as is. Is there a way I can indicate in the summation
formula that I want to use the rounded values for each
number?
.
.
.

Thanks for the help Mark. Here's the situation:
I work for large municipality. The spreadsheet I'm working=20
on has three columns: account number, assessment amount,=20
and agency billed. I have to tally the total amount=20
charged to each agency. That's what the SUMIF formula is=20
for, I have cells that look for each occurrence of an=20
agency and totals the corresponding amounts. The=20
spreadsheet is sorted by account number and I can't alter=20
this. So although the SUMPRODUCT & ROUND functions worked=20
great to use the rounded values, I don't know how to use=20
them in my SUMIF formula, or what to replace that formula=20
with.

-----Original Message-----
Hi Dino,
The SUMPRODUCT function is often used when the SUMIF=20
function comes up short. Say you only wanted to sum the=20
values greater then 10. You could use:

=3DSUMPRODUCT(ROUND(A1:A10,2)*(A1:A1010))

I have included below a writeup on the SUMPRODUCT=20
function. If you run into trouble, post some more details=20
and I'm sure we can help.

Good Luck,
Mark Graesser

Boston MA

SUMPRODUCT:

The SUMPRODUCT function will create an array of values=20
for each argument. The corresponding components of each=20
array are then multiplied, and these products are added.

The arrays must be of the same size, and in the same=20
direction (vertical or horizontal). However, they do not=20
have to be level. One can use an array of A1:A5 in one=20
argument and an array of B11:B15 in another argument. =20
Arrays of conflicting size will produce a #N/A error.

Also, the arrays must be of a definite size. Full column=20
references (A:A) are not valid and will return a #NUM=20
error.

The use of a single multiplier is also acceptable. =20
SUMPRODUCT( (A1:A5) * 5 )

For conditional arguments the return is a 0 if false and=20
a 1 if true

Non-conditional arguments, values used directly from the=20
spreadsheet, must be numeric. Text arguments will return=20
a #VALUE error.

SAMPLE:

With the following table in A1:C9

A D 1
A E 2
A F 3
B D 4
B E 5
B F 6
C D 7
C E 8
C F 9

And the formula:

=3DSUMPRODUCT( (A1:A9=3D=E2?=9DB=E2?=9D) * (B1:B9=3D=E2?=9DE=E2?=9D) * =
(C1:C9) )

The resultant arrays produced a

(0,0,0,1,1,1,0,0,0) * (0,1,0,0,1,0,0,1,0,) *=20
(1,2,3,4,5,6,7,8,9)

The products of the corresponding components then produce:

(0*0*1) + (0*1*2) + (0*0*3) + (1*0*4) + (1*1*5) + =E2?=A6 =3D=20
(0 + 0 + 0 + 0 + 5 + 0 + 0 + 0 + 0)

And the final sum of these products is 5

=20
----- Dino wrote: -----
=20
Thanks! It worked fine. Unfortunately, I also do a=20
sum=20
elsewhere in the spreadsheet using the same cells in=20
a "sumif" formula. I may have to change the original=20
numbers after all :-(
=20
Dino
=20
-----Original Message-----
Sorry - you said penny, not dime. So use:
=3DSUMPRODUCT(ROUND(A1:A10,2))
instead.
Jason
-----Original Message-----
One way:
=3DSUMPRODUCT(ROUND(A1:A10,1))
HTH
Jason
Atlanta, GA
-----Original Message-----
I get lists of dollar amounts that I need to sum.=20
The=20
actual numbers have four or five digits to the=20
right of=20
the decimal, like $25.5637 I need to sum only to=20
the=20
nearest penny, but I want to leave each=20
individual=20
number=20
as is. Is there a way I can indicate in the=20
summation=20
formula that I want to use the rounded values for=20
each=20
number?
.
.
.

.

On Tue, 9 Mar 2004 14:06:47 -0800, "DINO"
wrote:

The spreadsheet I'm working
on has three columns: account number, assessment amount,
and agency billed. I have to tally the total amount
charged to each agency. That's what the SUMIF formula is
for, I have cells that look for each occurrence of an
agency and totals the corresponding amounts. The
spreadsheet is sorted by account number and I can't alter
this. So although the SUMPRODUCT & ROUND functions worked
great to use the rounded values, I don't know how to use
them in my SUMIF formula, or what to replace that formula
with.

Don't use SUMIF. Use this formula:

=SUMPRODUCT((AgencyBilled=F2)*ROUND(AssessmentAmou nt,2))

F2 contains the designation for the Agency Billed that you want the sum of
what's being billed for.

You understand that if you SUM rounded numbers (as opposed to ROUNDing SUM'd
numbers), you may introduce errors in the totals. There was a fellow early on
who hacked a bank computer and made big bucks skimming off the "fractions of
cents" that resulted from this rounding.

If the municipality is large enough, the errors might be significant.

--ron

Thanks Ron. Yes I know that there will be errors in the
totals, but in this particular case, it's OK. The
agencies are billed according to the rounded numbers
regardless what I have in the spreadsheet, it's just my
spreadsheet numbers that are off.

Dino


-----Original Message-----
On Tue, 9 Mar 2004 14:06:47 -0800, "DINO"

wrote:

The spreadsheet I'm working
on has three columns: account number, assessment
amount,
and agency billed. I have to tally the total amount
charged to each agency. That's what the SUMIF formula
is
for, I have cells that look for each occurrence of an
agency and totals the corresponding amounts. The
spreadsheet is sorted by account number and I can't
alter
this. So although the SUMPRODUCT & ROUND functions
worked
great to use the rounded values, I don't know how to
use
them in my SUMIF formula, or what to replace that
formula
with.

Don't use SUMIF. Use this formula:

=SUMPRODUCT((AgencyBilled=F2)*ROUND(AssessmentAmou nt,2))

F2 contains the designation for the Agency Billed that
you want the sum of
what's being billed for.

You understand that if you SUM rounded numbers (as
opposed to ROUNDing SUM'd
numbers), you may introduce errors in the totals. There
was a fellow early on
who hacked a bank computer and made big bucks skimming
off the "fractions of
cents" that resulted from this rounding.

If the municipality is large enough, the errors might be
significant.

--ron
.

On Tue, 9 Mar 2004 21:40:23 -0800, "DINO"
wrote:

Thanks Ron. Yes I know that there will be errors in the
totals, but in this particular case, it's OK. The
agencies are billed according to the rounded numbers
regardless what I have in the spreadsheet, it's just my
spreadsheet numbers that are off.

Hopefully, the formula I gave you will give you the correct answer. Let us
know.


--ron