Loading

Got a version of Excel that uses the
menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)?
**This site is for you!** If you
use a later version of Excel, visit
our *ExcelTips* site focusing on the ribbon interface.

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.

Learn more about Allen...

ExcelTips FAQ

ExcelTips Resources

Ask an Excel Question

Make a Comment

Free Business Forms

Free Calendars

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.

*ExcelTips* is your source for cost-effective Microsoft Excel training. This tip (2835) applies to Microsoft Excel 97, 2000, 2002, and 2003.

*Related Tips:*

**Create Custom Apps with VBA!** Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out *Mastering VBA for Office 2013* today!

Thanks for the tip. It works for numbers with one decimal place, with the decimal being .5. I am trying to get a more general IF statement that works with all numbers, when I specify the number of decimal places. For instance, if I want to round the numbers 7.485003 and 7.485 to 2 decimal places, the ROUND function will give me 7.49 in both instances, but according to E29 I should get 7.49 in the first case, and 7.48 in the second.

Thanks!

Thanks!

@ Enrique La Motta

The statement above is incorrect, there is no need to minus the INT first. The statement:

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

works correctly using your 8.5, 10.5, 12.5 test data.

The statement above is incorrect, there is no need to minus the INT first. The statement:

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

works correctly using your 8.5, 10.5, 12.5 test data.

I tried it with 8.5, 10.5, 12.5, which should have been rounded to 8, 10, 12 (even numbers), but instead I got 9, 11, 13.

Please explain it, because I need an IF statement to follow the ASTM E-29 standard.

Thanks,

Enrique

Please explain it, because I need an IF statement to follow the ASTM E-29 standard.

Thanks,

Enrique