Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding
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 kirkm wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding
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/micro...7fce6145b70d69 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 "kirkm" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ROUNDING FORMULA =ROUND(B12/$B$10,1) ROUNDING TO HIGH | Excel Worksheet Functions | |||
I need a formula with rounding up & rounding down to the nearest . | Excel Worksheet Functions | |||
Rounding | Excel Discussion (Misc queries) | |||
Rounding | Excel Discussion (Misc queries) | |||
Worksheet rounding vs VBA rounding | Excel Programming |