Rounding as values are entered into a cell .5 up/down rules

[teachman]

Hello,

I was taught that the rounding rule is if the number to the left of the decimal point is even and the number to the right of the decimal point is <=.5 then round to the closest even whole number. So, 6.499999999 rounds to 6; 6.5 rounds to 6; 6.5000000001 rounds to 7; 7.49999999 rounds to 7; 7.50000001 rounds to 8; and 7.5 rounds to 8.

Excel does not seem to use this rule set. When I set a cell to a format of an integer and enter a number with one decimal point Excel will round to the the closest whole number. But when the number entered is x.5 Excel rounds to the absolute value of x+1 no matter what the sign of the number entered. This violates the rounding rules that I was taught. For example: 6.5 should round to 6, but Excel rounds to 7. -6.5 should round to -6, but Excel rounds to -7.

I would like to know if there is some setting or option in Excel 2010 that can be used to modify the default rounding rule?

Note, I am talking about just straight forward data entry. I can't use any of the rounding functions that have been discussed in the many, many messages about this that I have read, both on Mr. Excel and on other Excel forums that I won't mention. Mr. Excel is the BEST.

The only modification I make to the default General format is to set the cell to the number format with 0 decimal points. I also have validations set to limit the range of the value entered. I.e. Whole number between 0 and 140, etc.

Is there something I can do to force the 'correct' rounding rule?

Thanks,

George

 

[Joe4]

Is there something I can do to force the 'correct' rounding rule?

It isn't that one is more "correct" than the other, there are just different concepts, and they sometimes depend on what you are using it for.

The rounding that Excel does is the most common type of rounding found. The type of rounding that you are talking about is known as "Banker's Rounding". So while it is probably quite prevalent in the financial industries, it isn't quite some common in other areas.

There is no setting or inherent function in Excel that will do Banker's Rounding, but you can create a User Defined Function to do it. The following article talks about Banker's Rounding, and has the VBA code you need to do this (called BRound): How To Implement Custom Rounding Procedures

 

[WaxonWaxov]

To do it with a formula, presuming your given value is in cell A1 the formula is.....

=A1+((IF((MOD((A1*100),1))>=0.5,1-(MOD((A1*100),1)),-(MOD((A1*100),1))))/100)

Explanation:

First: multiply A1 by 100. This moves the cents amount to the left of the decimal point.

Second: get the MOD 1 of the value. Everything after the decimal point after multiplying my 100

Third: if the result of step two is equal to or greater than .5 get the value of 1 - the value you got in step Two. If it's not, you need negative of the value from step Two.

Fourth: Add the value from step Three to the original value.

Using this formula...

1.43500000 becomes 1.44
1.43499000 becomes 1.43

CAUTION: Make sure your business wants to round UP then the third decimal is equal to or greater than 5. If they do not the formula is =FLOOR(A1,0.01)