In this chapter, we are going to take a look at various rounding functions in Excel.

Let’s look at the first cell in the GPA column. Alice has a GPA of 3.92. You’ll notice in the formula bar, the value of her GPA is actually 3.919 (see Figure 9.2), but we only have two decimals showing in the cell itself—it rounds to 3.92.

If we want to change the number of decimals that show in the cell, we can select the cell, make sure we’re on the Home tab, and then change the number of decimal digits showing using the Increase Decimal and Decrease Decimal buttons. This will increase or decrease the number of decimal digits displayed. (See Figure 9.3)

In each case when it removes a decimal, it will round what is showing according to the actual number in the cell. For instance, if we reduce the number of decimals to the nearest whole number, 3.919 rounds to four. The important thing to note is that regardless of how many decimals are showing in the cell, the actual number in the cell is whatever it is—3.919 in this case—which may be different from what actually shows in the cell. This is important because if you use any functions or formulas that refer to this cell, what actually is used to calculate is the number that is embedded in the cell rather than what is shown in it. This method of rounding is different from using a rounding formula.

Round Function

We can also round the numbers with a formula. The Round function will round up or down to the nearest decimal place specified in the formula.

1. Type =ROUND and an open (left) parenthesis ( ( ) in an empty cell.
2. Select the reference cell with the data to round (E3 in Figure 9.4).
3. Type a comma ( , ) followed by the number for the nearest digit to round to (2 in Figure 9.4).
1. This number is the digit after the decimal point. For example, 1 will round to the nearest tenth, 2 will round to the nearest hundredth, and 3 will round to the nearest thousandth.
4. Type a close (right) parenthesis ( ) ) to close the formula.
5. Press Enter to complete the cell’s formula (=ROUND(E3,2)).

Round Down Function

The Round Down function will round down to the nearest decimal place specified in the formula.

1. Type =ROUNDDOWN and an open (left) parenthesis ( ( ) in an empty cell.
2. Select the reference cell with the data to round (E3 in Figure 9.5).
3. Type a comma ( , ) followed by the number for the nearest digit to round down to (2 in Figure 9.5).
1. This number is the digit after the decimal point. For example, 1 will round down to the nearest tenth, 2 will round down to the nearest hundredth, and 3 will round down to the nearest thousandth.
4. Type a close (right) parenthesis ( ) ) to close the formula.
5. Press Enter to complete the cell’s formula (=ROUNDDOWN(E3,2)).

Roundup function

The Roundup Function will round up to the nearest decimal place specified in the formula.

1. Type =ROUNDUP and an open (left) parenthesis ( ( ) in an empty cell.
2. Select the reference cell with the data to round (E3 in Figure 9.6).
3. Type a comma ( , ) followed by the number for the nearest digit to round up to (2 in Figure 9.6).
1. This number is the digit after the decimal point. For example, 1 will round up to the nearest tenth, 2 will round up to the nearest hundredth, and 3 will round up to the nearest thousandth.
4. Type a close (right) parenthesis ( ) ) to close the formula.
5. Press Enter to complete the cell’s formula (=ROUNDUP(E9,2)).

Ceiling Function

The Ceiling Function will round up to the nearest multiple of the number specified in the formula.

1. Type =CEILING.MATH and an open (left) parenthesis ( ( ) in an empty cell.
2. Select the reference cell with the data to round (D3 in Figure 9.7).
3. Type a comma ( , ) followed by the number for the nearest multiple to round up to (5 in Figure 9.7).
1. This number represents the number of significance that rounds up to the nearest integer. For example, 5 will round up the reference number to the nearest multiple of 5.
4. Type a close (right) parenthesis ( ) ) to close the formula.
5. Press Enter to complete the cell’s formula (=CEILING.MATH(D3,5)).

Floor Function

The Floor Function will round down to the nearest multiple of the number specified in the formula.

1. Type =FLOOR.MATH and an open (left) parenthesis ( ( ) in an empty cell.
2. Select the reference cell with the data to round (D3 in Figure 9.8).
3. Type a comma ( , ) followed by the number for the nearest multiple to round down to (5 in Figure 9.8).
1. This number represents the number of significance that rounds down to the nearest integers. For example, 5 will round down the reference number to the nearest multiple of 5.
4. Type a close (right) parenthesis ( ) ) to close the formula.
5. Press Enter to complete the cell’s formula (=FLOOR.MATH(D3,5)).

When completed, your practice worksheet should look like the following: