September 15, 2005

Rounding to Death

I have had a chance to test my latest file in ADT 2004. When using a Property Data Format to perform rounding, the Up and Down options work as expected when the Round Off Value is set to any of the values I tested, other than 0. That advice applies to 2004, 2005 and 2006.

When using the Nearest option, the results for 2004 are different than for 20054 and 2006. In 2004, the Nearest option appears to work correctly only when the Round Off value is set to 0. You can then set the Precision value and the rounding will take place at the last digit. This limits the Nearest option to rounding to the nearest integer, tenth, hundredth, thousandth...or hundred millionth. If you need to round to the nearest five, nearest 100 or nearest half, you will need to use a formula property or upgrade to 2005 or 2006. For other Round Off Values, positive numbers are always rounded down and negative numbers are always rounded up [closer to zero in both cases].

I also found that there were some numbers that did not round correctly even though the method appears to work reliably for all of the other cases tested. Obviously, I did not test every possible number, so this is not an exhaustive listing, but would be good to keep in mind if you use rounding for critical data.

2.0075, when the Round Off Value is 0.001, rounded nearest incorrectly for both the PDF method and my formula. That was the only tested number in which my formula failed. Perhaps there is some issue with how that number is represented in binary that causes the incorrect rounding. [Note that 7.5, with a Round Off Value of 1, works just fine for both methods, so it does not always happen when rounding a "7" and the next digit is "5".]

-2.0075 and -2.0025 both rounded incorrectly when using the PDF method with Round Off Value set to 0 and the Precision set to 0.001.

No comments: