1. ## 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

2. ## 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.

3. ## 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.

4. ## Re: Calculation (XP)

Brilliant minds.....

5. ## Re: Calculation (XP)

<img src=/S/grin.gif border=0 alt=grin width=15 height=15>

6. ## 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.

7. ## Re: Calculation (XP)

So, to you 2+3=4 is correct? That is what the user would see doing it your way.

8. ## 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•