Rounding to the nearest 0.25

sknight22

Board Regular
Joined
Feb 16, 2016
Messages
67
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
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:

sknight22

Board Regular
Joined
Feb 16, 2016
Messages
67
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
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

Board Regular
Joined
Feb 16, 2016
Messages
67
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
If your cell has time serial values, then:

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

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
Example results:

Excel 2010
Row\Col
A
B
1
Time SerialRound 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

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
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

Board Regular
Joined
Feb 16, 2016
Messages
67
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:

Watch MrExcel Video

Forum statistics

Threads
1,099,580
Messages
5,469,505
Members
406,656
Latest member
Kriscrawford76

This Week's Hot Topics

Top