Rounding Religious Wars, Take Two

Last week I featured a tip about controversy among statisticians and others concerning how a value, exactly in the middle of two other values, should be rounded. For instance, between the values 7 and 8, how should the value 7.5 be rounded? For some folks, the value should always be rounded up, but for others it should always be rounded to the nearest even number. In other words, 7.5 would be rounded to 8 by both camps, but 8.5 would be rounded to 9 by one camp and 8 by the other camp.

I incorrectly stated, last week, that rounding an exact center value to the nearest even number would introduce bias into the results. Specifically I said that given the numbers 7.0, 7.1, 7.2, etc., all the way through 7.9, that if you round to the nearest even you will get five values going down (7.0 through 7.4), four values going up (7.6 through 7.9), and one waffling either way (7.5). This is incorrect because the value 7.0 is not rounded at all. This means that you really get four values going down (7.1 through 7.4), four going up (7.6 through 7.9), and the center value should be able to go either way to keep the two directions free of bias.

It also appears that there is an ANSI standard on this whole issue. One subscriber indicated that he had always followed the standards ASTM E29 and ANSI Z25.1, both of which specify that a fractional value of .5, exactly, should be rounded to the nearest number ending in an even digit. If you need to do this type of rounding, then the proper formula to use is this:

=IF(A1-INT(A1)-0.5,EVEN(ROUNDDOWN(A1,0)),ROUND(A1,0))

To see how this can affect the outcome of rounding, I generated a series of 25,000 random numbers between 1 and 100, where each result had up to two decimal places. I then rounded the values to a whole value using the regular ROUND function in one column, and in the next column I rounded the numbers using the above formula. I then summed each column to see which method of rounding produced results closer to the original sum. In my test, the results were over 50% closer to original sum by using the above formula rather than Excels ROUND function alone.

I then generated 25,000 random numbers with up to three decimal places, and the results were the same--the formula was closer than a generic ROUND. The same held true with numbers with four and five decimal places, as well.

One thing I did notice in my testing was that in the first set of test data (random numbers with up to two decimal places) there were 234 values that exactly matched the criteria of being exactly .5 (and thus eligible for rounding up or down). In the list with three decimal places the number of matches dropped to 14 values, with four decimal places it was 2 values, and with five decimal places it was 0 values. It stands to reason that the fewer values there are that meet the criteria of ending in .5, the less necessity there is to apply the "round up or down" logic. Thus, the rounding formula, above, loses its effectiveness when you start dealing with numbers having four, five, six, or more digits to the right of the decimal point by virtue of the fact that there are exact-center matches.

There is one thing that was mentioned last week that still has value--make sure that you do your rounding as one of your final steps. You should always work with "raw" numbers as much as possible. This means that when using aggregative functions, such as SUM or AVERAGE, you should not apply them to values that have already been rounded. Instead, you should SUM or AVERAGE the raw values, and then do the rounding on the SUM or AVERAGE. You will get more precise results by remembering this tip.