1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2003 Round to nearest ten

Discussion in 'Business Applications' started by holymoly, Mar 27, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    for example a number like 253,372

    returns 253,370

    or a number 68,538

    returns 68,540


    I've tried
    =Roundup(number,-1)
    but the first number 253,372
    returens
    253,380 and not 253,370
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    =round(a1/10,0)*10
     
  3. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    I found =MROUND(A1,10) you may have to do a Tools->Cells->Add-in-Analysis Tools VBA install

    So if A1 is 22 you get 20

    or if A1 is 28 you get 30

    =========================================

    Bomb#21 also works

    only problem with both is 25 or 35 returns 20 and 30

    and not 30 and 40
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Hmm. My Analysis Toolpak seems to have gone AWOL. :mad:

    So, that's solved then?
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Mine doesn't for me. Check your typing. :)
     
  6. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    Well an interesting observation. some numbers ending in 5 go up to nearest 10 and some down.

    ie. 25 can returns 30

    but 35 returns 30

    bizarre
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    For me, 25 > 30; 35 > 40.
     
  8. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    Ok try this number

    86,345

    using yours I get 86,340

    but 311,245

    i get 311,250 :eek::confused:
     
  9. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    I get 86350.

    Upload your file?
     
  10. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    I think I know why.

    My A1 number is a formula(calculation). If I change A1 to raw data or 86,345 i get 86,350

    But then with a formula calculation of 311,245 as A1 I do get 311,250 and not 311,240
     
  11. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
  12. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Your raw data is probably 86,344.9 or lower, but only showing anything after the decimal place, so rounding down is actually correct.
     
  13. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    check here
     

    Attached Files:

  14. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635

    yes that's it
     
  15. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Yep:
    C1 = 86,344.9
    C5 = 311,245.4
    Hence the error.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/697688

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice