Rounding

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bud - 24 Nov 2005 04:27 GMT

I would really like some help,

round(3348.86*.1,2) = 334.89
round(3348.86*.03,2) = 100.47
when added together 334.89+100.47 = 435.36
yet
round(3348.86*.13,2) = 435.35

I beleive it is because 100.4658 is rounding to 100.47, when it should be
100.46.

I really need these numbers to add up. If this is possible it would be
great. Thank you

Reply to this Message

Biff - 24 Nov 2005 05:02 GMT

Hi!

Actually, there is not a problem and Excel is calculating these properly.

The difference is the result of the ROUND function.

Not rounded: (to 15 decimal places)

3348.86*0.1=334.886000000000000
3348.86*0.03=100.465800000000000

Added together = 435.351800000000000

Biff

>I would really like some help,
>
[quoted text clipped - 9 lines]
> I really need these numbers to add up. If this is possible it would be
> great. Thank you

Reply to this Message

stuarthow - 24 Nov 2005 14:03 GMT

Hi Bud

I agree with Biff; Excel is correct in rounding 100.4658 up to 100.47
if using ROUND. If you specifically want to round down to 100.46 use
ROUNDDOWN instead of ROUND.

Stu

Signature

stuarthow

Reply to this Message

joeu2004@hotmail.com - 25 Nov 2005 02:33 GMT

> round(3348.86*.1,2) = 334.89
> round(3348.86*.03,2) = 100.47
> when added together 334.89+100.47 = 435.36
> yet round(3348.86*.13,2) = 435.35

This is a normal anomaly of rounding. It is one reason
why the IRS, for example, suggests rounding only the
result of a computation, not intermediate results.

You do not need Excel to understand what is happening.
Consider adding 1.4 + 2.4, round to an integer manually.
If you round 1.4 and 2.4 first, your sum is 1+2 = 3. If you
round only the result, your sum is 1.4 + 2.4 = 3.8, which
rounds to 4.

> I beleive it is because 100.4658 is rounding to 100.47

Yes. Alternatively it is because 334.886 is rounded to
334.89.

> when it should be 100.46.

Why do you believe that? No matter: your expectation is
simply incorrect.

> I really need these numbers to add up. If this is possible
> it would be great.

There is no solution where, in all cases, f(A+B) = f(A)+f(B),
where "f" is any of the operations like INT, CEILING, ROUND,
ROUNDUP or ROUNDDOWN. You need to accept that as
a mathematical fact.

To determine the right solution for you, you need to decide
what your goals are. If you want an accurate final result,
it might be best to round only the final computation. If you
want the Excel computation to match what you would do
manually based on intermediate values displayed in cells,
you might want to round each intermediate result, accepting
a small error in the final computation. (There is also an
option to force this behavior for all spreadsheet compuations.
See Tools > Options > Calculation > Precision As Displayed.)

Moreover, you might use ROUNDUP or ROUNDDOWN to
maximize or minimize the error in the final computation in a
particular direction. The choice is a tricky judgment call that
requires a detailed understanding of the purpose of your
computations.

Reply to this Message