Rounding issues

  • Thread starter Thread starter DINO
  • Start date Start date
D

DINO

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

.
Click to expand...
 
-----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?
.
Click to expand...
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
 
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:A10>10)

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 Graesse
(e-mail address removed)
Boston M

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 tru

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:C

A D
A E
A F
B D
B E
B F
C D
C E
C F

And the formula

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

The resultant arrays produced are

(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


----- 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 :-

Din
 
Thanks for the help Mark. Here's the situation:
I work for large municipality. 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.
-----Original Message-----
Hi Dino,
The SUMPRODUCT function is often used when the SUMIF
Click to expand...
function comes up short. Say you only wanted to sum the
values greater then 10. You could use:
=SUMPRODUCT(ROUND(A1:A10,2)*(A1:A10>10))

I have included below a writeup on the SUMPRODUCT
Click to expand...
function. If you run into trouble, post some more details
and I'm sure we can help.
Good Luck,
Mark Graesser
(e-mail address removed)
Boston MA

SUMPRODUCT:

The SUMPRODUCT function will create an array of values
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
spreadsheet, must be numeric. Text arguments will return
a #VALUE error.
 
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.
Click to expand...

Don't use SUMIF. Use this formula:

=SUMPRODUCT((AgencyBilled=F2)*ROUND(AssessmentAmount,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
 
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.
Click to expand...

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


--ron
 
Back
Top