locked
VBA Rounding? RRS feed

  • Question

  • two part question:

    1. x=(a/b) how to round to two decimal places

    2. y=c/d) how to roundup to next even multiple of 2

    thanks,

    Doug

    Monday, July 31, 2017 12:05 PM

Answers

  • x = Application.WorksheetFunction.Round(a / b, 2)

    and

    y = Application.WorksheetFunction.Ceiling_Math(c / d, 4)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, July 31, 2017 2:46 PM

All replies

  • x = Application.WorksheetFunction.Round(a / b, 2)

    and

    y = Application.WorksheetFunction.Ceiling_Math(c / d, 4)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, July 31, 2017 2:46 PM
  • Nice quasar picture, which one is that?

    Andreas.

    Private Sub Example_Multiple()
      Debug.Print Multiple(2.5, 1)         '3
      Debug.Print Multiple(-2.5, -2, -1)   '-4
      Debug.Print Multiple(-2.5, 2)        '-2
      Debug.Print Multiple(1.5, 0.1)       '1.5
      Debug.Print Multiple(0.234, 0.01, 1) '0.24
    End Sub
    
    Static Function Multiple(ByVal Value As Variant, ByVal Factor As Variant, _
        Optional ByVal UpDown As Integer = 0) As Variant
      'Rounds Value to the smallest multiple of Factor
      Dim R As Variant, G As Variant
      G = Fix(Value / Factor)
      R = Abs(Value / Factor - G)
      If UpDown = 0 Then
        'Round up or down
        Multiple = G * Factor + IIf(R >= 0.5, Factor * Sgn(Value), 0)
      ElseIf UpDown > 0 Then
        'Round up
        Multiple = G * Factor + IIf(R > 0, Factor * Sgn(Value), 0)
      Else
        'Round down
        Multiple = G * Factor - IIf(R > Factor, Factor * Sgn(Value), 0)
      End If
    End Function
    
    Private Sub Example_Round()
      Dim Numbers, Number
      Numbers = Array( _
        -2.6, -2.5, -2.4, -1.6, -1.5, -1.4, -0.6, -0.5, -0.4, _
        0.4, 0.5, 0.6, 1.4, 1.5, 1.6, 2.4, 2.5, 2.6)
      Debug.Print "Number", "VBA", "Up/Down", "Up", "Down"
      For Each Number In Numbers
        Debug.Print Number, _
          VBA.Round(Number, 0), Round(Number, 0, 0), _
          Round(Number, 0, 1), Round(Number, 0, -1)
      Next
    End Sub
    
    Static Function Round(ByVal Value As Variant, _
        Optional ByVal Places As Integer = 0, _
        Optional ByVal UpDown As Integer = 0) As Variant
      'VBA.Round performs banker's rounding, means Round(1.5) = 2 and Round(2.5) = 2
      'http://support.microsoft.com/kb/196652/en-us
      'https://support.microsoft.com/en-us/kb/78113
      'This function performs a symmetric arithmetic up/down rounding
      Dim i As Long
      Dim Pot10(-28 To 28) As Variant
      Dim Temp As Variant
      If i = 0 Then
        For i = LBound(Pot10) To UBound(Pot10)
          Pot10(i) = CDec(10 ^ i)
        Next
      End If
      If UpDown = 0 Then
        'Round up or down
        Round = Fix(Value * Pot10(Places) + 0.5 * Sgn(Value)) / Pot10(Places)
      ElseIf UpDown > 0 Then
        'Round up
        Temp = Fix(Value * Pot10(Places))
        Round = (Temp + IIf(Value = Temp, 0, Sgn(Value))) / Pot10(Places)
      Else
        'Round down
        Round = Fix(Value * Pot10(Places)) / Pot10(Places)
      End If
    End Function
    

    Monday, July 31, 2017 2:47 PM
  • thanks,

    lots of good info - very helpful!!

    not sure on the Quasar...just like the pic

    Thursday, August 3, 2017 11:30 AM