# Thread: Rounding to the nearest 0.25 Thanks:  1 Post #5184071 (1) Likes:  2 Post #5184071 (2)

1. ## 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. ## 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?

3. ## Re: Rounding to the nearest 0.25

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. ## 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. ## 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. ## Re: Rounding to the nearest 0.25

If your cell has time serial values, then:

=CEILING(A1,"00:15")*24

7. ## 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

8. ## Re: Rounding to the nearest 0.25

Originally Posted by sknight22
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. ## 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

10. ## Re: Rounding to the nearest 0.25

Originally Posted by steve the fish
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