+ 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,636

    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,636

    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,636

    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)

Similar Threads

  1. Rounding formulas
    By nygirl175 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-30-2013, 04:22 PM
  2. ROUNDING results of IF formulas
    By rwhite713 in forum Excel General
    Replies: 1
    Last Post: 07-06-2010, 11:21 AM
  3. Rounding(UP/DOWN) formulas
    By BlueLobster in forum Excel General
    Replies: 5
    Last Post: 06-15-2010, 05:26 AM
  4. [SOLVED] Rounding-Formulas
    By fak119 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 05-10-2006, 08:20 AM
  5. [SOLVED] formulas / rounding
    By JT in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-19-2006, 06:55 PM
  6. [SOLVED] Rounding Formulas
    By Chandra in forum Excel General
    Replies: 5
    Last Post: 07-05-2005, 01:05 PM
  7. Rounding UP in formulas
    By vorag in forum Excel General
    Replies: 1
    Last Post: 04-02-2005, 02:03 AM
  8. [SOLVED] formulas for rounding
    By Mas in forum Excel General
    Replies: 1
    Last Post: 02-10-2005, 03:06 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