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

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

--
Regards

Roger Govier


"Bruce" wrote in message
...
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

or non-array

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Roger Govier" wrote in message
...
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))}

--
Regards

Roger Govier


"Bruce" wrote in message
...
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

In general you'll get a higher level of accuracy rounding the final sum
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))=0


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=506687

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,

"daddylonglegs" wrote:


In general you'll get a higher level of accuracy rounding the final sum
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))=0


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=506687