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