Rounding Issues

[DINO]

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. I also have to use the rounded values of the
assessments, not the carried out to four digits as it is
on the spreadsheet. 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.

Thanks in advance if anyone can help on this.

-----Original Message-----
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 Graesser
(e-mail address removed)
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.

 

[Bob Phillips]

Dino,

Mark showed you that when he showed the last SUMPRODUCT formula.

For instance, say you current SUMIF is

=SUMIF(A1:A100,">10")

which sums only those amounts greater than 10. This doesn't round as well,
but

=SUMPRODUCT(ROUND(A1:A10,2)*(A1:A10>10))

rounds the numbers to the cent, and then sums on ly those items greater than
10.

So SUMPRODUCT is your man, ditch SUMIF.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

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. I also have to use the rounded values of the
assessments, not the carried out to four digits as it is
on the spreadsheet. 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.

Thanks in advance if anyone can help on this.

-----Original Message-----
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 Graesser
(e-mail address removed)
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.

 

[JE McGimpsey]

You might want to include the ROUND() in the second argument too,
otherwise you'll risk having something like 10.0001, which, to 2 digits
is *not* greater than 10, but would be included by the formula.

Perhaps:

=SUMPRODUCT((ROUND(A1:A10,2)>10)*ROUND(A1:A10,2))

or, as I prefer

=SUMPRODUCT(--(ROUND(A1:A10,2)>10),ROUND(A1:A10,2))

 

[Bob Phillips]

True, somewhat negates testing for it if you then sum without it.

Bob

 

[Guest]

Thanks for the help! I will try the suggestions.

Dino