Rounding

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.

try that one

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.

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.

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

Code:

---

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

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 :

Code:

---

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

---

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.

Code:

---


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

---

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

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

Code:

---

    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

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

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