by Allen Wyatt
(last updated May 28, 2013)
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:
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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2835) applies to Microsoft Excel , 8, , 10, and .
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
You can freeze information in rows or columns using one of the built-in features of Excel. As you move up or down in the ...Discover More
One of the many pieces of information that Excel keeps track of is your name. If you want to change your name for Excel's ...Discover More
Excel can recalculate your worksheets either automatically or manually. The default is to calculate them automatically, ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."