rounding

[pfdubz]

I have an interesting conundrum with the Excel round function.
I have three numbers - Hourly Rate (HR), Hours Worked (HW) and Burdened
Labor Rate (BLR). HR =56.50, Hw = 95, and BLR = 1.51.
When I calculate ROUND(HR*HW,2) which I will label "A" and then multiply
that by the BLR using ROUND( A*BLR,2) I get 8104.93. However, when I
calculate ROUND(HR*BLR,2) which I will label "B" and then multiply that by
the HW using ROUND(B*HW,2), I get 8105.40.
Why is that and how do I get equal numbers using the two methods.
Unfortunatley, I have use the two different methods in two different places
of a report.

 

[Sheeloo]

Suppose you add 1.25 two time and then round. What do you get? 3 right?
Now round both first and then add. 2 right?

How you round depends on your application... typically one performs all
maths and then use round...

in general
round(1*round(2*3) is not equal to round(round(1*2)*3) as you have discovered.

 

[Jim Thomlinson]

When you use the round function you change the number. Here is a simple
example rounding to the nearest 10

=5*10 answer is 50
=round(5, -1) * round(10, -1) = 100
when I round I am basically rounding 5 up to 10 so I get 10 * 10 = 100

To get around this you wo not want to change the number only adjust it's
formatting. don't do any of the rounding and just format the numbers to 2
decimal places. The only draw back to this is that sometimes thing do not
appear to add up as in this case.

123.5 + 123.5 = 247
but if you have formatted the number to have no decimals it will appear this
way
124 + 124 = 247

 

[JoeU2004]

Do the math! Rounding is not a commutative operation.

When I calculate ROUND(HR*HW,2) which I will label "A"
and then multiply
that by the BLR using ROUND( A*BLR,2) I get 8104.93

The second ROUND is multiplying 5367.50 times 1.51.

when I calculate ROUND(HR*BLR,2) which I will label "B"
and then multiply that by
the HW using ROUND(B*HW,2), I get 8105.40.

The second ROUND is multiplying 85.32 times 95.

The difference is large because in the first case, any rounding error is
small relative to the unrounded result (in fact, there is no error!),
whereas in the second case, the rounding error is larger relative to the
unround result.


----- original message -----

 

[JoeU2004]

PS....

When I calculate ROUND(HR*HW,2) which I will label "A" and then multiply
that by the BLR using ROUND( A*BLR,2) I get 8104.93. However, when I
calculate ROUND(HR*BLR,2) which I will label "B" and then multiply that by
the HW using ROUND(B*HW,2), I get 8105.40.
[....]
Unfortunatley, I have use the two different methods in two different
places of a report.

If you are asking "which is right?", you should do whichever rounding
matches intermediate numbers in your report, if you show only "A" or "B",
but not both.

If you do not show "A" and "B" anywhere in the report, you should compute

=round(HR*HW*BLR,2)

On the other hand, if you show both "A" and "B" in the report, I would use
the above formula in both places to compute the final charge, and I would
add a footnoote to explain that rounding can affect the calculation. That
is a common accounting practice.

However, if the practices of your industry require that you perform the
computations exactly as you have done, you need to go to accounting experts
familiar with your industry to resolve the conflict. I seriously doubt that
this step is necessary.


----- original message -----