LinkBack URL
About LinkBacks
Please can anyone help me to create a formula that will round a total to the nearest 0.25?
e.g. 0.45 would be 0.50, 25.22 would be 25.25, 4.67 would be 4.75, 1.88 would be 2.00, etc.
Many thanks.
Please can anyone help me to create a formula that will round a total to the nearest 0.25?
e.g. 0.45 would be 0.50, 25.22 would be 25.25, 4.67 would be 4.75, 1.88 would be 2.00, etc.
Many thanks.
=ROUND(A1*n,0)/n
therefore to nearest quarter would be
=ROUND(A1*4,0)/4
Good morning Dunderlin
davesexcel's already provided you with a perfectly adequate answer, but just in the interests of some of Excel's more obscure functions, you could look at the Ceiling function :
=CEILING(A1,0.25)
HTH
DominicB
Please familiarise yourself with the rules before posting. You can find them here.
Just stumbled on this ~2 years later, but wanted to give a heads up that this solution only rounds up and doesn't go to the nearest. For example, if you have 12.8 hours, you may want to round to 12.75 and not up to 13.Originally Posted by dominicb
In my test point, I have a timesheet that adds hours in 15 minute increments and have an overall hourly goal of 80% to billable to clients. Trying to compare whether or not I'm hitting the target using ceiling wasn't working. The simple round/divide by 4 solution posted by Davesexcel worked perfectly.
Just a note, this thread is 12 years old.
Hah! I had only seen the reply from 2016 and didn't realize the original was from 2006. Either way - helped me out this morning.Just a note, this thread is 12 years old.
I have tried both of these methods but neither seems to work. Try entering 17.7 and neither gives 17.75 ??
=ROUND(A1*4,0)/4
I entered 17.7 in A1 and I got 17.75
What are you getting?
Now its working thanks - my error
Re: Round up to nearest quarter
great spot and thank you to whoever reads this and a giant thank you to anyone who can help.
We need to round up to the nearest 25 in a cell. We would like to apply the formula to the cell
instead of creating another cell with formula reading the cell with data
We need to up quantities for printing on even sheets.
the page has 25 tags. column 7 has all the qtys.
not sure if it is possible without creating a new column for the result
=ROUNDUP(A1/25,0)*25 is what i used and it worked but it is in another column to get the value
we would like to be able to update the value with the formula in the original cell
hope it made sense, thank you in advance.
Glen
949-981-6822
Sub mt() Dim cell As Range On Error GoTo NeverMind For Each cell In ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants, xlNumbers) cell.Value2 = WorksheetFunction.Ceiling(cell.Value2, 25) Next cell NeverMind: End Sub
Entia non sunt multiplicanda sine necessitate
magictrix welcome to the forum
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 2 users browsing this thread. (0 members and 2 guests)
Posting Permissions
Register To Reply
,
Bookmarks