MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Default rounding of 0.5


Posted by Chris B. on May 24, 2001 11:44 AM

How do I change the default rounding of a number like 4.5 to round down to 4 instead of round up to 5 when reducing the significant figures displayed?


Posted by Barrie Davidson on May 24, 2001 12:13 PM

Use the rounddown function.
=ROUNDDOWN(number to round down,number of digits to round to)
For your example
=ROUNDDOWN(4.5,0)

Barrie

Posted by Chris B. on May 24, 2001 2:38 PM

Thanks for that solution. However, my question should have included "but I want to round numbers higher than 4.500001 to 5, also." (One solution would, of course be to subtract 0.000001 from the result - not elegant, but it produces the desired result)

Chris B.

Posted by IML on May 24, 2001 2:53 PM

Speaking of not elegant... assuming your number is D7, you could try
=IF(D7-(INT(D7)+0.5)>0,INT(D7)+1,INT(D7))

I think there be a function in the anlysis add in that would do it prettier.

good luck

Posted by Chris B. on May 26, 2001 9:32 AM


Maybe so. Thanks for the feedback! Based on both of the above, I have another workaround that seems to work for positive numbers above 1 also - again not pretty. If the number is in A1:

=IF((A1-0.5)<=INT(A1),ROUNDDOWN(A1,0),ROUNDUP(A1,0))

Chris B.