URGENT... Rounding

[brad mcculloch]

Hi Rica,

There's a couple of things here.

Firstly, the correct algorithm to round off numbers is to
take any remainders of five to the nearest EVEN number, so
the "correct" result of your rounding is 0.68.
Systematically rounding values of .5 either up or down
would create a bias.

Secondly, Excel when you pass the value 1.25 as a double
to a function in Excel it comes through as
1.249999999982. This sort of this is not unusual and is
often the machanism used to generate "random" numbers in a
computer. I've made the assumption here that
within .00001 of 0.5 is 0.5, and within 0.000001 of 0.05
is 0.05, etc. You may need to tweak this.

Regards,

B

Public Function Rounder(ByVal dblInValue As Double, ByVal
intNumPlaces As Integer) As Double
' this function rounds the inValue to the number of places
specified
' but intelligently rounding values of 5 to the nearest
even number

Dim dblWorkingValue As Double
Dim intTruncatedValue As Integer
Dim dblRemainder As Double


dblWorkingValue = dblInValue * (10 ^ intNumPlaces)
intTruncatedValue = dblWorkingValue \ 1
dblRemainder = dblWorkingValue - intTruncatedValue

Select Case dblRemainder
Case Is <= 0.49999
Rounder = intTruncatedValue / (10 ^ intNumPlaces)
Case Is >= 0.50001
Rounder = (intTruncatedValue + 1) / (10 ^ intNumPlaces)
Case Else ' the value is really 0.5
If intTruncatedValue Mod 2 = 0 Then 'we have an even
integer component (round down)
Rounder = intTruncatedValue / (10 ^ intNumPlaces)
Else ' we have an odd integer component (round up)
Rounder = (intTruncatedValue + 1) / (10 ^
intNumPlaces)
End If
End Select

End Function