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.

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.

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.

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

**Comprehensive VBA Guide** Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out *Mastering VBA for Office 2010* today!

One of the settings you can make in Excel is to specify a user's name. This name is accessible through macros, and can ...

Discover MoreOpen a workbook, look at the data, start to close the workbook, and you are asked if you want to save your changes. What ...

Discover MoreHave you ever recalculated a worksheet, only to notice that not everything calculated as it should? Here's a way you can ...

Discover More**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

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

Num = 2.45

MsgBox Format(Num, "0.0")

2016-05-17 08:51:20

Tom Bates

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

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

2016-05-16 13:47:42

Rick H.

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

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.

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

2016-05-14 05:54:52

Rick Rothstein

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.

**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

Copyright © 2017 Sharon Parq Associates, Inc.

## Comments