How to round to nearest .25 (times)

Requirement:

The user needs to round numbers to the nearest .25, e.g. 8.39 to 8.50, 4.21 to 4.25.

Solution:

There is a function to do it directly. It requires the Analysis Tool Pack (add-in) be attached.

=MROUND(a1,0.25)

The Analysis Toolpak ships with Excel - it is part of standard Excel, but you have to activate the addin.

see here for instructions

http://office.microsoft.com/en-us/as...277241033.aspx

Dates are stored numerically, starting with the number 1 (which was 1/1/1900) - on that basis, Excl recognises today as being 38,384 - this is why we can subtract 1 date from another and calculate the elapsed days....

Time values are similar. Granted that 24 Hours has the value of 1 (in date terms), the following logic should make sense.

24 Hours can be measured as 1/24

i.e. if a day (in excel terms) is 1 - then it follows that excel thinks of an hour as 1/24

OK... hope you're still with me...

So a quarter of an hour is 1/24/4 That's a 1 divided by 24 divided by 4 - this can also be expressed as 1/(24*4)

So, let's go back to our problem.

Let's say A1 has the time 12:26

In A2 type

=MROUND(A1,1/(24*4))

Hey... the answer is 12:30

Which is what you want.

If you needed to alter it to round to the nearest 10 minute slot..... well, there are 4*15 minutes in 1 hour & 6*10 minutes...

so it's

=MROUND(A1,1/(24*6))