Rounding to the nearest 0.25

[sknight22]

Good morning,

I have a sheet that calculates waiting time and I am using

=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

 

[Jon von der Heyden]

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?

 

[sknight22]

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

 

[steve the fish]

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.

 

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

 

[Jon von der Heyden]

If your cell has time serial values, then:

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

 

[Jon von der Heyden]

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

 

[steve the fish]

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?

 

[sknight22]

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

 

[Jon von der Heyden]

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