+ Reply to Thread
Results 1 to 9 of 9

Non-standard rounding

  1. #1
    Registered User
    Join Date
    02-05-2020
    Location
    Iowa
    MS-Off Ver
    365 ver 1912
    Posts
    7

    Non-standard rounding

    I would like to round for example 430 to return 400 and 431 to return 500. Not sure that is possible??

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Non-standard rounding

    First off, welcome to the forum.
    Not sure what MS office version Iowa is, please update your MS version as the answers given can depend on the version you have.
    Also, you might be able to do that but it would be important to know what the parameters are that would indicate which direction that rounding should go, for example 431 is much closer to 400 than it is to 500. To develop a formula it would be good to know when to go up vs down.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Non-standard rounding

    Try it this way:

    =ROUND(A1+19,-2)

    assuming your number is in A1.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    02-05-2020
    Location
    Iowa
    MS-Off Ver
    365 ver 1912
    Posts
    7

    Re: Non-standard rounding

    I would like to be able to change the break point for rounding up to the even 100 point or down to the even 100 point. For example <=541 =500 and >541 =600

  5. #5
    Registered User
    Join Date
    02-05-2020
    Location
    Iowa
    MS-Off Ver
    365 ver 1912
    Posts
    7

    Re: Non-standard rounding

    Ok that worked perfectly for the example given!! What is "19" in this formula? I thought it would be the break point variable but it does not work that way??

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Non-standard rounding

    if you use =ROUND(A1,-2) it will take anything below 450 and round it down to 400 and anything from 450 up to 549 and round it to 500. 19 is the distance below 450 that 431 is so adding it to 431 makes it 450 so it rounds 431 and anything up to 531 to 500 and anything below 431 to 400.
    hope that helps.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Non-standard rounding

    With standard rounding, anything above (and including) 50 will round up to the next 100, and anything below 50 will round down. You want to round up from 31 and upwards, which is a difference of 19 from the standard round point, hence the +19 to adjust the rounding point. Any of the numbers from 431 to 530 inclusive will round to 500 (try it, with consecutive numbers in column A and the formula in B).

    Hope this helps.

    Pete

  8. #8
    Registered User
    Join Date
    02-05-2020
    Location
    Iowa
    MS-Off Ver
    365 ver 1912
    Posts
    7

    Re: Non-standard rounding

    Thanks guys this was exactly what I was hoping for!!

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Non-standard rounding

    That's good to hear, Steve.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Banker's Round/Half to Even Rounding Double Rounding
    By CastorSunshine in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-01-2019, 02:51 PM
  2. [SOLVED] IF Regular or Standard Hours show as "Regular,Standard"
    By BigErnKingpin in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-03-2019, 04:24 AM
  3. [SOLVED] Converting multiple non-standard time formats to one standard?
    By RandyStone in forum Excel General
    Replies: 6
    Last Post: 06-14-2018, 07:40 AM
  4. MROUND Rounding Issues (Rounding Down, Not Up)
    By roundandout in forum Outlook Formatting & Functions
    Replies: 4
    Last Post: 06-16-2015, 10:39 PM
  5. [SOLVED] Rounding problems using lookup instead of rounding functions
    By thnkfree in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-18-2014, 06:21 PM
  6. Inserting a 'Standard'
    By Metolius Dad in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 02-15-2006, 11:50 PM
  7. Worksheet rounding vs VBA rounding
    By Simon Cleal in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-01-2005, 09:05 PM

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