CoverAcknowledgementsExcel OverviewTime SaverAuto Fill and Fill SeriesCell ReferencingAbsolute and Relative ReferencingNavigation ShortcutsManaging CellsSorting and FilteringHiding and GroupingPaste SpecialNamed RangesRankingRoundingSum and CountManaging WorksheetsLookup Functions Data Validation IF Statements Formula Errors Text FunctionsText to ColumnsLogic Functions 2Date and TimeConditional Formatting 1Standards of Professionalism Spreadsheet Formatting and Design Basic ChartsDashboardsConditional Formatting 2Spreadsheet Protection Basic Pivot TablesAdvanced Pivot Tables and Slicers Logic Functions 3Advanced Lookup FunctionsLoan Amortization Schedule Advanced ChartsMacros 1Macros 2Macros 3How to Use Search Engines

Rounding

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

BUS115_image_9.1.png
Figure 9.1

Practice Spreadsheet

Use this workbook for the chapter.

Note: This is the same workbook used in the Ranking chapter.

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.

BUS115_image_9.2.png
Figure 9.2

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)

BUS115_image_9.3.png
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)).
BUS115_image_9.4.png
Figure 9.4

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)).
BUS115_image_9.5.png
Figure 9.5

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)).
BUS115_image_9.6.png
Figure 9.6

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)).
BUS115_image_9.7.png
Figure 9.7

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)).
BUS115_image_9.8.png
Figure 9.8

Supplemental Resource

End-of-Chapter Survey

: How would you rate the overall quality of this chapter?
  1. Very Low Quality
  2. Low Quality
  3. Moderate Quality
  4. High Quality
  5. Very High Quality
Comments will be automatically submitted when you navigate away from the page.
Like this? Endorse it!