Rounding Numbers

Excel provides quite a few functions that round values in various ways. Table 10-1 summarizes these functions.

Table 10-1: EXCEL ROUNDING FUNCTIONS
Open table as spreadsheet

Function	Description
CEILING	Rounds a number up (away from zero) to the nearest specified multiple
DOLLARDE	Converts a dollar price expressed as a fraction into a decimal number
DOLLARFR	Converts a dollar price expressed as a decimal into a fractional number
EVEN	Rounds a number up (away from zero) to the nearest even integer
FLOOR	Rounds a number down (toward zero) to the nearest specified multiple
INT	Rounds a number down to make it an integer
MROUND	Rounds a number to a specified multiple
ODD	Rounds a number up (away from zero) to the nearest odd integer
ROUND	Rounds a number to a specified number of digits
ROUNDDOWN	Rounds a number down (toward zero) to a specified number of digits
ROUNDUP	Rounds a number up (away from zero) to a specified number of digits
TRUNC	Truncates a number to a specified number of significant digits

Caution 

It's important to understand the difference between rounding a value and formatting a value. When you format a number to display a specific number of decimal places, formulas that refer to that number use the actual value, which may differ from the displayed value. When you round a number, formulas that refer to that value use the rounded number.

Cross Ref 

Chapter 6 contains examples of rounding time values.

The following sections provide examples of formulas that use various types of rounding.

Basic Rounding Formulas

The ROUND function is useful for basic rounding to a specified number of digits. You specify the number of digits in the second argument for the ROUND function. For example, the formula that follows returns 123.40 (the value is rounded to one decimal place):

 =ROUND(123.37,1) 

If the second argument for the ROUND function is zero, the value is rounded to the nearest integer. The formula that follows, for example, returns 123.00:

 =ROUND(123.37,0) 

The second argument for the ROUND function can also be negative. In such a case, the number is rounded to the left of the decimal point. The following formula, for example, returns 120.00:

 =ROUND(123.37,-1) 

The ROUND function rounds either up or down. But how does it handle a number such as 12.5, rounded to no decimal places? You'll find that the ROUND function rounds such numbers away from zero. The formula that follows, for instance, returns 13.0:

 =ROUND(12.5,0) 

The next formula returns –13.00 (the rounding occurs away from zero):

 =ROUND(-12.5,0) 

To force rounding to occur in a particular direction, use the ROUNDUP or ROUNDDOWN functions. The following formula, for example, returns 12.0. The value rounds down.

 =ROUNDDOWN(12.5,0) 

The formula that follows returns 13.0. The value rounds up to the nearest whole value.

 =ROUNDUP(12.43,0) 

Rounding to the Nearest Multiple

The MROUND function is useful for rounding values to the nearest multiple. For example, you can use this function to round a number to the nearest 5. The following formula returns 135:

 =MROUND(133,5) 

Rounding Currency Values

Often, you need to round currency values. For example, you may need to round a dollar amount to the nearest penny. A calculated price may be something like $45.78923. In such a case, you'll want to round the calculated price to the nearest penny. This may sound simple, but there are actually three ways to round such a value:

• Round it up to the nearest penny.

• Round it down to the nearest penny.

• Round it to the nearest penny (the rounding may be up or down).

The following formula assumes a dollar and cents value is in cell A1. The formula rounds the value to the nearest penny. For example, if cell A1 contains $12.421, the formula returns $12.42.

 =ROUND(A1,2) 

If you need to round the value up to the nearest penny, use the CEILING function. The following formula rounds the value in cell A1 up to the nearest penny. For example, if cell A1 contains $12.421, the formula returns $12.43.

 =CEILING(A1,0.01) 

To round a dollar value down, use the FLOOR function. The following formula, for example, rounds the dollar value in cell A1 down to the nearest penny. If cell A1 contains $12.421, the formula returns $12.42.

 =FLOOR(A1,0.01) 

To round a dollar value up to the nearest nickel, use this formula:

 =CEILING(A1,0.05) 

You've probably noticed that many retail prices end in $0.99. If you have an even dollar price and you want it to end in $0.99, just subtract .01 from the price. Some higher-ticket items are always priced to end with $9.99. To round a price to the nearest $9.99, first round it to the nearest $10.00 and then subtract a penny. If cell A1 contains a price, use a formula like this to convert it to a price that ends in $9.99:

 =ROUND(A1/10,0)*10-0.01 

For example, if cell A1 contains $345.78, the formula returns $349.99.

A simpler approach uses the MROUND function:

 =MROUND(A1,10)-0.01 

Working with Fractional Dollars

The DOLLARFR and DOLLARDE functions are useful when working with fractional dollar value, as in stock market quotes.

Consider the value $9.25. You can express the decimal part as a fractional value ($9 1/4, $9 2/8, $9 4/16, and so on). The DOLLARFR function takes two arguments: the dollar amount and the denominator for the fractional part. The following formula, for example, returns 9.1 (the .1 decimal represents 1/4):

 =DOLLARFR(9.25,4) 

Caution 

In most situations, you won't use the value returned by the DOLLARFR function in other calculations. In the preceding example, the result of the function will be interpreted as 9.1, not 9.25. To perform calculations on such a value, you need to convert it back to a decimal value by using the DOLLARDE function.

The DOLLARDE function converts a dollar value expressed as a fraction to a decimal amount. It also uses a second argument to specify the denominator of the fractional part. The following formula, for example, returns 9.25:

 =DOLLARDE(9.1,4) 

Tip 

The DOLLARDE and DOLLARFR functions aren't limited to dollar values. For example, you can use these functions to work with feet and inches. You might have a value that represents 8.5 feet. Use the following formula to express this value in terms of feet and inches. The formula returns 8.06 (which represents 8 feet, 6 inches).

 =DOLLARFR(8.5,12) 

Another example is baseball statistics. A pitcher may work 6²/3 innings, and this is usually represented as 6.2. The following formula displays 6.2:

 =DOLLARFR(6+2/3,3) 

Using the INT and TRUNC Functions

On the surface, the INT and TRUNC functions seem similar. Both convert a value to an integer. The TRUNC function simply removes the fractional part of a number. The INT function rounds a number down to the nearest integer, based on the value of the fractional part of the number.

In practice, INT and TRUNC return different results only when using negative numbers. For example, the following formula returns –14.0:

 =TRUNC(-14.2) 

The next formula returns –15.0 because –14.3 is rounded down to the next lower integer:

 =INT(-14.2) 

The TRUNC function takes an additional (optional) argument that's useful for truncating decimal values. For example, the formula that follows returns 54.33 (the value truncated to two decimal places):

 =TRUNC(54.3333333,2) 

Rounding to an Even or Odd Integer

The ODD and EVEN functions are provided for situations in which you need to round a number up to the nearest odd or even integer. These functions take a single argument and return an integer value. The EVEN function rounds its argument up to the nearest even integer. The ODD function rounds its argument up to the nearest odd integer. Table 10-2 shows some examples of these functions.

Table 10-2: RESULTS USING THE EVEN AND ODD FUNCTIONS
Open table as spreadsheet

Number	EVEN Function	ODD Function
−3.6	−4	−5
−3.0	−4	−3
−2.4	−4	−3
−1.8	−2	−3
−1.2	−2	−3
−0.6	−2	−1
0.0	0	1
0.6	2	1
1.2	2	3
1.8	2	3
2.4	4	3
3.0	4	3
3.6	4	5

Rounding to n Significant Digits

In some cases, you may need to round a value to a particular number of significant digits. For example, you might want to express the value 1,432,187 in terms of two significant digits (that is, as 1,400,000). The value 9,187,877 expressed in terms of three significant digits is 9,180,000.

If the value is a positive number with no decimal places, the following formula does the job. This formula rounds the number in cell A1 to two significant digits. To round to a different number of significant digits, replace the 2 in this formula with a different number.

 =ROUNDDOWN(A1,2-LEN(A1)) 

For non-integers and negative numbers, the solution gets a bit trickier. The formula that follows provides a more general solution that rounds the value in cell A1 to the number of significant digits specified in cell A2. This formula works for positive and negative integers and non-integers.

 =ROUND(A1,A2-1-INT(LOG10(ABS(A1)))) 

For example, if cell A1 contains 1.27845 and cell A2 contains 3, the formula returns 1.28000 (the value, rounded to three significant digits).