Rounding up a number

[leonepaolo]

Hi!

How can I round up any number to the next interger or long.

i.e. 30.00001 = 31

Thanks in advance,
Paolo

 

[Ed2020]

Dim x as integer

x = int(30.000001) + 1

Ed Metcalfe

Please do not feed the trolls.....

 

[leonepaolo]

Thanks for the tip.

 

[macropod]

Hi Ed,

Simpler still:

Sub test()
Dim x
x = 30.000001
MsgBox -Int(-x)
End Sub

Cheers

[MS MVP - Word]

 

[johnwm]

.. but watch out for what happens with a negative valued input. You may also like to look at the Fix function.

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[Andrzejek]

Ed2020 what about te whole number?

Dim x as integer

x = int(30) + 1

x = 31

I don't think leonepaolo wants that....


Have fun.

---- Andy

 

[macropod]

Hi johnwm,

With my code, if x = 30.000001, the returned result is 31 and, if x = -30.000001, the returned result is 30. I'd say it's rounding up correctly in both cases.

Cheers

[MS MVP - Word]

 

[johnwm]

I didn't say it was wrong, as it wasn't clear what the OPs 'up' meant - I just gave a heads up that he may not get the result he expected.

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[HarleyQuinn]

Just for anyone interested, here's a useful article regarding rounding (it also has examples of several custom rounding functions including one that does what it sounds like the OP is after):Rounding
Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Ed2020]

Andy,

Good point - hadn't thought about integers being passed to the function.

Ed Metcalfe.

Please do not feed the trolls.....

 

[HughLerwill]

I originally tried to post this with a lengthy explanation but it got lost when I tried to Sumbit it. The notes now included are but a shadow! It's dinner time...

You actually need a function which rounds numbers to any number of decimal places. Vb has the Round function; but that has issues

http://www.xbeat.net/vbspeed/c_Round.htm

Any rounding function which return a numeric variable type runs the risk of returning a 'silly' number eg. 1.1200001. To print the rounded result you have to resort to Format$ which has fixed rounding rules(next or even I think).

So consider having the function return a string hammered into the required format.

The following does Standard rounding with consideration for the computer's decimal character.

Public Function round$(ByVal n As Single, ByVal places&, Optional ByVal formatResult As Boolean = True)

Static dp$, p!, Prevn!, PrevNPlaces&, PrevResult$, PrevFormat As Boolean, WeMustSupplyLeadingZero As Boolean

If p = 0 Then
WeMustSupplyLeadingZero = Asc(CStr(0.1)) <> 48 'By default OS will usually supply them; affected by OS regional settings
p = 1!
dp$ = dpChar$()
End If

If PrevNPlaces = places Then
If n = Prevn Then
If formatResult = PrevFormat Then
round$ = PrevResult$
Exit Function
End If
End If
Else
p = 10 ^ places
PrevNPlaces = places
End If
Prevn = n
PrevFormat = formatResult

round$ = n
If InStrB(round$, dp$) Then 'its a fractional number having a decimal portion
If Len(round$) - InStr(round$, dp$) = places + 1 Then ' the original figure is one place longer than required
If right$(round$, 1) = "5" Then
' has a 5 in the position requiring round off
'make sure it will be rounded up, that is away from zero
Mid$(round$, Len(round$), 1) = "6"
n = Val(round$)
End If
End If
round$ = Int(n * p + 0.5!) / p
ElseIf places < 0 Then
round$ = Int(n * p + 0.5!) / p
'Else
'its a whole number and there is no need to round it
End If

If formatResult Then
'leading zero
If WeMustSupplyLeadingZero Then
If InStrB(round$, dp$) = 1 Then
round$ = "0" + round$
ElseIf InStrB(round$, "-" + dp$) = 1 Then
round$ = " " + round$
Mid$(round$, 1, 3) = "-0" + dp$
End If
End If
'trailing dp and zeroes
If places > 0 Then
If InStrB(round$, dp$) = 0 Then round$ = round$ + dp$ + "0"
While Len(round$) - InStr(round$, dp$) < places
round$ = round$ + "0"
Wend
End If
End If

PrevResult$ = round$

End Function

Function dpChar$()

dpChar$ = Mid$(Format$(0.1, "fixed"), 2, 1)

End Function

Function Val#(ByVal txt$)

'Wraps VBA.Val
' VBA val only works on decimals when the decimal char is "."

Static normal As Boolean, init As Boolean, dp$
If init = False Then
dp$ = dpChar()
normal = (dp$ = ".")
init = True
End If

If normal Then
Val = VBA.Val(txt$)
Else
Val = VBA.Val(Replace(txt$, dp$, "."))
End If

'VB help recommends CDbl but..
'the following dos'nt work with strings that contain numbers and text
' like eg. "5.4 mp" because the error is tripped and 0 is returned
'On Error Resume Next
'Val = CDbl(txt$)

End Function

 

[BobRodes]

<Simpler still:

Eh? Seems less so to me.

 

[genomon]

This can also be modified:

Public Function RoundTotal(ByVal dblNumber As Double, ByVal intDecimals As Integer) As Double

  'Comments   : 
  '           : 0.5 is rounded up
  'Parameters : dblNumber - number to round
  '           : intDecimals - number of decimal places to round to
  '           : (positive for right of decimal, negative for left)
  'Returns    : Rounded number

    Dim dblFactor As Double
    Dim dblTemp As Double         ' Temp var to prevent rounding problems in INT()

    dblFactor = 10 ^ intDecimals
    dblTemp = dblNumber * dblFactor + 0.5
    RoundTotal = Int("" & dblTemp) / dblFactor
  
End Function

Silence is golden.
Duct tape is silver.

 

[HughLerwill]

?RoundTotal(-1.2345,3)
-1.234

 

[HughLerwill]

genomon,

Please disregard my previous you said up and up is what it does!

 

[macropod]

Hi Bob,

IMHO '-int(-x)' is not only simpler than 'int(x)+1', it also gives the correct answer, whereas Andrzejek has already pointed out that 'int(x)+1' isn't reliable.

You could also get the correct answer with 'int(x + 0.5)'.

Cheers

[MS MVP - Word]

 

[BobRodes]

<Andrzejek has already pointed out
So he has, I stand corrected.

<You could also get the correct answer with 'int(x + 0.5)'.
I don't see this, or don't understand what you're saying. 30.4 goes to 30. If you meant 'round(x + 0.5)' that doesn't work either, owing to "Banker's rounding": 30.0 will return 30, but 31.0 will return 32.

I think your original formula is the correct one.

 

[macropod]

Hi Bob,

I didn't mean 'round(x + 0.5)' - I meant 'int(x + 0.5)', as I said, which is fundamentally different.

Cheers

[MS MVP - Word]

 

[johnwm]

In the dark days of Microsoft Basic 80 (early 1980's) before there was a Round() function, the normal method of rounding was to use Int(x + 0.5), which performs substantially like a modern Round, but without the stats correction (known as bankers rounding)

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[zarkon4]

Couldn't you just do this:

x=iif(x>int(x),x=int(x)+1,x)