Rounding Religious Wars

This could be much improved, perhaps with type and error checking, but if the vba rounding method (Banking) works better for you:-

Function BankRound(x, Optional y = 0)
BankRound = Round(x, y)
End Function

=BankRound(A1) rounds to whole number
=BankRound(A1, 3) rounds to 3 decimal places

Thanks for this, very enlightening!

I agree with Allen's take on ASTM / ANSI rounding method, sometimes also known as "military rounding". I used it often.

Let me add a related discussion topic: Conditional formatting of formatted numbers.

When preparing reports, it's common to apply conditional formats to highlight certain results. Unfortunately, this often leads to misleading results.

For example, suppose you want to shade all cells with values equal to to greater than 4 with red. If you have a value of 3.96 and it's formatted to one decimal place "#.#", it will appear as 4.0 but it won't be shaded red. This can happen with both normal conditional formatting and macro-applied formatting.

Since I did a lot of executive reporting, and because executives can get really fussy when their results are near an edge, I often adopted a modified rule so that numbers got shaded per how they appeared. Thus, 3.96 would appear as 4.0 and it would be shaded red. That stopped a lot of pedantic arguments.

Going a step further, I had two ways to do this. I could either round off the value so that 3.96 got changed to 4.0. Or I could tweak the shading so that red applied to all values greater than 3.95000. Which one I applied depended on the situation.

As a final step, I usually rounded reported values. A calculation might produce a result of 3.96365438. That's a hard number to remember if transcribing and it implies too much precision. So I would round it to 3.96 or 4.0.

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

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.

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.

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...

Thanks for the update.

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.

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

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

Thanks Rick Rothstein for that tip. That will come in very handy!

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")

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.

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).

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.

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.

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.

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

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'."

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?