+ Reply to Thread
Results 1 to 3 of 3

Round to Nearest Half Based On Defined Range of Decimal

  1. #1
    Registered User
    Join Date
    03-16-2013
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    11

    Question Round to Nearest Half Based On Defined Range of Decimal

    Rounding traditionally to the nearest half is easy.....But I need to round numbers to the nearest half based on a specific range of decimals in the original number. For example if the number in D6 is:

    28.0 to 28.399 then rounds DOWN to 28
    28.4 to 28.799 then rounds to 28.5
    28.8 to 28.99 then rounds UP to 29

    I am trying to do this without VBA if possible....

    Thank you for any help and insights!
    Last edited by HobbesIsReal; 08-03-2013 at 03:26 PM.

  2. #2
    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: Round to Nearest Half Based On Defined Range of Decimal

    =int(a1) + lookup(round(mod(a1,1), 6), {0,4,8}/10, {0,5,10}/10)
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    03-16-2013
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    11

    Thumbs up Re: Round to Nearest Half Based On Defined Range of Decimal

    Quote Originally Posted by shg View Post
    =int(a1) + lookup(round(mod(a1,1), 6), {0,4,8}/10, {0,5,10}/10)
    This works PERFECTLY!!!!! I never would have come up with that myself.

    .....Now I just have to learn why (or how) it works so I can do it on my own next time!

+ 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. Round the 4th decimal to the nearest 5 or 0
    By Zaxivette in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-14-2013, 01:57 PM
  2. Replies: 2
    Last Post: 01-22-2013, 02:05 PM
  3. How to Round up to nearest whole number at a specific decimal?
    By trpost in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-26-2005, 02:05 PM
  4. [SOLVED] Round a decimal to the nearest sixteenth
    By crazybass2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-22-2005, 02:05 PM
  5. Round to nearest quarter & half
    By hailnorm in forum Excel General
    Replies: 1
    Last Post: 04-14-2005, 01:47 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1