Rounding & Totalling Error

[prkhan56]

I am using Windows XP/Office 2003 and have the following problem

Tools-Option-Calculation = Precision as displayed (ticked)
A B C
WT RATE AMOUNT
1 24.975 2125 53,071.88 (=A1*B1)
2 24.979 2125 53,080.38 (=A2*B2)
3 49.954 2125 106,152.25 (=Round(A3*B3,2))
106,152.25 (=Sum(C1:C2)

As can be seen the total should be 106,125.26 and not .25 as displayed.
I have tried using the ROUND formula with 2 decimal places and normal
SUM Formula but did not achieve the desired result

Can anybody point me in the right direction to achieve this?

TIA

Rashid Khan

 

[mother earth]

Hi,

The end result must be 106,152.25 --> because:
24.975 * 2125 = 53,071.875 (and not 53,071.88 = already rounded)
24.979 * 2125 = 53,080.375 (and not 53,080.38 = already rounded)
So in conclusion: 53,071.875 + 53,080.375 = 106,152.25 is the correct
result

If you let the cels in column C display the full result --> it will
make sense

Basic: only end results may be rounded!

 

[prkhan56]

Thanks for your prompt response. But I cannot do as suggested as it is
Amount (which has to be displayed in 2 decimal places)

Any better ideas

 

[Tushar Mehta]

XL has the ability to calculate at the displayed precision. Check out
Tools | Options... | Calculation tab | Workbook options section |
'Precision as displayed' checkbox.

You may also want to check out the XL help on the subject *before*
enabling this option.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

Thanks for your prompt response. But I cannot do as suggested as it is
Amount (which has to be displayed in 2 decimal places)

Any better ideas

 

[joeu2004]

Tools-Option-Calculation = Precision as displayed (ticked)
Noted.

A B C
WT RATE AMOUNT
1 24.975 2125 53,071.88 (=A1*B1)
2 24.979 2125 53,080.38 (=A2*B2)
3 49.954 2125 106,152.25 (=Round(A3*B3,2))
106,152.25 (=Sum(C1:C2)
As can be seen the total should be 106,125.26 and not .25 as displayed.
I have tried using the ROUND formula with 2 decimal places and normal
SUM Formula but did not achieve the desired result
Can anybody point me in the right direction to achieve this?

Be sure that the cells are formatted with the precision that you want.
When I format C1:C2 as Number with 2 decimal places, SUM(C1:C2) results
in ***.26. However, A3*B3 still results in ***.25 because A3 has 3
decimal places and 49.954*2125 is indeed 106,152.25. If you want C3 to
be the total of displayed values in C1:C2, you should use SUM().

 

[prkhan56]

Thanks to all who have helped me...

Rashid Khan

XL has the ability to calculate at the displayed precision. Check out
Tools | Options... | Calculation tab | Workbook options section |
'Precision as displayed' checkbox.

You may also want to check out the XL help on the subject *before*
enabling this option.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005