CODEWITHSUNDEEP
vbaexcel
InWoods
InWoods

Reputation: 105

Excel Numbers with two decimal places

I wrote the following simple code which takes the input from user for N1 and N2 and adds them up and gives them as output (N3).There is an issue with this that, it rounds up each of the input instead of just taking the numbers.

Easy example: N1 = 25.5, N2 = 25.5 Right Answer = 51 but the answer it gives is 52. I am new to programming so would appreciate any help I can get.

I want it to display N3 up to 6 decimal places without rounding individual inputs from N1 and N2 at the time of taking inputs. 

Sub InputVariable()

    Dim N1 As Integer
    Dim N2 As Integer
    Dim N3 As Integer
    N1 = InputBox("Enter 1st number")
    N2 = InputBox("Enter 2nd number")
    N3 = N1 + N2
    MsgBox ("Your total is " & N3)
End Sub

Upvotes: 3

Views: 12510

Answers (2)

arcadeprecinct
arcadeprecinct

Reputation: 3777

You need to use the Double data type. If you use an integer type (Integer or Long) it will round the numbers at N1 = InputBox(...) because it can't store non-integer values in the variables.

edit: Double stands for double precision (8 byte) compared to single precision (4 byte). It is interesting to note that because Double uses the binary number format it can't store exact values like 0.1 (just like the decimal system can't express 1/3 no matter how many digits you have).

If you need to do really precise calculations with decimal numbers, there's the Decimal format that you can use. You can't actually declare it but you can convert a number to decimal and store it in a Variant. See this example:

Sub precisionTest()
    Dim i As Long
    Dim dbl As Double
    Dim dblResult As Double
    Dim dec As Variant
    Dim decResult As Variant

    dblResult = 0
    decResult = 0
    dbl = 0.00001
    dec = CDec(0.00001)
    For i = 1 To 100000
    dblResult = dblResult + dbl
    decResult = decResult + dec
    Next i
    MsgBox "Double result: " & dblResult & vbCr & "Decimal result: " & decResult
End Sub

edit2: Rounding and formatting of numbers: You can use the Format function to create strings of your number without changing the value (for display purposes only). Valid formats look like "0.##" where 0 means the decimal place is always displayed and # means it's displayed if it's non-zero:

Sub formatTest()
    Dim dbl As Double
    Dim dbl2 As Double
    Dim dbl3 As Double
    dbl = 1.234
    dbl2 = 1.2
    dbl3 = 0.1
    MsgBox "Format(1.234,""0.##"") = " & Format(dbl, "0.##") & vbCr _
    & "Format(1.234,""0.00"") = " & Format(dbl, "0.00") & vbCr _
    & "Format(1.2,""0.##"") = " & Format(dbl2, "0.##") & vbCr _
    & "Format(1.2,""0.00"") = " & Format(dbl2, "0.00") & vbCr _
    & "Format(0.1,""#.##"") = " & Format(dbl3, "#.##") & vbCr _
    & "Format(0.1,""0.00"") = " & Format(dbl3, "0.00") & vbCr
End Sub

If you want to actually round your numbers, use Round(number,decimalplaces)

Upvotes: 6

Carmelid
Carmelid

Reputation: 228

As InWoods mentioned, you need N1, N2 and N3 to be double types, then you can cast the output in the print statement, like this:

MsgBox ("Your total is " & Format(N3, "Standard"))

The standard formatting includes two decimal places.

Upvotes: 1

Related Questions