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
[Table="width:, class:head"][tr=bgcolor:#888888][th]_Row\^Col[/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]Time Serial[/td][td]Round Up .25[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]

01:05​

[/td][td]

1.25​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]

01:10​

[/td][td]

1.25​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

01:15​

[/td][td]

1.25​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]

01:20​

[/td][td]

1.5​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]

01:25​

[/td][td]

1.5​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]

01:30​

[/td][td]

1.5​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td]

01:35​

[/td][td]

1.75​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td]

01:40​

[/td][td]

1.75​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td]

01:45​

[/td][td]

1.75​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

11​

[/td][td]

01:50​

[/td][td]

2​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

12​

[/td][td]

01:55​

[/td][td]

2​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

13​

[/td][td]

02:00​

[/td][td]

2​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

14​

[/td][td]

02:05​

[/td][td]

2.25​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[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