Excel formula is calculating wrong total number on some of cells

[Alan]

Hi!

I having 1 problem that Excel formula is calculating wrong total number on
some of cells. For example:

1)M36=2.83
2)O36=ROUND(M36*1.075,2)
3)Q36=O36*110%
4)L36=1434
5)R36=L36*Q36
The R36 total is 4795.30 (wrong)

When I used calculator to calcalate it, the correct total is 4789.56
(Formula is 3.34*1434=4789.56).

May I know how to solve this problem? Please give advise. Thank you!

Regards,
Alan

 

[David Biddulph]

You need to recheck what you did with your calculator, as you got it wrong.
Q36 is 3.344, not 3.344
Please check your own working before you suggest that Excel has got things
wrong.

 

[Ron Rosenfeld]

Q36 is 3.344, not 3.344

????
--ron

 

[Ron Rosenfeld]

Hi!

I having 1 problem that Excel formula is calculating wrong total number on
some of cells. For example:

1)M36=2.83
2)O36=ROUND(M36*1.075,2)
3)Q36=O36*110%
4)L36=1434
5)R36=L36*Q36
The R36 total is 4795.30 (wrong)

When I used calculator to calcalate it, the correct total is 4789.56
(Formula is 3.34*1434=4789.56).

May I know how to solve this problem? Please give advise. Thank you!

Regards,
Alan

Your calculator is rounding the step where you compute O36 * 110%. The correct
answer to that is 3.344, but your calculator is dropping the last .004.

If you want the "correct" answer, you will have to figure out why your
calculator does not use more than two decimal digits.

If you want Excel to agree with your calculator, you will have to either
truncate or round your Q36 formula in the same way as your calculator is
altering the results.
--ron

 

[joeu2004]

1)M36=2.83
2)O36=ROUND(M36*1.075,2)
3)Q36=O36*110%
4)L36=1434
5)R36=L36*Q36
The R36 total is 4795.30 (wrong)
When I used calculator to calcalate it, the correct
total is 4789.56 (Formula is 3.34*1434=4789.56).

The Excel result in R36 is correct (approximately) for the procedure
that you describe.

I suspect you are fooling yourself because some cells (notably Q36 and
R36) are formatted with 2 decimal places.

As you describe it, Q36 is actually about 3.344. That might
__display__ as 3.34 due to cell formatting. But the __actual__
underlying value is still about 3.344.

Consequently, the formula in R36 is effectively 3.344*1434, not
3.34*1434. The extra 0.004*1434 is sufficient to explain the
difference that you see.

Moreover, it should be noted that R36 is really about 4795.296, not
4795.30 -- again, because of the difference between the displayed
value due to cell format and actual underlying value. This might
propagate "error" (misunderstanding) throughout your worksheet.

At a minimum, apparently you want the formula in Q36 to be =ROUND
(O36*110%,2). It would be prudent to also change the formula in R36
to =ROUND(L36*Q36,2).