how do i add rounded numbers?

How do I get Excel to add the rounded numbers in the cells, formated to 2
decimal places, and get the same answer that I would get if I added them on
the calculator. Excel seems to add the unrounded numbers giving an
incorrect answer, off by 1 or 2.

Try

=SUMPRODUCT(ROUND(A1:A20,2))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"echase" <[email protected]> wrote in message
news:[email protected]...
> How do I get Excel to add the rounded numbers in the cells, formated to 2
> decimal places, and get the same answer that I would get if I added them
on
> the calculator. Excel seems to add the unrounded numbers giving an
> incorrect answer, off by 1 or 2.

I suspect that you *mean* you *formatted* your numbers, even though you
mention rounding.

Say your Sum formula is totaling A1 to A10,
Where the values in Column A are coming from formulas, such as:

A1
=B1*C1

A2
=B2*C2

A3
=B3*C3
etc...

To *round* this column of formulas, revise them to something like this:

=ROUND(B1*C1,2)
=ROUND(B2*C2,2)
=ROUND(B3*C3,3)

Now, this might return values with 1, or 2, or NO decimal places, depending
on the actual results of the calculation.

Here's where the *formatting* comes in.

Format to 2 decimal places, and the returns will always display 2 places,
with zeroes filling in the non-significant values.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


"echase" <[email protected]> wrote in message
news:[email protected]...
How do I get Excel to add the rounded numbers in the cells, formated to 2
decimal places, and get the same answer that I would get if I added them on
the calculator. Excel seems to add the unrounded numbers giving an
incorrect answer, off by 1 or 2.

On Sat, 12 Nov 2005 09:29:02 -0800, echase <[email protected]>
wrote:

>How do I get Excel to add the rounded numbers in the cells, formated to 2
>decimal places, and get the same answer that I would get if I added them on
>the calculator. Excel seems to add the unrounded numbers giving an
>incorrect answer, off by 1 or 2.


=SUM(ROUND(rng,2))

entered as an *array* formula (hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula).


--ron

"echase" wrote:
> How do I get Excel to add the rounded numbers in the cells,
> formated to 2 decimal places, and get the same answer that
> I would get if I added them on the calculator. Excel seems
> to add the unrounded numbers giving an incorrect answer,
> off by 1 or 2.

It sounds like you are relying on Excel formatting to do the
rounding. There are two simple solutions:

1. Explicitly use the ROUND() function in your formulas, rather
than depend on formatting to do the rounding. For example,
=ROUND(A1/A2, 2). Or ....

2. Set the option Tools > Options > Calculation > Precision as
displayed. The downside of this option is that it affects the
calculations of all cells in the spreadsheet. That may or may
not be what you want.