How To Implement Custom Rounding Procedures

There are a number of different rounding algorithms available in Microsoft products. Rounding algorithms range from Arithmetic Rounding in Excel’s Worksheet Round() function to Banker’s Rounding in the CInt(), CLng(), and Round() functions in Visual Basic for Applications.

The Round() function is not implemented in a consistent fashion among different Microsoft products for historical reasons.  

I had an issue where in my .net code the number would be round down and in sql server the number would be round up. so I set out to write my own algorithm.

Friend Function Round(ByVal val As Decimal, ByVal Count As Integer) As Decimal
        Dim retVal As Decimal = 0.0
        Dim value As String = val
        If Not String.IsNullOrEmpty(value) Then
            Dim lenghtOfString As Integer = value.Length
            Dim IndexOfPoint As Integer = value.IndexOf(“.”)
            If IndexOfPoint > 0 Then
                Dim ValueBeforePoint As String = value.Substring(0, IndexOfPoint)
                Dim ValueAfterPoint As String = value.Remove(0, IndexOfPoint + 1)
                Dim LenghtOfAfterPoint As Integer = ValueAfterPoint.Length – 1
                Dim newValue As Integer
                If LenghtOfAfterPoint + 1 <= Count Then
                    retVal = ValueBeforePoint & “.” & ValueAfterPoint
                Else
                    For i As Integer = LenghtOfAfterPoint To 0 Step -1
                        Dim strchar As String = ValueAfterPoint(i)
                        If strchar >= “5″ Then
                            ‘Increase next value
                            Dim NextValue As String = ValueAfterPoint(i – 1)
                            newValue = CInt(NextValue) + 1
                            Dim indexOfNextVal As Integer = ValueAfterPoint.LastIndexOf(NextValue)
                            If indexOfNextVal = ValueAfterPoint.Length – 1 Then
                                indexOfNextVal = indexOfNextVal – 1
                            End If
                            ValueAfterPoint = ValueAfterPoint.Remove(indexOfNextVal, 2)
                            ValueAfterPoint = ValueAfterPoint.Insert(indexOfNextVal, newValue)
                        Else
                            ‘decrease next values
                            Dim NextValue As String = ValueAfterPoint(i – 1)
                            newValue = CInt(NextValue)
                            Dim indexOfNextVal As Integer = ValueAfterPoint.LastIndexOf(NextValue)
                            If indexOfNextVal = ValueAfterPoint.Length – 1 Then
                                indexOfNextVal = indexOfNextVal – 1
                            End If
                            ValueAfterPoint = ValueAfterPoint.Remove(indexOfNextVal, 2)
                            ValueAfterPoint = ValueAfterPoint.Insert(indexOfNextVal, newValue)
                        End If
                        If ValueAfterPoint.Length = Count Then
                            retVal = ValueBeforePoint & “.” & ValueAfterPoint
                            Exit For
                        End If
                    Next
                End If
            Else
                retVal = val
            End If
        End If
        Return retVal
    End Function