software

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,

BruceHi 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 Govierquot;Brucequot; gt; wrote in message
...
gt; Hi,
gt;
gt; Anyone know how to round each cell before summing the data.
gt;
gt; For example, I have data from A1:A10.
gt; If I sum the data and then round eg.
gt;
gt; round(sum(A1:A10),0)
gt;
gt; This 'MAY' give me a different answer to rounding each of A1:A10
gt; before the
gt; sum.
gt;
gt; How do I do the latter? Is it an array formula?
gt;
gt; Rgds,
gt;
gt; Bruce
gt;
or non-array

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Roger Govierquot; gt; wrote in message
...
gt; Hi Bruce
gt;
gt; To do the latter is an array formula, which is committed or amended with
gt; Ctrl Shift Enter when Excel includes the curly braces around the formula
gt; { }. Do not type them yourself.
gt; {=SUM(ROUND(A1:A10,0))}
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; quot;Brucequot; gt; wrote in message
gt; ...
gt; gt; Hi,
gt; gt;
gt; gt; Anyone know how to round each cell before summing the data.
gt; gt;
gt; gt; For example, I have data from A1:A10.
gt; gt; If I sum the data and then round eg.
gt; gt;
gt; gt; round(sum(A1:A10),0)
gt; gt;
gt; gt; This 'MAY' give me a different answer to rounding each of A1:A10
gt; gt; before the
gt; gt; sum.
gt; gt;
gt; gt; How do I do the latter? Is it an array formula?
gt; gt;
gt; gt; Rgds,
gt; gt;
gt; gt; Bruce
gt; gt;
gt;
gt;

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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=506687Hi 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,

quot;daddylonglegsquot; wrote:

gt;
gt; In general you'll get a higher level of accuracy rounding the final sum
gt; rather than each individual value within it.
gt;
gt; Consider the situation where all cells A1:A10 contain 0.42
gt;
gt; =SUM(A1:A10) =4.2
gt; =ROUND(SUM(A1:A10),0)=4
gt; =SUMPRODUCT(ROUND(A1:A10,0))=0
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=506687
gt;
gt;