Rounding formula question
 

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

Rounding formula question
 

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)

"kirkm" wrote in message
...

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

Rounding formula question
 

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)

"kirkm" wrote in message
...

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

Rounding formula question
 

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

Rounding formula question
 

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/micro...2dc7c322cf4b3f

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.

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