Section 8.1. Rounding Numbers
8.1. Rounding Numbers
Most people don't devote much thought to rounding , the process by which you adjust fractional numbers so they're less precise but more manageable. For example, rounding transforms the unwieldy number 1.984323125 to 2. In Excel, there are two ways to use rounding:
-
Modify the number format of the cell . With this method, Excel rounds the displayed value, but it doesn't change the underlying value. The advantage to this approach is that you can use the value in other calculations without losing any precision. When Excel rounds your numbers using this method, it simply rounds to the last displayed digit (rounding up if the next digit is 5 or greater). For example, if you tell Excel to show the number 3.145 using two decimal places, Excel displays the rounded value of 3.15. (Cell formatting is described in Chapter 4.)
-
Use a rounding function . This approach gives you more control. For example, you can round a number before you use it in another calculation, or you can round numbers to a multiple you choose, like 500 or 1,000. The drawback is that when you use a rounding function, you may lose precision. This problem doesn't occur when you use the format rounding because Excel simply tweaks the way the number is displayed.
With classic overkill, Microsoft includes no fewer than 10 functions designed specifically for rounding numbers, from the basic ROUND( ), to the more flexible MROUND( ), and to the quirky EVEN( ) and ODD( ).
8.1.1. ROUND( ), ROUNDDOWN( ), ROUNDUP( ): Rounding Numbers
The most basic (and most commonly used) of Excel's rounding functions is ROUND( ), which rounds a numeric value to whatever level of precision you choose. The ROUND( ) function needs two arguments: the actual number you want to round, and the number of digits you want to appear to the right of the decimal point. Here's what it looks like:
ROUND(number_to_round, number_of_digits)
For example, the following formula rounds the number 3.987 to two decimal places. The result is 3.99.
=ROUND(3.987, 2)
If you specify 0 for the number of digits, Excel rounds to the nearest whole number. Interestingly, you can also round to the nearest 10, 100, 1000, and so on by using negative numbers for the second argument. For example, if you use -2 for the number of digits, Excel rounds two digits to the left of the decimal place, which means your number gets rounded to the nearest 100.
Here's an example:
=ROUND(34655.7, -2)
This formula produces a result of 34,700.
Note: The ROUND( ) function always rounds the positive values 1 through 4 down ; 5 through 9 get rounded up . If you round 1.5 to the nearest whole number, for instance, the result is 2. When dealing with negative numbers, Excel rounds the digits 5 through 9 down (toward the larger negative value). Similarly, -1 through -4 get rounded up. For example, -1.5 gets rounded to -2, while -1.4 gets rounded up to -1.
The ROUNDDOWN( ) and ROUNDUP( ) functions work similarly to ROUND( ). Like ROUND( ), they take two arguments: the number you want to round, and the number of decimal places you want the final, rounded number to use. The difference is that ROUNDDOWN( ) always rounds numbers down, while ROUNDUP( ) always rounds numbers up.
For example, the result of ROUNDUP(1.1, 0) is 2, even though 1.1 is only slightly above 1. Similarly, the result of ROUNDDOWN(1.9, 0) is 1, even though 1.9 is almost 2. The only time that ROUNDUP( ) and ROUNDDOWN( ) won't change a number is if it's already rounded to the appropriate precision. For example, the result of ROUNDUP(2, 0) and ROUNDDOWN(2, 0) is the same: 2.
When most people learn about ROUNDUP( ) and ROUNDDOWN( ) they often wonder why anyone would want to use a rounding function that's less precise than ol' reliable ROUND( ). The answer, not surprisingly, has to do with making more money. If you're selling discounted Beanie Babies, say, you might set the price at 60% of the original list price. However, this formula produces prices like $8.43423411 that you need to round to the nearest penny. Rather than rounding down (and giving up your fractional cents ), you can use ROUNDUP( ) to make sure the price is always rounded up, ensuring that you keep every last penny and even collect a few extra. Never say Microsoft didn't try to help you save money.
8.1.2. MROUND( ), CEILING( ), and FLOOR( ): More Rounding Functions
While you might not necessarily think of the ROUND( ) function as allowing you to round a number to any multiple of a power of 10 you'd like, that's actually what's going on under the hood. But in some cases, you might want to round your numbers to something that isn't a power of 10. For example, you might want to round a number to the nearest multiple of 5 or 50 or 100.
In these cases, you need to use Excel's MROUND( ) function, which is part of the Analysis ToolPak add-in (Section 7.2.4). MROUND( ) takes two arguments: the number you want to round, and the multiple you want to use:
MROUND(number_to_round, multiple)
Here it is in action, as the following formula rounds the number 653 to the nearest multiple of 5. The result is 655.
=MROUND(653, 5)
CEILING( ) and FLOOR( ) work like MROUND( ), except they let you control whether the rounding goes up (in which case you'd use CEILING( )) or down (FLOOR( )). In other words, the CEILING( ) function is really a combination of MROUND( ) and ROUNDUP( ). The FLOOR( ) function, meanwhile, is a combination of MROUND( ) and ROUNDDOWN( ).
Consider the following formula. It rounds the number 653 to the nearest multiple of 5, but, because it uses the FLOOR( ) function, it rounds downward . The result is 650:
=FLOOR(653, 5)
Note: The rounding functions don't always treat negative numbers the way you'd expect. ROUNDUP( ) and ROUNDDOWN( ) ignore the minus sign entirely. That means if you use ROUNDUP( ) to round to no decimal places with -4.5, you'll end up with -5. The result is a larger negative number. MROUND( ) exhibits the same behavior (positive and negative numbers are treated identically), with one catch. When rounding negative numbers with MROUND( ), you must supply a negative number for the multiple argument.
Compare that with the following formula, which produces a result of 700. This formula uses the CEILING function, which means it always rounds up, unless the number is already rounded to the appropriate multiple, of course.
=CEILING(652, 50)
The CEILING( ) function can be handy if you need to calculate an item's price in more than one currency. Consider a U.S.-based company that's trying to figure out how to price its products in Canada (Figure 8-1). The company would probably start by setting the price in U.S. dollars, and then use a currency conversion ratio to produce a set of Canadian prices. However, this approach produces prices that, in effect, leave a little money on the table. (Most people willing to pay $153.16 for a product are probably also willing to pay $153.99whether it's Canadian or American dollars.) Using a function like MROUND( ), the company could make sure that prices are always rounded to the nearest 25 or 50 cent multiple. That's fine, but there's a possibility that margins might be shaved too thin if you round prices downso the CEILING( ) function is a better choice. And if you want to get particularly fancy, why not use CEILING( ) to round up to the nearest dollar and then subtract a single cent, giving attention- grabbing prices that always end with .99?
|
8.1.3. INT( ) and TRUNC( ): Chopping Off Non-Whole Numbers
INT( ) and TRUNC( ) are two functions that simply shorten your numbers, removing extra digits without performing any rounding. INT( ) is the simplest of the two, as it always rounds to whole numbers. This means that you only need to specify the number you want rounded. The formula here, for example, works out to 2:
=INT(2.5)
You'd get the same answer for INT(2), INT(2.9), INT(2.7509630), and so on. In each case, Excel discards the decimal portion.
TRUNC( ) is similar to INT( ), except it uses a second argument specifying the number of decimal places you want to preserve . This argument is optional, and if you leave it out, TRUNC behaves exactly like INT( ):
TRUNC(number_to_round, [number_of_digits])
For the most part, TRUNC( ) behaves just like ROUNDDOWN( ) because it reduces most numbers to their nearest, lower value. The only exception is when you use TRUNC( ) on negative numbers. Whereas ROUNDDOWN( ) always rounds a number toward its smallest value (so that =ROUNDDOWN(-3.4, 0) gives you -4), TRUNC( )'s only mission is to eliminate digits regardless of what this action does to a number's value. Therefore, the following formula with TRUNC( ) works out to -3:
=TRUNC(-3.4, 0)
In real life, you'll find reasons to round numbers far more often than you have reasons to truncate them.
One of the few instances in which you might use truncation is when you want to ignore the digits of a number that you don't want, rather than change them. For example, imagine you want to start memorizing the digits of the mathematical constant pi (3.14159265 . . . ). Using the following formula, you can truncate pi to the first three decimal places so you can get started:
=TRUNC(PI( ), 3)
This formula gives you the number 3.141 (note that Excel lets you use the PI( ) function to insert the constant pi). If you used the ROUND( ) function, you would end up with 3.142 instead, because Excel would round the last digit.
You might also use TRUNC( ) to give you just the whole number portion of a value. This approach is reasonable when the decimal portion doesn't have any real meaning. For example, you might calculate that your furniture factory produces 5.6 chairs per day when you average the monthly production. Unfortunately, this still means you can only rely on having five chairs to sell at the end of the day. A similar case can be made for using the ROUNDUP( ) function. For example, you might know that your friends tend to eat 1.3 pumpkin pies each time they come for a dinner party, so you'd do best to round up and make sure you slide two pies into the oven.
8.1.4. EVEN( ) and ODD( ): Rounding Up to Even or Odd Values
EVEN( ) and ODD( ) are the last of Excel's rounding functions. These functions accept a single number and round it up to the nearest even or odd number. As simple as this sounds, these functions can cause a bit of confusion because many people assume that the functions will return the closest odd or even number. But they don't. Since the functions round up , the closest correct number may be, numerically speaking, a confusingly long ways away.
| POWER USERS' CLINIC Excel's Rounding Might Not Be Good Enough |
| Even though Excel provides 10 rounding functions, they all work in exactly the same way. They use a technique called arithmetic rounding , which always rounds the number 5 up. Although this might seem like the only option, accountants and statisticians are well aware that there are a variety of different approaches to rounding numbers, and they all have their own particular problems. The key difficulty is deciding how to round the number 5. For example, consider 1.5, which lies exactly halfway between the numbers 1 and 2. In arithmetic rounding, 1.5 is always rounded up to 2. This can bias your results if you're rounding a large group of numbers and then adding them together (since the five digits 5, 6, 7, 8, and 9 always get rounded up, versus the four digits 1, 2, 3, and 4, which always get rounded down). The best way to understand the problem is to think of the sales tax you pay on a typical restaurant bill. Sales tax is supposed to be calculated as a percentage of the total, and then rounded arithmetically. However, imagine what would happen if the sales tax were calculated separately on each item you ordered, rounded up separately, and then added. You'd pay more than if the tax were calculated from the total. So how can you avoid rounding biases? The best option is to calculate first, and round later. Never add numbers that you've already rounded. Another option is to use a different type of rounding. One example is banker's rounding, which rounds 5 up sometimes and down other times, depending on whether it's paired with an even or odd number. For example, 1.5 is rounded up to 2, but 2.5 is rounded down to 2, 3.5 is rounded up to 4, and so on. You can use banker's rounding in Excel, but Microsoft makes it available only to the VBA (Visual Basic for Applications) programming language. Translation: you need to write a custom macro or function to access it. You'll learn about how you can use banker's rounding with VBA on Section 26.3.8. In the meantime, you can learn more about the various aspects of rounding, and see what types of rounding Microsoft uses in different products in the technical support note at http://support.microsoft.com/default.aspx?scid=kb;EN-US;196652. |
To understand these quirky functions a little better, consider the following formula:
=ODD(2.6)
This formula produces the expected result: it rounds 2.6 up to the closest odd number, 3. Now consider
=ODD(3.6)
This also rounds up to the nearest odd number, which in this case is 5.
In fact, ODD( ) always rounds up, unless you begin with a whole odd number. That means that the result of the following formula is also 5, even though 3.1 is clearly much closer to 3 than 5:
=ODD(3.1)
The EVEN( ) function behaves similarly. Thus, the result of the following formula is 4:
=EVEN(2.1)
The EVEN( ) and ODD( ) functions aren't useful too often. For most people, they simply represent an interesting footnote in Excel functions.
EAN: 2147483647
Pages: 185