rounding values in in array before sum

[Guest]

Hi,

Anyone know how to round each cell before summing the data.

For example, I have data from A1:A10.
If I sum the data and then round eg.

round(sum(A1:A10),0)

This 'MAY' give me a different answer to rounding each of A1:A10 before the
sum.

How do I do the latter? Is it an array formula?

Rgds,

Bruce

 

[Roger Govier]

Hi Bruce

To do the latter is an array formula, which is committed or amended with
Ctrl+Shift+Enter when Excel includes the curly braces around the formula
{ }. Do not type them yourself.
{=SUM(ROUND(A1:A10,0))}

 

[Bob Phillips]

or non-array

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[daddylonglegs]

In general you'll get a higher level of accuracy rounding the final su
rather than each individual value within it.

Consider the situation where all cells A1:A10 contain 0.42

=SUM(A1:A10) =4.2
=ROUND(SUM(A1:A10),0)=4
=SUMPRODUCT(ROUND(A1:A10,0))=

 

[Guest]

Hi daddylonglegs,

Arithmatically you are correct.

In my problem I have data that is coverted from one unit to another and the
result goes into a system that only accepts whole numbers.
I want to use this formula to reconcile the output.

Rgds,