Rounding formula question

[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.

Someone kindly supplied this yesterday

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))


but unfortunately the formula doesn't "throw to the odd", I know that
"throwing to the even" is called 'bankers rounding' and there is no
function in Excel that supports this. Throwing to the odd is commonly
used in meteorology when working out average temperatures' eg. 12.5 +
3.8 = 16.3 when divided by 2 to give the average it results in 8.15,
when rounded to one decimal place Excel rounds this up to 8.2 when the
figure should be 8.1. It is possible to do this manually but this can
lead to errors.

Any further help appreciated.

Thanks - Kirk

 

[Bob Phillips]

More complex than I anticipated

=IF(AND(ISEVEN(ROUND((A1*10/2),0)),ROUND(MOD(A1,0.1),2)=0.05),ROUNDDOWN(A1,1
),ROUNDUP(A1,1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

a bit simpler

=IF(ROUND(MOD(A1, 0.1),2)=0.05, (INT(A1*10) + MOD(INT(A1*10), 1))/10,
ROUND(A1, 1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[bplumhoff]

Hi Bob,

If 0.85 should become 0.9 and -3.55 should give -3.5 I would suggest:

=ROUND(A1,1)-(MOD(A1*20,1)=0)*MOD(INT(ABS(A1)*10),2)/10*SIGN(A1)

Regards,
Bernd

 

[Guest]

Why are you not happy with the user defined function that I provided in your
previous thread on this topic?

http://groups.google.com/group/microsoft.public.excel.programming/msg/0d2dc7c322cf4b3f

Jerry