how do i add rounded numbers?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
Try

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

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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.
 
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.
Click to expand...


=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 said:
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.
Click to expand...

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.
 
Back
Top