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