Written by Allen Wyatt (last updated March 31, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
A statement made by an ExcelTips subscriber (Chuck) in answer to a Help Wanted question provoked some interesting comments from many other ExcelTips subscribers. Chuck made the statement that, statistically, the value .5 should be round up half the time and down the other half of the time because it is exactly in the middle of two whole values. The analogy was provided that if a tennis ball was balanced on a net, statistically the ball should fall left half of the time and right the other half of the time.
It seems that there are some strong feelings about such statements, even among other statisticians. (All disciplines seem to have their various religious wars where feelings run high.) As one correspondent mentioned, this is "the old 'fences vs. fence posts' problem in counting intervals between numbers." The argument is where something will "fall" when it is situated right on a fencepost. The problem with the tennis ball and net analogy (or fences and fence posts) is that the net in the middle of the court is not the only precise dividing line.
For instance, let's say that the left end of a tennis court has a line marked "4.0" and the other end has a line marked "5.0." This means that the net is marked "4.5." While a tennis ball could balance on the 4.5 mark and fall either way, theoretically the ball could also balance on the line at either end of the court (4.0 and 5.0) and fall either way off of them, as well.
One correspondent expressed the feeling that rounding .5 either up or down (half one way and half the other) is inappropriate because it introduces bias into the data. Consider the situation where you are dealing with one digit to the right of the decimal point: You have numbers 7.0, 7.1, 7.2, etc., all the way through 7.9. When rounding these figures, five values would always round down (7.0 through 7.4), one value could round either way (7.5), and four values would always round up (7.6 through 7.9). In other words, over time 5.5 values would round down and 4.5 values would round up. In a true even application of statistical probability, 5 values should round down and 5 up, but the "waffling" of the center value (7.5) makes a bias in favor of rounding down and against rounding up.
So, which theory of rounding is correct? Should 7.5 round up half the time and down half the time, or should it always round up? Microsoft has obviously made its mind up, as it always round 7.5 up (the tennis ball always falls to the right for positive values and to the left for negative values). Does Microsoft's decision mean that always rounding .5 up is correct? Your position in the rounding religious war will determine your answer.
Well, perhaps another data point will help. It 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 an exact fractional value of .5 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 Excel's 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.
Any discussion of rounding, of course, needs to assume that you are rounding raw values, not previously rounded values. For instance, if a raw value is 14.46 and you round it to 14.5, it would be improper to later round the 14.5 to 15. The correct procedure would be to examine the original 14.46, which should round down, to 14. Thus, you should always use ROUND as one of your last steps in working with numbers, not as one of the first. 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 (2829) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Need to round a value by a power of 10? You can do it by using the ROUND function as described in this tip.
Discover MoreNeed to round the time in a cell to a certain value? There are a couple of ways you can do this with a formula.
Discover MoreThe primary method of rounding values is to use the ROUND function in your formulas. Here's an introduction to this ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-07-17 07:04:00
Maarten Boers
Hi Allen,
Thanks for the clear explanation. I think Excel should provide a function that does this without the fairly complex formula you described.
I had a bit of trouble understanding it, until I noticed a typo:
The formula should read:
=IF(A1-INT(A1)=0.5,...
rather than:
=IF(A1-INT(A1)-0.5,...
Just before posting I noticed the discussion below, so the error has been corrected in ways similar to the above.
But you haven't corrected the formula yet...
Cheers,
M
2020-04-01 11:46:55
Rick H.
I took a moment to figure out why the noted formula doesn't work for me. I thought it was my version of Excel.
The formula should be "=IF(A1-INT(A1)-0.5=0,EVEN(ROUNDDOWN(A1,0)),ROUND(A1,0))"
For two decimal places: "=IF(A1*100-INT(A1*100)-0.5=0,EVEN(ROUNDDOWN(A1*100,0))/100,ROUND(A1,2))"
Dave - It seems to work fine with negative values. -0.5 rounds to zero, -1.5 rounds to -2.
2020-03-31 12:50:35
Chester Hood
I was a little bit surprised but pleased to see the reference to the ASTM and ANSI standards in this tip. I am not sure when either of these standards were established, however, the method of rounding to an even digit was exactly what was taught in freshman engineering courses in the late 1960s and early 1970s. I am not surprised that the exercise with 25,000 random numbers so clearly supported this method to avoid introducing bias due to rounding.
Our calculation tool at that time was a slide rule and we were taught about significant digits and orders of magnitude. Four function calculators were introduced in this era and cost over $100, so almost all of us had a Keuffel & Esser Log Log Duplex Decitrig (recommended) or a Pickett. We also learned to do "back of the envelope" calculations (get the decimal point in the right place) which served me well throughout my career and I taught this technique to my children.
2020-03-31 12:07:28
Dave
I've always heard this method referred to as military rounding.
I wonder whether the formula works seamlessly for numbers as we transition to negative numbers...
2017-11-02 12:35:17
Ron MVP
Thanks for the update.
2017-11-02 11:28:00
Chris C
Interesting discussion. One thought I had regarding the tennis ball where half the time it falls on one side of the net or the other is that due to imperfections of the parts, if the ball were placed to slightly favor one side it will still fall the other way once in while. Of course this will never happen in rounding formulas, even though the point of rounding is to accept that numbers involved have flaws that remove the significance of some digits. Another thought is proven by Alan's analysis that more decimal places lower the frequency of the perfect center point. This means that once the value reaches x.5, if the precision were increased it would always show to be greater than x.5 but never less than x.50... Therefore it is usually safe to ignore any concern of which way the .5 value gets rounded and drags the whole group of numbers with it. Regardless of how it gets rounded the result will still be within the margin of error based on the concept of significance. The assumption of course is that all the numbers are raw data and not from a previously rounded input.
2017-11-01 09:58:39
Allen
Ron,
I just now moved it from the menu site to the ribbon site. I also updated the tip (above) with info that was in the older article you mentioned.
-Allen
2017-11-01 04:03:05
Ron MVP
Hi Allen:
Could you migrate this tip to the Ribbon site, so it does not get lost when you eventually retire the menu tips.
There used to be a second article to expand on this one. But I can't find it any more to share with other users (I have a copy in my archive disks)
http://excel.tips.net/T002835_Rounding_Religious_Wars_Take_Two.html
2016-05-19 16:26:30
Tom Bates
Thanks Rick Rothstein for that tip. That will come in very handy!
2016-05-18 05:32:08
Rick Rothstein
Tom Bates gets what I was trying to say in my first post. By the way Tom, every function in VB that uses rounding internally (such as CLng) uses what many call "Banker's Rounding" (another name for the rounding to even numbers method) with one exception... the Format function which is the only VB function that performs what I call normal rounding (5s always round up). So you can use Format to perform your rounding in VB without calling out to the worksheet's ROUND function. For example,
Num = 2.45
MsgBox Format(Num, "0.0")
2016-05-17 08:51:20
Tom Bates
Consider this series:
tenths =ROUND() CLng()
1.2 1.0 1
1.3 1.0 1
1.4 1.0 1
1.5 2.0 2
1.6 2.0 2
1.7 2.0 2
1.8 2.0 2
1.9 2.0 2
2.0 2.0 2
2.1 2.0 2
2.2 2.0 2
2.3 2.0 2
2.4 2.0 2
2.5 3.0 2
2.6 3.0 3
2.7 3.0 3
2.8 3.0 3
2.9 3.0 3
3.0 3.0 3
3.1 3.0 3
3.2 3.0 3
3.3 3.0 3
3.4 3.0 3
3.5 4.0 4
3.6 4.0 4
3.7 4.0 4
3.8 4.0 4
From this list, we see that there are 10 2.0 values in column 2, and 10 3.0 values, because the ROUND function is correctly rounding .5 up.
In contrast, the CLng function rounds incorrectly, because the results are obviously very skewed towards even numbers: we see more 2's (11) than 3's (9).
Thanks to stas for pointing that out; henceforth, I will be using the spreadsheet function ROUND rather than allowing VBA to perform the rounding incorrectly.
2016-05-17 08:30:20
Tom Bates
The fact that rounding 7.0 yields 7.0 does not mean that it has not been rounded. It simply means that the result of rounding yields the same number.
If you consider rounding to a multiple of 10, 70 *remains* 70, while 71 *becomes* 70. It may be easier to recognize that 70 to 79 are "the seventies"; 70 to 74 are the lower half, 75 to 79 the upper half, exactly even. 80 is clearly not part of the 70's.
So .49999999999999 rounds down, .5 rounds up. Always (mathematically speaking).
2016-05-16 18:42:22
Scott Renz
In the example 7.0, 7.1, 7.2, etc., 7.0 does not get rounded. It keeps its same value. So, only the 7.1, 7.2, 7.3, and 7.4 get rounded down.
2016-05-16 13:47:42
Rick H.
Wow. Did I make this more complicated than necessary?
Trying to stay away from VBA, I worked up this formula to round cell C16 to two places and rounding even when the third and final digit was a 5:
=IF(AND(C16*1000=INT(C16*1000),C16*200=INT(C16*200),ISEVEN(C16*100)),INT(C16*50+0.5)*0.02,ROUND(C16,2))
If it matters, I'm still using Excel 2000, Win7.
2016-05-16 10:15:29
stas
The more striking is a difference between "round" worksheet function and a "round" in VBA. Worksheet function always round 0.5 up. VBA "round" rounds 0.5 to the even value.
worksheet round VBA round
0.5 1 0
1.5 2 2
2.5 3 2
3.5 4 4
4.5 5 4
5.5 6 6
6.5 7 6
7.5 8 8
8.5 9 8
9.5 10 10
10.5 11 10
11.5 12 12
12.5 13 12
13.5 14 14
14.5 15 14
15.5 16 16
16.5 17 16
VBA round was from
For i = 1 To 17
Cells(i, 3) = Round(Cells(i, 1), 0)
Next i
I Use Excel 2013 with Win7.
2016-05-16 09:24:33
Rick H.
Why are we including 7.0 in the series of values without including 8.0?
7.0 is not rounded down. The decimal is simply dropped. 7.0 = 7
This leaves 7.1 through 7.4 (four values) always rounding down, and 7.6 through 7.9 (four values) always rounding up.
Are the values in this scenario only between 7.0 and 8.0?
I was taught to round even. This takes care of skewing the data, you don't have to keep track of which direction the last value was rounded, and subsequent halving isn't left with another .5 to deal with.
If our scenario includes values from a minimum of 0 to a maximum of 10, and we always round the .5 values up, the average is skewed. Rounding even corrects this issue.
Raw values: .5+1.5+...8.5+9.5=50, Avg.=5
Rounding up: 1+2+...9+10=55, Avg.=5.5 which rounds to 6.
Rounding even: 0+2+...8+10=50; Avg.=5
2016-05-14 08:17:15
Roger
I was just discussing this the other day with a client. I told her that I used to teach children in Australia and this is how I explained it to them.... "My uncle used to live in England and he wanted to swim to France across the English Channel. He was VERY keen to do this. So off he went and swam and swam and swam. When he got exactly half way, he was so tired and knew he could not make it - so he swam back to England. Hands flew up and asked why he did not go on because it was the same distance going to France as swimming back. I said, 'That is why we round up rather than down'."
2016-05-14 05:54:52
Rick Rothstein
No question about it, .5 should always be rounded up... and the reason is simplicity itself. Let's use the 7.5 for our example. The lower interval is 7.0 to 7.4 and the upper interval is 7.5 to 7.9 (8 belongs to the next interval and does not figure into the decision). Those two intervals contain exactly the same number of values... the lower interval rounds down and the upper interval rounds up... and since 7.5 belongs to the upper interval, it should always round up just like the other members of its interval. Simple, right?
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments