+ Reply to Thread
Results 1 to 6 of 6

Rounding Formulas

  1. #1
    Registered User
    Join Date
    02-07-2016
    Location
    Canada
    MS-Off Ver
    13
    Posts
    65

    Red face Rounding Formulas

    Hi experts,

    I need help setting up formulas to round pricing (some very specific rules):

    -If less than $100, rounds to nearest $, no cents
    -If $100-$105, rounds to $99
    - Between$100 -$500 - price ends in 5 or 9, but 5 never follows 0 (no $105 => 109 instead, no $205=> 209 instead etc)
    - Between $500-$1000 Price ends in 5 or 9; 515 is the lowest; never use 0 (515, 615, 699, 799 etc)
    - Over $1,000 rounds to $25/$50/$75/$99 increments

    I hope you will be able to help!

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,299

    Re: Rounding Formulas

    >>-If $100-$105, rounds to $99
    do you mean 100 to 999?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    02-07-2016
    Location
    Canada
    MS-Off Ver
    13
    Posts
    65

    Re: Rounding Formulas

    Hi! No, if the value is between 100 and 105, make it 99
    Sorry for confusion, and thanks for your help!

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,299

    Re: Rounding Formulas

    i soon regretted starting this shortly after i started

    spent way too much time on this finicky thing

    anyways see the attached file
    mixture of ceiling/mod formulas

    ceiling to round up to nearest number
    mod to divide the number leaving remainder

    flushed out many of the nuances but i'm sure i may have missed some
    anyway you have the ground work to complete it
    Attached Files Attached Files
    Last edited by humdingaling; 01-03-2018 at 09:52 PM. Reason: wont even let me post the formula...

  5. #5
    Registered User
    Join Date
    02-07-2016
    Location
    Canada
    MS-Off Ver
    13
    Posts
    65

    Re: Rounding Formulas

    Thank you so much! It's perfect! I am sorry for the time you spent on it, I really appreciate it!

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,299

    Re: Rounding Formulas

    not a problem

    i just thought it would of been easier when i first started

+ 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