Round to nearest quarter

  • Thread starter Thread starter TraciAnnNeedsHelp
  • Start date Start date
T

TraciAnnNeedsHelp

I couldn't find this on a previous post anywhere. The Round function rounds
to the specified decimal. I need to round to the nearest quarter (.25)

e.g. If the value is 2.37 then round to 2.25, If the value is 2.38 then
round to 2.5

Thanks!
TraciAnn
 
You will have to do that with code. Here's the general idea:

1. Save original value
2. Get the integer part of original value and subtract it leaving only
decimal part
3. If decimal part is < 0.25 then new decimal part is 0
4. If decimal part is < 0.5 then new decimal part is 0.25
5. If decimal part is < 0.75 then new decimal part is 0.5
6. If decimal part is < 0.99 then new decimal part is 0.75
7. Add new decimal part to integer part

Hope this helps.
 
TraciAnn,

Here is another solution that accommodates negative amounts.

Public Function GetCurQtrs(ByRef curAmount As Currency) As Currency
On Error GoTo Err_GetCurQtrs

Dim curAbsolute As Currency
Dim curDollars As Currency
Dim curCents As Currency

curAbsolute = Abs(curAmount)
curDollars = Int(curAbsolute)
curCents = curAbsolute - curDollars
Select Case curAbsolute - curDollars
Case Is < 0.125
curCents = 0
Case Is < 0.375
curCents = 0.25
Case Is < 0.625
curCents = 0.5
Case Is < 0.875
curCents = 0.75
Case Else
curCents = 1
End Select
GetCurQtrs = Sgn(curAmount) * (curDollars + curCents)

Exit_GetCurQtrs:
Exit Function

Err_GetCurQtrs:
MsgBox "Function is GetCurQtrs" & vbNewLine & _
Err.Number & vbNewLine & Err.Description
Resume Exit_GetCurQtrs

End Function

Jack Cannon
 
I couldn't find this on a previous post anywhere. The Round function rounds
to the specified decimal. I need to round to the nearest quarter (.25)

e.g. If the value is 2.37 then round to 2.25, If the value is 2.38 then
round to 2.5

Thanks!
TraciAnn
Click to expand...

See if this works:

SELECT my_number,
IIf(my_number+0.125>=(Int(my_number/0.25)*0.25)+0.25,(Int(my_number/0.25)*0.25)+0.25,Int(my_number/0.25)*0.25)
AS Rounded
FROM My_Table;
 
Back
Top