Most people don't
enough thought to
, the process by which you adjust
numbers so they're less precise but more manageable. For example, rounding transforms the unwieldy number 1.984323125 to 2. Excel has two ways to use rounding:
Modify the number format of the
. 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
digit is 5 or greater).
For example, if you tell Excel to show the number 3.145 using two decimal places, then Excel displays the rounded value of 3.15. (Cell value formatting is described in Chapter 5.)
Use a rounding function
. This approach gives you more control. For example, you can round a number
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
rounding because Excel simply tweaks the way the number's displayed.
With classic overkill, Microsoft includes no fewer than 10 functions designed
for rounding numbers, from the basic ROUND( ), to the more flexible MROUND( ), and to the quirky EVEN( ) and ODD( ).
9.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 keep to the right of the decimal point. Here's what it looks like:
For example, the following formula rounds the number 3.987 to two decimal places. The result is 3.99.
If you specify 0 for the number of digits, then Excel rounds to the nearest whole number. Interestingly, you can also round to the
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, then Excel rounds two digits to the
of the decimal place, which means your number gets rounded to the nearest 100.
Here's an example:
This formula produces a result of 34,700.
The ROUND( ) function always rounds the positive values 1 through 4
; 5 through 9 get rounded
. 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 through4 get rounded up. For example,1.5 gets rounded to2, while1.4 gets rounded up to1.
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( ) don'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
would want to use a rounding function that's
precise than ol' reliable ROUND( ). The answer, not surprisingly, has to do with making more money. If you're selling discounted Beanie Babies, say, then you might set the price at 60 percent 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
), 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.
9.1.2. MROUND( ), CEILING( ), and FLOOR( ): More Rounding Functions
While you may not
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
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. MROUND( ) takes two arguments: the number you want to round, and the multiple you want to use:
Here it is in action, as the following formula rounds the number 653 to the nearest multiple of 5. The result's 655.
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's 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
. The result is 650:
The rounding functions don't always treat negative numbers the way you'd expect. ROUNDUP( ) and ROUNDDOWN( ) ignore the minus sign entirely. If you use ROUNDUP( ) to round to no decimal places with4.5, then you'll end up with5. The result is a larger negative number. MROUND( ) exhibits the same behavior (Excel treats positive and negative numbers 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.
The CEILING( ) function is 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 9-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-
prices that always end with .99?
The CEILING( ) function in action. Notice how different approaches to rounding net different profits.
9.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 simpler of the two, as it always rounds to whole numbers. You need only specify the number you want rounded. The formula here, for example, works out to 2:
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
. This argument is optional, and if you leave it out, TRUNC behaves exactly like INT( ):
For the most part, TRUNC( ) behaves just like ROUNDDOWN( ) because it
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
value (so that =
gives you4), 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 to3:
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'd use
is when you want to
the digits of a number that you don't want, rather than
them. For example, imagine you want to start
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:
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, then 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 rely on having only five chairs to sell at the end of the day. You can make a similar case for using the ROUNDUP( ) function. For example, maybe you know your
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.
POWER USERS' CLINIC
Excel's Rounding May Not Be Good Enough
Even though Excel provides 10 rounding functions, they all work in exactly the same way. They use a technique called
, which always rounds the number 5 up. Although this may seem like the only option,
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 convention can bias your results if you're rounding a large
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,
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. You could also use a different type of rounding. One example is
, 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 in Section 28.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.
9.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
odd or even number. But they don't. Since the functions round
, the closest correct number may be,
speaking, a confusingly long ways away.
To understand these quirky functions a little better, consider the following formula:
This formula produces the expected result: It rounds 2.6 up to the closest odd number, 3. Now consider:
This formula 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:
The EVEN( ) function behaves similarly. Thus, the result of the following formula is 4:
The EVEN( ) and ODD( ) functions aren't useful too often. For most people, they simply represent an interesting footnote in Excel functions.