Rounding

[kirkm]

In Excel .5 is always rounded up when the decimal places are reduced,
ie. 3.55 becomes 3.6, 3.65 becomes 3.7 etc. I need a formula that will
"throw .5 to the odd" so that 3.55 would become 3.5, 3.65 would become
3.7 etc. I would appreciate any help on this.

Thanks - Kirk

[bi...@yahoo.com]

Hi,
Assuming your numbers are in column A, try this formula in column B

=IF(A1-FLOOR(A1,1)<=0.55,FLOOR(A1*10,1)/10,ROUND(A1,2))

cheers,
julian

[Jerry W. Lewis]

Since you posted in the Programming newsgroup, I presume that you want a VBA
solution. However, you should be aware that the VBA Round function does not
round exactly 5 up, it rounds 5 to an even number (per the ASTM standard).

The difficulty with writing an alternate rounding procedure is that the
rounding direction is determined by a decimal condition, but Excel and VBA
(as well as almost all other software) does binary math. Unless you are very
careful, code that seems to work will produce unexpected results with some
inputs due to binary approximations. Note that the VBA Round function was
NOT carefully implemented in this regard.

The following code has not been extensively tested, but should work well due
to its similarity to posted ASTM rounding code
http://groups.google.com/group/microsoft.public.excel.charting/msg/107fce6145b70d69
that has been more extensively tested.

Function RoundOdd(number As Double, _
Optional num_digits As Integer = 0) As Double
' round exactly 5 to odd (vs. ASTM standard that rounds 5 to even)
' requires Excel 2000 or later
Dim x
' buffer against binary approximations by rounding to an integer
' CDbl(CStr()) ensures that we get the primary binary _
representation the decimal display
RoundOdd = Abs(CDbl(CStr(number * 10 ^ num_digits)))
x = Fix(RoundOdd)
If RoundOdd = x + 0.5 Then
If x / 2 = Fix(x / 2) Then
' even
RoundOdd = (x + 1) * Sgn(number) / 10 ^ num_digits
Else
' odd
RoundOdd = x * Sgn(number) / 10 ^ num_digits
End If
Else
RoundOdd = Round(RoundOdd, 0) * Sgn(number) / 10 ^ num_digits
End If
End Function

Jerry