Round Numbers Up to the Nearest 5 or 10 in Excel

01
of 01

Excel CEILING Function

Rounding Numbers Up to the Nearest 5 or 10 with Excel's CEILING Function
Rounding Numbers Up to the Nearest 5 or 10 with the CEILING Function. &copy Ted French

CEILING Function Overview

Excel's CEILING function can be used to eliminate unwanted decimal places or insignificant digits in data by rounding the numbers upwards to the nearest value that is considered significant.

For example, the function can be used to round a number upwards to the nearest 5, 10, or other specified multiple.

A multiple of a number can quickly be determined by counting by the number. For example, 5, 10, 15, and 20 are all multiples of 5

A practical use for the function is to round up the cost of items to the nearest dime ( $ 0.10 ) to avoid having to deal with smaller change such as pennies ($ 0.01 ) and nickels ($ 0.05 ).

Note: To  round numbers up without specifying the amount of rounding, use the ROUNDUP function.

Changing Data with the Rounding Functions

Like other rounding functions, the CEILING function actual alters the data in your worksheet and will, therefore affect the results of any calculations that use the rounded values.

There are, on the other hand, formatting options in Excel that allow you change the number of decimal places displayed by your data without changing the numbers themselves.

Making formatting changes to data has no effect on calculations.

The CEILING Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the CEILING function is:

= CEILING ( Number, Significance )

Number - the value to be rounded. This argument can contain the actual data for rounding or it can be a cell reference to the location of the data in the worksheet.

Significance - the number of decimal places present in this argument indicates the number of decimal places or significant digits that will be present in the result (rows 2 and 3 of example)
- the function rounds the Number argument specified above up to the nearest multiple of this value
- if an integer is used for this argument all decimal places in the result will be removed and the result will be rounded up to the nearest multiple of this value (row 4 of example)
- for negative Number arguments and positive Significance arguments, the results are rounded upwards towards zero (rows 5 and 6 of example)
-  for negative Number arguments and negative Significance arguments, the results are rounded downwards away from zero (row 7 of example)

CEILING Function Examples

The example in the image above uses the CEILING function to round several decimal values to the next even integer.

The function can be entered by typing the function name and arguments into the desired cell or it can be entered using the function's dialog box as outlined below.

The steps used to enter the function into cell C2 are:

  1. Click on cell C2 to make it the active cell - this is where the results of the CEILING function will be displayed
  2. Click on the Formulas tab of the ribbon menu
  3. Choose Math & Trig from the ribbon to open the function drop down list
  4. Click on CEILING in the list to bring up the function's dialog box
  5. In the dialog box, click on the Number line
  6. Click on cell A2 in the worksheet to enter that cell reference into the dialog box
  7. In the dialog box, click on the Significance line
  8. Type in 0.1
  9. Click OK to complete the function and close the dialog box
  10. The answer 34.3 should appear in cell C2
  11. When you click on cell E1 the complete function = CEILING ( A2 , 0.1 ) appears in the formula bar above the worksheet

How Excel arrives at this answer is that:

  • first it removes one insignificant digit (2) from the end of the number - one decimal place in the Significance argument means only one decimal place in the result
  • next it rounds the remaining digit of the number up to 34.3 since this is the next highest multiple of 0.10 after 34.2

Cell C3 to C7 results

If the above steps are repeated for cells C3 to C7, the following results are obtained:

  • cell C3 contains the value 34.25, since the two decimal places in the Significance argument require two decimal places in the result and 34.25 is the next highest multiple of 0.05 after 34.22
  • cell C4 contains the value 35, since the Significance argument is an integer, all decimal places are removed from the result and 35 is the next highest multiple of 1 after 34
  • cell C5 contains the value -34.2 for the same reasons outlined for cell C2
  • cell C6 contains the value - 34 for the same reasons as cell C4
  • cell C7 contains the value -35, combining a negative Number argument and a negative integer for the Significance argument removes all decimal places and rounds the result down to the next multiple of 1 after -34

#NUM! Error Value

The #NUM! error value is returned by Excel for the CEILING function if a positive Number argument is combined with a negative Significance argument.

Format
mla apa chicago
Your Citation
French, Ted. "Round Numbers Up to the Nearest 5 or 10 in Excel." ThoughtCo, Jan. 8, 2017, thoughtco.com/round-numbers-up-to-nearest-5-or-10-3123986. French, Ted. (2017, January 8). Round Numbers Up to the Nearest 5 or 10 in Excel. Retrieved from https://www.thoughtco.com/round-numbers-up-to-nearest-5-or-10-3123986 French, Ted. "Round Numbers Up to the Nearest 5 or 10 in Excel." ThoughtCo. https://www.thoughtco.com/round-numbers-up-to-nearest-5-or-10-3123986 (accessed January 17, 2018).