Results 1 to 8 of 8
Thread: Calculation (XP)

20041201, 08:23 #1
 Join Date
 May 2003
 Location
 South Australia, Singapore
 Posts
 22
 Thanks
 0
 Thanked 0 Times in 0 Posts
Calculation (XP)
The figures key in the respective cells are to the nearest 2 decimal places but I've display the figures to the nearest dollar.
For example the figure in Cell A1 is 233.32 and A2 is 593.49. When I adds up the A1 and A2, the amount should be 826.81.
However if the displayed figures are to the nearest dollar, figure in Cell A1 is 233 and A2 is 593. Thus total is 826.
My question is how can I make sure that the total amount adds up to 826 (based on the display figures) even though the amout entered in Cell A1 & A2 are to the nearest 2 decimal places.
Regards
Min

20041201, 08:31 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Calculation (XP)
Use =ROUND(A1,0)+ROUND(A2,0) as formula.
You could also enter =ROUND(A1,0) in B1 and =ROUND(A2,0) in B2, then use =B1+B2 to calculate the sum. You can hide column B, if you like.
Excel also has an option "Precision as displayed" (in the Calculation tab of Tools  Options...), but setting this means that ALL values will be stored the way they are displayed, so the values in A1 and A2 will lose their decimals too.

20041201, 08:33 #3
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculation (XP)
Use:
=ROUND(A1,0)+ROUND(B1,0)
Excel always uses the actual data in the cells, not the displayed data.
You can force Excel to use displayed data instead (Tools, options, calculation, precision as displayed), but that will apply to an entire workbook, not just to some cells. I don't recommend that option, since changing number formatting of cells then affects calculation results.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20041201, 08:33 #4
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculation (XP)
Brilliant minds.....
Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20041201, 08:35 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Calculation (XP)
<img src=/S/grin.gif border=0 alt=grin width=15 height=15>

20041201, 14:29 #6
 Join Date
 Sep 2003
 Location
 Louisville, Kentucky, USA
 Posts
 134
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculation (XP)
While Hans and Jan are right as always in their Excel advice, I question whether the original poster is really doing the right thing. I deal with these sorts of numbers issues all the time and we just let Excel do the calculation and display it without going through the contortions that Hans and Jan suggested. For example, if I want to add 1.5 and 2.5, adding them up gives me 4. If you add them up as displayed, you get 5. To me, 4 is the right answer.

20041201, 19:04 #7
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculation (XP)
So, to you 2+3=4 is correct? That is what the user would see doing it your way.
Legare Coleman

20041202, 20:29 #8
 Join Date
 Sep 2003
 Location
 Louisville, Kentucky, USA
 Posts
 134
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculation (XP)
My example was purposely extreme, but yes, 4 is my answer. More typically, we add 20 dollar amounts but every displayed number is rounded to the nearest thousand or million dollars. The total does not always add up to the sum of the displayed numbers, but it's more important that the total be correct. If anyone questions it, we just say it's rounding and everyone accepts that. The alternative is to display the decimal points, but that leads to a more cluttered presentation which is usually deemed to be a worse solution.