Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to round up with excel VBA round()?

Tags:

excel

vba

I have the following data:

cell(1,1) = 2878.75
cell(1,2) = $31.10
cell(2,1) = $89,529.13

However, when I tried to use round(cells(1,1).value*cells(1,2).value),2), the result does not match cell(2,1). I figured it has to do with the rounding issue, but I'm just wondering if it is possible to get round() to act normally. That is, for value > 0.5, round up. And for value < 0.5, round down?

like image 956
Ting Ping Avatar asked Apr 15 '13 14:04

Ting Ping


People also ask

Does round () round Down?

The round() function rounds a number to the nearest whole number. The math. ceil() method rounds a number up to the nearest whole number while the math. floor() method rounds a number down to the nearest whole number.

How do you round up in Visual Basic?

Use RoundUp() to round borderline numbers up. Round a number to the specified number of decimal places (0.5 is rounded up). The VBA/VB6 Round function performs Bankers Rounding which rounds 0.5 up or down depending on whether the previous digit is even or odd.

What is round () function?

The ROUND function rounds a number to a specified number of digits. For example, if cell A1 contains 23.7825, and you want to round that value to two decimal places, you can use the following formula: =ROUND(A1, 2) The result of this function is 23.78.


2 Answers

If you want to round up, use half adjusting. Add 0.5 to the number to be rounded up and use the INT() function.

answer = INT(x + 0.5)

like image 175
Steven Alpha Avatar answered Oct 03 '22 06:10

Steven Alpha




Try this function, it's ok to round up a double

'---------------Start -------------
Function Round_Up(ByVal d As Double) As Integer
    Dim result As Integer
    result = Math.Round(d)
    If result >= d Then
        Round_Up = result
    Else
        Round_Up = result + 1
    End If
End Function
'-----------------End----------------
like image 20
ana Avatar answered Oct 03 '22 07:10

ana