Rounding formula question

K

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
 
B

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

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top