Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Aug 2005
    Location
    Novi Sad, Serbia and Montenegro (Yugoslavia)
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    problems with decimals-help urgently

    Hello!

    In attached workbook, in sheet named Analiza-dio1, there are several tables. Several numbers are rounded to four decimal places. They have to be rounded to two decimal places, but there is problem. If you round these numbers to two decimal places and then calculate it on desk calculator, the total sum in column Indirektni porezi-total is 100,01%. This mustn't be case.

    Question is: how to round them to two decimal places with total sum(calculated on desk calculator) 100%? I mustn't delete contents of cells and type values myself.


    Any help would be greatly appreciated.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Slaven,

    There is a thing you MUST remember about Excel, that being formatting a number to 2 decimal only affects how the number is displayed NOT the value of the number used in calculations!

    You MUST use the Round() function to affect how the number is used in calculations. I took your example and added a column which rounds the number to the right to 2 decimal places. Then Summed the numbers using the Sum() function. I then added the numbers in the new column using the Calculator and you'll notice that the numbers match.

    Slaven_Rounding.JPG

    Note: there is no way to accomplish your task if you can't make changes. You must be able to add the column to do the rounding. HTH
    Last edited by RetiredGeek; 2015-02-28 at 13:37.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 986 Times in 916 Posts
    Rounding in Excel has long been a problem. If you have two numbers, 1.2455 and 1.2545, adding them gives 2.5. If you round them to 2 decimal places you get 1.25 and 1.26, obviously not the same. You need to keep the full precision of the numbers, no rounding, but display them as 2 decimal places using formatting.

    cheers, Paul

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Paul,

    What version of Excel are you using?
    Things seem to work fine in 2010
    Round2.JPG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Star Lounger
    Join Date
    Aug 2005
    Location
    Novi Sad, Serbia and Montenegro (Yugoslavia)
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RetiredGeek, Paul T, I am talking about sheet Analiza-dio1(this is second sheet).

    In this sheet, there are several cells with numbers rounded to 4 decimals.

    Try to round it to two decimals and sum these numbers. The total sum is 100,01 percent.

    Are you willing to edit file that I attached, keeping original formulas, and do the procedure you typed and post edited file again?

    Thank you so much.

    Reason why I ask you to do this for me is because I am in a big hurry.
    Last edited by slaven_savic; 2015-02-28 at 14:17.

  6. #6
    Star Lounger
    Join Date
    Aug 2005
    Location
    Novi Sad, Serbia and Montenegro (Yugoslavia)
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    63,01 (formula:ROUND(63,0059;2)) Reference: A1
    24,30 (formula:ROUND(24,2991;2)) Reference: A2
    12,70 (formula:ROUND(12,6951;2)) Reference: A3
    100,01 (formula: SUM(A1:A3)) Reference: A4

    This mustn't be case!

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Staven

    As Paul and RG have indicated, if you format B5:D5 to show 2 decimal places, the total remains 100.00 in cell F5. Cells G5:I5 have the rounding function applied and result in 100.01 proving that formatting the cell to 2 decimals using formatting (not the Round function) will not change your result

    rounded1.png

    HTH,
    Maud
    Last edited by Maudibe; 2015-02-28 at 15:16.

  8. #8
    Star Lounger
    Join Date
    Aug 2005
    Location
    Novi Sad, Serbia and Montenegro (Yugoslavia)
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    On desk calculator sum of these 3 numbers is 100,01.

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    perhaps it is the method that your desk desk calculator rounds at each input such as columns G thru I in my example

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Slaven,

    The problem here is you can't expect to round numbers to a lesser precision (2 vs 4 decimal places) and then get the same result in a final answer. Intermediate rounding (e.g. each value that makes up a total) will almost always cause this type of error.
    Round2.JPG
    You can see in the attached graphic the amount of ROUNDING INCREASE for each number (they all rounded up) which is equal to the .01 error. This is not a problem with Excel but a misunderstanding of how rounding affects numbers. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #11
    Star Lounger
    Join Date
    Aug 2005
    Location
    Novi Sad, Serbia and Montenegro (Yugoslavia)
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Now final question.

    But, firstly, one note: only me have working tables. But, final data will be public. The data from tables will be copied to Word document

    What you("you" means all people except me, that commented problem) suggest: to accept Excel's solution of rounding and put a note below table, or to change values myself after it's copying to Word?

    Thanks to everyone.

  12. #12
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 986 Times in 916 Posts
    It will be a lot easier to put a disclaimer in advising of possible rounding errors.

    cheers, Paul

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Slaven

    ..using your file as example, if you require the total% of the three values in columns B, C and D to be exactly 100.00%, then perhaps the best 'solution' is to force the result to be exactly 100.00%.
    So, in column C use a formula, as per the example file attached.
    For your info, I copied the original values for column C to column I to allow you to compare the results.

    The only other option I can think of is to use Excel2003 Tools>Options..>Calculation>Precision as displayed

    zeddy

Posting Permissions

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