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

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!

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

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.

Code:

---

'-- 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

---

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!