Dmytro Pastovenskyi commented on Oct 10, 2017

Re: When rounding 0.5 equals 0... and what to do about it

Mario Varchmin - thanks for sharing that!

Andreas Killer commented on Aug 16, 2010

When rounding 0.5 equals 0... and what to do about it

Both versions are wrong.

The version from Marcus Foerster don't work, because checkDbl isn't declared nor initialized anywhere.

And both versions are wrong in the case of negativ numbers, -1.5 should be rounded to -2 if dec=0 not -1.

Andreas Killer

Static Function Round(ByVal Value As Variant, _

Optional ByVal Places As Integer = 0, _

Optional ByVal UpOrDown As Integer = 0) As Variant

Dim I As Long

Dim Pot10(-28 To 28) As Variant

If I = 0 Then

For I = LBound(Pot10) To UBound(Pot10)

Pot10(I) = CDec(10 ^ I)

Next I

End If

On Error Resume Next

If Value > 0 Then

Round = Int(Value * Pot10(Places) + 0.5) * Pot10(-Places)

Else

Round = -Int(-Value * Pot10(Places) + 0.5) * Pot10(-Places)

End If

If UpOrDown <> 0 Then

If Value < 0 Then UpOrDown = -UpOrDown

If UpOrDown > 0 Then

If Value > Round Then Round = Round + Pot10(-Places)

Else

If Value < Round Then Round = Round - Pot10(-Places)

End If

End If

If Err.Number Then Round = Value

On Error GoTo 0

End Function

Marcus MF Foerster commented on May 17, 2010

When rounding 0.5 equals 0... and what to do about it

Your father is a wise man, Mario

Mario Varchmin commented on May 17, 2010

When rounding 0.5 equals 0... and what to do about it

My father taught me this one twenty years ago:

Function roundCommercially(inputDbl As Double, dec As Integer) As Double

roundCommercially=Int(inputDbl * (10 ^ dec) + 0.5) / (10 ^ dec)

End Function

It requires less typing and disk space, so it carries a lower CO2 footprint. Think green!!! ;-)