Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Rounding to nearest .25 (times)

1. I agreed to these rules
Join Date
28th May 2003
Posts
1
I need to round numbers to the nearest .25, e.g. 8.39 to 8.50, 4.21 to 4.25, etc)

Any help would be appreciated.

Excel Video Tutorials / Excel Dashboards Reports

2. EDIT: Correct solution below. This was a brain fault. I think there is a function to do this directly -- I&#039;m blanking. But the following should work : if you number is in A1:
=round(a1*100,0)/100

Format the cell to two decimal places.

Excel Video Tutorials / Excel Dashboards Reports

3. BRAIN DEAD (me, not you): Sorry, I was rushing out. Use:
=round(a1*4,0)/4

Excel Video Tutorials / Excel Dashboards Reports

4. There is a function to do it directly (Thank you, Derk). It requires the Analysis Tool Pack (add-in) be attached.
=MROUND(a1,0.25)

Excel Video Tutorials / Excel Dashboards Reports

5. I agreed to these rules
Join Date
1st February 2005
Posts
11

## Re: Rounding to nearest .25

This has been asked before but I am not understanding it. Would someone please help me with my assignment for school? My assignment is this:

How do you round the product of one cell multiplied by another cell to the nearest \$.25? She is not looking for the problem to be rounded to 2 decimal places. She is wanting to be able to round to the nearest quarter of a cent.

For example: 4.92 would be rounded to 5.00 and 4.78 would be rounded to 4.75.

The answer will also work for rounding time to the nearest quarter of an hour or tenth of an hour, ect.

Last edited by ccstudent; February 1st, 2005 at 07:36. Reason: found I CAN use the tool pack

Excel Video Tutorials / Excel Dashboards Reports

6. ## Re: Rounding to nearest .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

Then you can use the formula

=MROUND(A1,0.25)

Assuming A1 contains 4.78, the answer will be 4.75

7. I agreed to these rules
Join Date
1st February 2005
Posts
11

## Re: Rounding to nearest .25

That was VERY helpful. Thank you very much! Now I am having trouble rounding up to the nearest quarter hour. Please be patient with me due to the fact I am learning the program and I am blonde Thanks again for all your help.

Excel Video Tutorials / Excel Dashboards Reports

8. ## Re: Rounding to nearest .25

Can you attach a small example of what you are trying to do...

I am learning the program and I am blonde

There are only stupid answers here... there are no stupid questions...

9. I agreed to these rules
Join Date
1st February 2005
Posts
11

## Re: Rounding to nearest .25

You helped me to understand rounding help (whiched worked, THANK GOD) Now I need to be able to apply the formule to rounding up to the nearest quarter hour or tenth of an hour (example: 2:36a.m. to 2:45am and 4:05p.m. to 4:15p.m.).

My instructor was telling me to take 1/(24*4) and to figure it out. But mind you I am not a huge mathmatical person (in fact I do only basic math) so I am confused for sure.

Thanks again, Steffanie

Excel Video Tutorials / Excel Dashboards Reports

10. ## Re: Rounding to nearest .25

OK, I can see what he's getting at, but he's being a bit of a vague arse if he's supposed to be teaching you. I mean, you don't know Excel right?

OK, unless you know Excel, you won't know the peculiar way it stores dates & times.

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))

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))

Hope this helps.... at least more so than "Professor Vague"

There are currently 4 users browsing this thread. (0 members and 4 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno