how do i add rounded numbers?

[Guest]

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.

 

[Bob Phillips]

Try

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

--

HTH

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

 

[RagDyeR]

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!
=====================================================


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.

 

[Ron Rosenfeld]

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

 

[Guest]

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.