Rounding Based on Value

[Kevin Sprinkel]

We prepare conceptual cost estimates for architectural
clients, and frequently do unit cost math in a cell. I
would like to program a macro to round the calculated
value appropriate to its value, e.g.:

<$1 Nearest penny $.51
<$10 Nearest dollar $9.00
<$100 Nearest ten $90.00

$1000 TBD

I've programmed in Access but have only used the Macro
recorder in Excel. Can someone tell me how to construct
an appropriate Switch statement?

Kevin Sprinkel

 

[Bob Phillips]

Kevin,

Something like

If myvalue < 1 Then
myvalue = Round(myvalue, 2)
ElseIf myvalue < 10 Then
myvalue = Round(myvalue, 0)
ElseIf myvalue < 100 Then
myvalue = Round(myvalue / 10, 0) * 10
Else
'TBD
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Kevin Sprinkel]

Thanks, Bob.

 

[Jerry W. Lewis]

http://groups.google.com/[email protected]

gives a formula for rounding to a specified number of significant figures.

Jerry