Rounding

[Jimbob]

How can I make sure a number is always rounded up, regardless of what follows the decimal point

Int() and Fix() seem to caouse it to be rounded down.

[kovan]

try that one

[Bigley]

Well I guess depending on how many decimal points you can have you could always add 0.999.... to the number and then round it, but don't give me a hard time on this one if it is complete rubbish, I am on my lunch break here.

[jbart]

Results using the ROUND() function recommended by kovan.

? ROUND(1234.1962, 3) && Displays 1234.1960
? ROUND(1234.1962, 2) && Displays 1234.2000
? ROUND(1234.1962, 0) && Displays 1234.0000
? ROUND(1234.1962, -1) && Displays 1230.0000
? ROUND(1234.1962, -2) && Displays 1200.0000
? ROUND(1234.1962, -3) && Displays 1000.0000


Syntax

ROUND(expression[,numdecimalplaces])

If you omit the numdecimalplaces, an integer is returned.

[Jimbob]

well, i've kinda sorted it like this, but it's a dodgy as hell kinda way of doing it: -

Public Function RoundUp(Num As Long) As Integer
Dim NumStr, TempStr1, TempStr2 As String
Dim X As Integer

    NumStr = CStr(Num)
    TempStr1 = ""
    TempStr2 = ""
    
    For X = 1 To Len(NumStr)
        TempStr1 = Mid(NumStr, X, 1)
        If TempStr1 = "." Then
            Exit For
        Else
            TempStr2 = TempStr2 + TempStr1
        End If
    Next X
    
    RoundUp = (CInt(TempStr2) + 1)
        
End Function

there has to be a mathematical way of doing this - but i struggle to add up, never mind do hard stuff

[Paul Warren]

For info. VB does some strange rounding when it rounds a decimal to an integer on your behalf. When the decimal is in the middle ( .5 ) then for odd numbers it rounds up and evens number round down ! 1.5 and 2.5 are both rounded to 2.

Try this code if you don't beleive me :

Private Sub Form_Load()

Dim sTemp1 As String
Dim sTemp2 As String
Dim iTemp1 As Integer
Dim iTemp2 As Integer

' Remember this lesson when changing values from string to integer
sTemp1 = "1.5"
sTemp2 = "2.5"
iTemp1 = Val(sTemp1)
iTemp2 = Val(sTemp2)

MsgBox iTemp1 & "  " & iTemp2, vbOKOnly, "VB Rounding"

End Sub

[jbart]

Well, then ROUND() is not going to be very reliable.

In the immediate window you get.

? ROUND(1.5, 0)
2
? ROUND(2.5, 0)
2

Paul, thanks for passing that on.

[HeSaidJoe]

Option Explicit
'rounding a number 

Function RoundNumber(lNumber, Optional iDecimalPlaces As Integer = 1)
    RoundNumber = Int(lNumber * (10 ^ iDecimalPlaces) + 0.5) / (10 ^ iDecimalPlaces)
End Function

'3 represents the number of places you want after the decimal
Private Sub Command1_Click()
    MsgBox RoundNumber(34567.8987, 3)
End Sub

[Jimbob]

that last one seems to round up OR down depending upon the value of the leftmost digit being removed, as normal meths would have it, but for my situation I am converting days to months for insurance cover - ie 35 days needs to be rounded up into a 2 month policy...

[QWERTY]

Do you consider one month as 31 days? If yes then try this:

    Dim inDays As Integer: inDays = Text1.Text
    Dim inMonths As Integer
    
    If inDays Mod 31 <> 0 Then
        inMonths = inDays \ 31 + 1
    Else
        inMonths = inDays \ 31
    End If
    
    MsgBox inMonths '//If inDays = 35 then inMonths returns 2

HTH

[Jimbob]

how can I force a number to have 2 decimal places??

ie 3 would appear as 3.00

tried Round(3,2), but just reduces the number of decimal places to 2, rather than increasing them

[jbart]

Look up the Format command and see the different ways it allows you to format numbers, dates, strings.

For example: format(3,"#.00")

returns 3.00

format(3.9,"#.00")

returns 3.90