Results 1 to 8 of 8
  1. #1
    New Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculation (XP)

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

  6. #6
    2 Star Lounger
    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.

  7. #7
    Uranium Lounger
    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

  8. #8
    2 Star Lounger
    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.

Posting Permissions

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