How to round to nearest .25 (times)
Category: [Excel] Demo Available
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))
Obtained from the OzGrid Help Forum.
Solution provided by Will Riley and thomach.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to create a macro to round total based on a cell value |
| How to use ROUND and MROUND |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.