Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Rounding to the nearest 0.25

  1. #1
    Board Regular
    Join Date
    Feb 2016
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Rounding to the nearest 0.25

    Good morning,

    I have a sheet that calculates waiting time and I am using
    Code:
    =IFERROR(MROUND(AF2,0.25),"")
    to round up times to the nearest 0.25

    I have a problem though. When a time is entered e.g 2.55 (2hrs 55mins) it returns 2.50 when I need it to show 2.75.

    Is there a way to alter this to work?

    Hope that makes sense

  2. #2
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    London, UK
    Posts
    10,657
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Rounding to the nearest 0.25

    Hi

    To round up you to a significance you can also use CEILING. E.g =CEILING(2.55,0.25)

    You can also roundup time values (e.g. 02:55) to a significance of 15 minutes, e.g. =CEILING("02:55","00:15")

    I'm slightly confused though. If 2.55 is 2 hours 55 mins, what is 2.75?
    Last edited by Jon von der Heyden; Nov 28th, 2018 at 04:53 AM.
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs
    English is a weird language. It can be understood through tough thorough thought, though!

  3. #3
    Board Regular
    Join Date
    Feb 2016
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rounding to the nearest 0.25

    Thanks for your reply.

    Basically I want to a to reference a cell with times in it e.g 1.10 (1hr 10mins) and have another cell round it up in multiples of 0.25 - so 1.10 would show 1.25/

    0.25 is 15 mins - 0.50 is 30 mins - 0.75 is 45 mins

    Sorry im rubbish at explaining

  4. #4
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,363
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Rounding to the nearest 0.25

    If you are not using the built in time format eg 01:15 and instead using decimals eg 1.15 then you could use:

    =INT(AF2)+CEILING(MOD(AF2,1)/0.6,0.25)

    That said id use the time format as supplied in excel.

  5. #5
    Board Regular
    Join Date
    Feb 2016
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rounding to the nearest 0.25

    Thank you,

    I would prefer to use the time forwat - would I need a different formula for that?

    Also with the above it rounds up 1.16 to 1.50 whereas if i was doing it manually i would count that as 1.25 as its closer to 1.15 than 1.30.

  6. #6
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    London, UK
    Posts
    10,657
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Rounding to the nearest 0.25

    If your cell has time serial values, then:

    =CEILING(A1,"00:15")*24
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs
    English is a weird language. It can be understood through tough thorough thought, though!

  7. #7
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    London, UK
    Posts
    10,657
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Rounding to the nearest 0.25

    Example results:

    Excel 2010
    Row\Col
    A
    B
    1
    Time Serial Round Up .25
    2
    01:05
    1.25
    3
    01:10
    1.25
    4
    01:15
    1.25
    5
    01:20
    1.5
    6
    01:25
    1.5
    7
    01:30
    1.5
    8
    01:35
    1.75
    9
    01:40
    1.75
    10
    01:45
    1.75
    11
    01:50
    2
    12
    01:55
    2
    13
    02:00
    2
    14
    02:05
    2.25
    Sheet: Sheet1
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs
    English is a weird language. It can be understood through tough thorough thought, though!

  8. #8
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,363
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Rounding to the nearest 0.25

    Quote Originally Posted by sknight22 View Post
    Thank you,

    I would prefer to use the time forwat - would I need a different formula for that?

    Also with the above it rounds up 1.16 to 1.50 whereas if i was doing it manually i would count that as 1.25 as its closer to 1.15 than 1.30.
    Did you not say you wanted to round up?

  9. #9
    Board Regular
    Join Date
    Feb 2016
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rounding to the nearest 0.25

    Thats great thank you very much.

    The only thing I notice is that it doesn't round up or down to the nearest 0.25 or 0.50 or 0.75

    So when I have 02:30 it shows 2.50 (which is correct) but when I have 02:31 it rounds up to 2.75 rather than the nearest which should still be 2.50

    any ideas?

    Sorry i explained badly - I wanted it to round up or down to the nearest 0.25
    Last edited by sknight22; Nov 28th, 2018 at 05:58 AM.

  10. #10
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    London, UK
    Posts
    10,657
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Rounding to the nearest 0.25

    Quote Originally Posted by steve the fish View Post
    Did you not say you wanted to round up?
    I was also working to that assumption. Adjusting my formula:

    =CEILING(A2-"00:07:30","00:15")*24
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs
    English is a weird language. It can be understood through tough thorough thought, though!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •