EDIT: Correct solution below. This was a brain fault. I think there is a function to do this directly -- I'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.
Compare Excel | Excel Templates | DownloaderXL Pro
Finance Templates & Add-In Bundle | NeuroXL Predictor | Construction Estimating
Merge Excel The Easy Way | Trading Add-ins For Excel | Convert Excel Into Calculating Web Pages
Excel Web Pages | Produce Clean Efficient VBA Code Every Time | Build Automated Trading Models In Excel | Excel Web Pages | Excel Video Training
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.
EDIT: Correct solution below. This was a brain fault. I think there is a function to do this directly -- I'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.
BRAIN DEAD (me, not you): Sorry, I was rushing out. Use:
=round(a1*4,0)/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)
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.
Please help me with this problem and give me clear instructions.
Last edited by ccstudent; February 1st, 2005 at 08:36. Reason: found I CAN use the tool pack
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
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.
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...
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
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))
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))
Hope this helps.... at least more so than "Professor Vague"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks