rounding dependent on value

  • Thread starter Thread starter Saintsman
  • Start date Start date
S

Saintsman

How can I set rounding to change according to calculated value

ie if value is less than 10=round to 2 dec place; 10 to 100=round to 1 dec
place; over hundred=round to 0 dec places

Any offers?
Thanks
Saintsman
 
Saintsman said:
How can I set rounding to change according to calculated value

ie if value is less than 10=round to 2 dec place; 10 to 100=round to 1 dec
place; over hundred=round to 0 dec places
Click to expand...

Sure, this should work for you:

Function Round2vP(dblIn As Double)
' Arvin Meyer 4/16/2009
On Error Resume Next ' For use in queries

Dim factor As Double
Dim scaled

If IsNumeric(dblIn) Then
Select Case dblIn
Case Is < 10
factor = 10 ^ 2
scaled = dblIn * factor
Round2vP = Int(scaled + 0.5) / factor
Case Is <= 100
factor = 10 ^ 1
scaled = dblIn * factor
Round2vP = Int(scaled + 0.5) / factor
Case Else
factor = 10 ^ 0
scaled = dblIn * factor
Round2vP = Int(scaled + 0.5) / factor
End Select
Else
Round2vP = dblIn
End If

End Function
 
WHERE are you attempting to do this?

You can use something like the following expression in most places
Round(x,IIF(x<10,2,IIF(x<100,1,0)))
And that assumes that all your numbers are positive. If you have negative
numbers you will need a more complex expression, since all negative numbers
are less than 10. YOu can probably just use the abs function to determine the
relative size of the number.

Round(x,IIF(Abs(x)<10,2,IIF(Abs(x)<100,1,0)))


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top