+ Reply to Thread
Results 1 to 10 of 10

When ROUND doesnt round

  1. #1
    Registered User
    Join Date
    12-18-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Question When ROUND doesnt round

    I got number -3569.6322124015200000 in A1
    I got number 4157.93943509912000000 in A2

    Both cells have been formated as number 16 dp to allow me to see whats going on

    If I use Round in both cells, such as
    In cell B1 =ROUND(A1,2) / Now displays -3569.6300000000000000
    in cell B2 =ROUND(A2,2) / Now displays 4157.9400000000000000

    Now I add both cells together
    In cell B3 =B1+B2 / displays 588.3099999999999999 !!!!!!

    So how is that possible if indeed the rounding is occuring to 2 dps in cells
    B3 should display 588.3100000000000000 because both B1 and B2 were rounded to 2dps

    Thats means when I do the comparison operation (B1+B2=588.31) it returns FALSE!!!!

    B1+B2 should return 588.310000000000000 not 588.3099999999999999

    does anyone know whats going on here??
    Last edited by Buckwa; 12-18-2011 at 07:00 AM. Reason: The numbers were wrong

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: When ROUND doesnt round

    Base 10 decimals cannot, in general, be represented exactly in base 2 decimals. Round the result.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: When ROUND doesnt round

    Hi Buckwa,

    You need to format the results as number with two dp and also I believe answer can never be 588.32 as after addition sum is to 588.31 only, see the attachment for more.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    Attached Files Attached Files
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  4. #4
    Registered User
    Join Date
    12-18-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: When ROUND doesnt round

    Quote Originally Posted by dilipandey View Post
    Hi Buckwa,

    You need to format the results as number with two dp and also I believe answer can never be 588.32 as after addition sum is to 588.31 only, see the attachment for more.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    Yes you're absolutely right I stuffed up the numbers in the question...it should add up to 588.31...I've edited my post

  5. #5
    Registered User
    Join Date
    12-18-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: When ROUND doesnt round

    Quote Originally Posted by dilipandey View Post
    Hi Buckwa,

    You need to format the results as number with two dp and also I believe answer can never be 588.32 as after addition sum is to 588.31 only, see the attachment for more.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    Yes I see where you have the result in B3 with 588.31 which is corrent at first glance.
    If I right click B3 > Format Cell > Number dps to 10 I get 588.3100000000 good thats correct, but if I go to 12 dp it suddenly changes to 588.309999999999 (you can see it in the preview when you're clicking the dp button)

    The whole point of this is if I compare this cell to the number "588.31" I get FALSE even thought the number in the cell is 588.31 to 2 dp, because really its 588.3099999999990000
    thats my point to this question.
    I appreciate you helping me with this thankyou.

    When I do the operation =IF(B3=588.31,"TRUE","FALSE") i get false...it should be true...I dont know why I should have to format the result when I'm adding 2 rounded numbers together, the result should be a rounded number to the same precision...I learnt this in primary school.
    Last edited by Buckwa; 12-18-2011 at 07:23 AM.

  6. #6
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: When ROUND doesnt round

    Don't quote whole posts -- it's just clutter.

    If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

  7. #7
    Registered User
    Join Date
    12-18-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: When ROUND doesnt round

    Quote Originally Posted by dilipandey View Post
    Hi Buckwa,

    You need to format the results as number with two dp and also I believe answer can never be 588.32 as after addition sum is to 588.31 only, see the attachment for more.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    Hi DILIPandey,

    I added something to your spreedsheet
    Look in cells B18 and B19...why is the result different?
    Attachment 133133

  8. #8
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: When ROUND doesnt round

    Hi Buckwa,



    I know it is little confusing but I believe Round just changes the display only but the value remains same and then Excel behaves accordingly. This holds true if you format the result as well with number two dp.

    Regards,
    DILIPandey


    <click on below 'star' if this helps>

    <

  9. #9
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: When ROUND doesnt round

    just accept it and as shg says round the result this has been covered many times on this forum but as the search isn't working you cant look it up
    =ROUND(B1+B2,2)
    if you really need to know read this
    http://support.microsoft.com/kb/78113
    or try using open office calc which calculates precision differently
    it gives the result you expect
    Last edited by martindwilson; 12-18-2011 at 08:05 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  10. #10
    Registered User
    Join Date
    12-18-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: When ROUND doesnt round

    Ok thankyou.

    And yes I did search for this and found alot of people talking about rounding but not answering my question, so how long do you search for the answer of your question before deciding to start a new thread....5 mins? well I searched for 5 mins and found nothing relevant so I started a new thread.

    I just found this in the last few minutes, if you go tools >> options > calculation tab > and check "precision as displayed" which also happens to be mentioned in that http://support.microsoft.com/kb/78113 from martindwilson

    Now in the previous spreadsheet I posted in cells B18 and B19, before I checked this box I got SAME and DIFF after checking I got DIFF and SAME respectively....WTF!!!
    I would expect after ticking that box if you see 588.31 you get 588.31 but that cant be the case either because you would have got SAME and SAME.

    This is a case where when I say round to 2 dps I want to round to 2 dps....I'm the user....Mr Excel is the develpper, its Mr Excel's job as the developer to make sure these things are intuitive for things like this...in my opinion, you cant say its the limitation of floating point numbers blah blah blah as that article goes on about...I can type the number into the cell and it works I get excel to calculate and it doesnt, yes one way around it is to round the result as martindwilson and shg said, but there may be times when you dont want to do this..and you might want to just follow the normal laws of mathematics!! haha...that is add two numbers of a certain precision you get a number with the same precision...its not rocket science.

    anyway thanks
    Last edited by Buckwa; 12-18-2011 at 08:34 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1