[RESOLVED] Rounding down an unknown number according to its (negative) size

[FSPH]

Hi there,

a week ago I started a thread called "Rounding up an unknown number according to its size".

I wanted to find a reasonable map category upper bound for model results. For example, result 204.2 would have an upper bound of 250; 2.042 would lead to 2.5; 2,634 would lead to 3,000, etc.

The code below does a great job as long as n > 1.

n = Fix(x) + (5 * 10 ^ (Len("" & Fix(x)) - 2)) - Fix(x) Mod (5 * 10 ^ (Len("" & Fix(x)) - 2))
' where x is Single positive number, n is a Long number. Any x < 1 will give n = 1.

Now I realize that I also need a lower bound for negative numbers. For example, model result "- 127.8" should return "-150".

Would you have any idea how I can achieve this?

Thanks for your help!

[opus]

Looks like you could use the posted formula on the absolute value and put the negative sign onto the result afterwards.

[anhn]

FSPH, Who gave you that formula? You didn't mention the author. That person should be smart?
Opus is right, if you want to use that formula, replace x with Abs(x) then n = Sgn(x) * n.

Here, he gives you the best function you can get to do your job.
This function can be used with any x "Double" value: >0, <0, >1, < 1.

'-- Function Rounup2ndDigit ---------------------------------------------------
'-- Usage: dMaxUp = Rounup2ndDigit(x)
'      or: dMaxUp = Rounup2ndDigit(x, d)
'-- From 0 to dMaxUp will have maximum of 100 steps,
'   d is the value of each step.
'-- With Value Axis of a graph:
'   dMaxUp can be used to set MaximumScale,
'   d can be used to set MajorUnit
'-------------------------------------------------------------------------------
Public Function Rounup2ndDigit(x As Double, Optional dStep As Double) As Double
   Dim sE As String, m As Double
   
   sE = Format(Abs(x), "0.0E+0")
   dStep = CDbl("0.1E" & Mid(sE, 5))
   m = CDbl(sE)
   '-- m was rounded to second non-zero digit, may be up or down.
   '-- if m was round down then round it up.
   If m < Abs(x) Then m = m + dStep
   Rounup2ndDigit = Sgn(x) * m
End Function
'-------------------------------------------------------------------------------
Sub Test()
   Dim m As Double, d As Double
   m = Rounup2ndDigit(0.0023735, d)
   MsgBox "Max = " & m & "  -  Step = " & d
   MsgBox Rounup2ndDigit(-34854.345)
End Sub

[FSPH]

Hello ANHN,

yes, of course, I got the formula from you. That's why I refered to the previous thread from a week ago. I am sorry, I could not remember your acronym ANHN, otherwise I would have mentioned it.

Anyway, thanks very much for the additional code that apparently also deals with values between 0 and 1. I appreciate your input!