+ Reply to Thread
Results 1 to 9 of 9

Rounding up to specific set of numbers.

  1. 12-10-2015, 08:59 PM #1
    theoudie
    theoudie is offline
    Registered User
    Join Date
    12-09-2014
    Location
    USA
    MS-Off Ver
    13
    Posts
    5

    Rounding up to specific set of numbers.

    For work I need to be able to round numbers up to:
    66
    84
    102
    120
    138
    156
    174
    192
    210
    228
    246
    264
    282

    if I do Ceiling (A1,18) it is not correct.

    How do i get it so 220 will show up as 228, or 263 will show up as 264.

    Thanks in advance.

    Josh
    Reply With QuoteRegister To Reply
  2. 12-10-2015, 09:14 PM #2
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Rounding up to specific set of numbers.

    Hi Josh,

    What if the number to round is greater than 282 or lest than 66?
    HTH
    Regards, Jeff
    Reply With QuoteRegister To Reply
  3. 12-10-2015, 09:19 PM #3
    shg
    shg is offline
    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: Rounding up to specific set of numbers.

    One way:

    Row\Col
    A
    B
    C
    D
    1
    282
    92
    		 C1: Input
    2
    264
    102
    		 C2: =INDEX(A1:A13, MATCH(C1, A1:A13, -1))
    3
    246
    4
    228
    5
    210
    6
    192
    7
    174
    8
    156
    9
    138
    10
    120
    11
    102
    12
    84
    13
    66
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  4. 12-10-2015, 09:20 PM #4
    jason.b75
    jason.b75 is offline
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Rounding up to specific set of numbers.

    Try

    =MAX(66,CEILING(A1-48,18)+48)
    Last edited by jason.b75; 12-10-2015 at 09:43 PM.
    Reply With QuoteRegister To Reply
  5. 12-10-2015, 09:21 PM #5
    theoudie
    theoudie is offline
    Registered User
    Join Date
    12-09-2014
    Location
    USA
    MS-Off Ver
    13
    Posts
    5
    Quote Originally Posted by jeffreybrown View Post
    Hi Josh,

    What if the number to round is greater than 282 or lest than 66?
    If it is less than 66 round up, if it is greater continue the trend of increasing by 18 up to around 500
    Reply With QuoteRegister To Reply
  6. 12-10-2015, 09:25 PM #6
    shg
    shg is offline
    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: Rounding up to specific set of numbers.

    Quote Originally Posted by jason.b75 View Post
    Try

    =CEILING(A1-48,18)+48
    Nice observation.
    Reply With QuoteRegister To Reply
  7. 12-10-2015, 09:49 PM #7
    clabulis
    clabulis is offline
    Forum Contributor
    Join Date
    05-04-2014
    Location
    New Jersey, United States
    MS-Off Ver
    Excel 2010, 2013
    Posts
    120

    Re: Rounding up to specific set of numbers.

    Edited: Ignore post
    Last edited by clabulis; 12-10-2015 at 10:14 PM.
    Reply With QuoteRegister To Reply
  8. 12-10-2015, 09:59 PM #8
    jason.b75
    jason.b75 is offline
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Rounding up to specific set of numbers.

    Quote Originally Posted by clabulis View Post
    =MAX(66,CEILING(B1,18)-6)
    That will round down the first 6 values in each group of 18.
    Reply With QuoteRegister To Reply
  9. 12-10-2015, 10:14 PM #9
    clabulis
    clabulis is offline
    Forum Contributor
    Join Date
    05-04-2014
    Location
    New Jersey, United States
    MS-Off Ver
    Excel 2010, 2013
    Posts
    120

    Re: Rounding up to specific set of numbers.

    Quote Originally Posted by jason.b75 View Post
    That will round down the first 6 values in each group of 18.
    Good catch.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Rounding Numbers after a cancatenation of two numbers with a hyphen
    By daszlosek in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-24-2015, 12:30 PM
  2. Rounding to a specific set of numbers in a table
    By Zipping2010 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-30-2012, 11:12 AM
  3. Rounding a range of numbers to a specific number using an IF statement
    By Jpeppa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-29-2012, 10:21 PM
  4. Rounding Numbers a specific way (Behind the Scenes Too)
    By jonvanwyk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-23-2011, 03:18 PM
  5. Rounding numbers to specific integers
    By Kagemucha in forum Excel General
    Replies: 4
    Last Post: 11-05-2006, 02:23 AM
  6. Specific rounding up of numbers.
    By Stoney in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-19-2006, 07:08 AM
  7. Rounding up to a set of specific numbers?
    By bradles in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-08-2006, 02:20 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1