+ Reply to Thread
Results 1 to 3 of 3

Rounding based on a cut-off number

  1. #1
    Registered User
    Join Date
    01-11-2019
    Location
    USA
    MS-Off Ver
    7
    Posts
    1

    Rounding based on a cut-off number

    Hello,

    I am trying to round times in a way that is inconsistent with the default MROUND command

    I need to round times to the half-hour, but based on a cut-off of :16, rather than the Excel default of :15 (i.e., MROUND). Exact half-hour and hour times should remain unchanged.

    Examples:
    1:15 rounds down to 1:00 and 1:16 rounds up to 1:30.
    1:43 rounds down to 1:30 and 1:47 rounds up to 2:00.
    1:30 = 1:30 and 2:00 = 2:00

    Is there an easy way to do this? Thanks for the assistance!

  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: Rounding based on a cut-off number

    a
    b
    c
    2
    1:15
    1:00
    b2: =mround(a2 - "0:1", "0:30")
    3
    1:16
    1:30
    4
    1:30
    1:30
    5
    1:43
    1:30
    6
    1:47
    2:00
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Rounding based on a cut-off number

    What is the precision of these times? Are all of them exact minutes, or are there seconds involved? What should 1:15:30 round to (assuming seconds are involved)? I don't understand the 3/4 hour -- what should 1:44:00 round to? 1:45:00? 1:46:00?

    My idea would be to still use MROUND(), but modify the time value a little. Maybe subtract 30 seconds (or other suitable value) from the time then MROUND().

    MROUND(time(1,16,0)-time(0,0,30),time(0,30,0)) should yield 1:30:00, but MROUND(time(1,15,0)-time(0,0,30),time(0,30,0)) should return 1:00:00.

    Would something like that work, or am I misunderstanding something?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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 number down instead of up
    By jambog in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-22-2018, 04:04 AM
  2. [SOLVED] Rounding number based on arbitrary value
    By hamidun in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-06-2016, 08:48 AM
  3. [SOLVED] Cell with text and number, not rounding to closest full number
    By cblp in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-03-2014, 07:51 PM
  4. [SOLVED] Rounding the number produced by an evaluated expression to the nearest number in CF
    By orly_sm in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-29-2013, 09:16 AM
  5. [SOLVED] Rounding up a decimel number to a whole number
    By Gunner 14 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-27-2012, 03:21 AM
  6. Rounding to the next whole number
    By mpeplow in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-19-2007, 04:46 PM
  7. [SOLVED] number rounding
    By tamiluchi in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-20-2006, 01:10 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