KJKAccess
asked on
Rounding up to the nearest 0.25 in Access
I would like to be able to round up to the nearest 0.25 in Access. The number I am rounding is always positive btw. I have searched this into the ground and thought I had an answer...but something is wrong and I have no idea what to change to make it work. I added the following module to my database...
Function RoundToNearest(dblNumber As Double, varRoundAmount As Double, _
Optional varUp As Variant) As Double
Dim dblTemp As Double
Dim lngTemp As Long
dblTemp = dblNumber / varRoundAmount
lngTemp = CLng(dblTemp)
If lngTemp = dblTemp Then
RoundToNearest = dblNumber
Else
If IsMissing(varUp) Then
' round up
dblTemp = lngTemp + 1
Else
' round down
dblTemp = lngTemp
End If
RoundToNearest = dblTemp * varRoundAmount
End If
End Function
I then put the following in my query...RoundToNearest([My Number],0. 25).
It delivers...
2.6616 as 3.00 (should be 2.75)
2.9683 as 3.25 (should be 3.00)
2.7921 as 3.00 (ok)
2.6019 as 2.75 (ok)
3.2427 as 3.50 (should be 3.25)
2 out of 5 just doesn’t work. What do I need to change or is there another way?
Thank you in advance...this one is hurting my head!!
Function RoundToNearest(dblNumber As Double, varRoundAmount As Double, _
Optional varUp As Variant) As Double
Dim dblTemp As Double
Dim lngTemp As Long
dblTemp = dblNumber / varRoundAmount
lngTemp = CLng(dblTemp)
If lngTemp = dblTemp Then
RoundToNearest = dblNumber
Else
If IsMissing(varUp) Then
' round up
dblTemp = lngTemp + 1
Else
' round down
dblTemp = lngTemp
End If
RoundToNearest = dblTemp * varRoundAmount
End If
End Function
I then put the following in my query...RoundToNearest([My
It delivers...
2.6616 as 3.00 (should be 2.75)
2.9683 as 3.25 (should be 3.00)
2.7921 as 3.00 (ok)
2.6019 as 2.75 (ok)
3.2427 as 3.50 (should be 3.25)
2 out of 5 just doesn’t work. What do I need to change or is there another way?
Thank you in advance...this one is hurting my head!!
Here's another formula you can try:
=INT(4*[YourNumberField]+0 .5)/4
Flyster
=INT(4*[YourNumberField]+0
Flyster
To get the results you specified, where the value is higher than the input number (A1 is your number)
=ROUND(A1*4+0.5,0)/4
=ROUND(A1*4+0.5,0)/4
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Tested Gustav's comment. I prefer it as a solution. My comment fails for .5 value.
ASKER
Thanks! I waited way too long to ask for help. This is awesome!!
ASKER
"Round" goes in which ever direction is closer...
So 2.76 would round to 2.75...but I need it to round up in all instances (go to 3.00 for this example)
So 2.76 would round to 2.75...but I need it to round up in all instances (go to 3.00 for this example)
You are welcome!
/gustav
/gustav
round(yourNumber * 4,0)/4