Excel Rounding Functions

How to choose functions in Excel that round numbers, up or down. Or format a number to look rounded, but keep the original value in the cell. Fix small differences in column sums

Rounding in Excel

To see a quick overview of 7 ways to round in Excel, you can watch this short slide show. There are details and step by step instructions, below the slide show.

Also, get the 7 Ways to Round sample workbook, so you can follow along with the slide show. The zipped file is in xlsx format, and does not contain any macros.

Round With Formatting

With formatting, a number can appear rounded on the worksheet, but the cell value is not affected. In the screen shot below, the total appears to be incorrect, because 2+3 does not equal 4.

However, if you click on any of the cells, you can see its actual value in the formula bar. For example, the coffee price, in cell B3, is $1.54.

The numbers in cells B3:B5 were formatted as currency, using the Number format drop down on the Ribbon's Home tab.

Then, the Decrease Decimal button was clicked twice, to set the number of decimals to zero.

If the Currency number format is reapplied, it shows the default formatting of 2 decimals, so the total amount looks correct -- 1.54 + 2.54 =4.08

When the numbers were rounded with formatting, each amount displayed as the closest dollar amount:

• the coffee price rounded up to $2
• the sandwich rounded up to $3
• the total rounded down to $4.

Automatic Rounding of Long Numbers

If you leave the default number format -- General -- and enter long numbers on a worksheet, Excel might round some of those numbers automatically.

To see how Excel can automatically round numbers with the General format, watch this short video, and the details are below the video

Maximum 11 Characters

In my tests, a maximum of 11 characters (including a decimal place) are displayed in a cell, even if the column is wide enough to show more numbers.

• If a number is longer than 11 characters, the decimals are rounded, to show a maximum of 11 characters.



• If a number is longer than 11 characters, and there are no decimals, the number is changed to Scientific format.

To prevent this automatic rounding/ormatting, apply one of the other number formats, such as Number, and set the number of decimals that you want to display.

Column Too Narrow

If you reduce the column width, with General format applied, the numbers will be automatically rounded, in an attempt to fit.

• Decimals are rounded, to show only the number that will fit the column
• The number will be rounded to an integer, if there is no room for decimals
• The number will change to Scientific format, if the rounded integer will not fit
• The cell will display number signs, if it can't display in Scientific format

Round With Custom Number Format

In a summary report, with very large numbers, precision to the decimal places is probably not required. It's difficult to get a quick overvier, if the numbers are too detailed, as shown below.

To round large numbers, and make them easier to read, you can use Custom Number Formats, to round to the left of the decimal point.

For example, show 123456.78 in one of the following formats.

Apply a Custom Format

To apply a custom format to cells:

On the Ribbon's Home tab, click the dialog launcher, at the bottom right of the Number group.

On the Number tab, in the list of Categories, click Custom.

Then, select one of the existing custom formats, or clear the box, to create your own.

For example, enter the custom format -- #, -- to show only the rounded thousands in a large number.

Then, click the OK button, to close the dialog box, and apply the format.

Here are a few more custom number formats that you can use:

You can also use a custom number format to line up numbers with varying decimal places. The question mark (?) is a placeholder that adds extra spacing, so the numbers line up at the decimal point.

There are more examples of Custom formats on the Microsoft website: Create a Custom Number Format

ROUND Function

The ROUND function can be used to round a number to a specific number of places from the decimal point. For example:

1. Round a sales amount to the nearest cent
2. Round an age to the nearest decade
3. Total amounts with small differences

Sales Tax Example

In the screen shot below, the sales tax (HST) is rounded to the nearest cent, by using 2 as the number of decimal places to round.

=ROUND(B5*D6,2)

Age by Decade Example

In the screen shot below, the age is rounded to the nearest decade, by using -1 as the number of decimal places to round. Because the number is negative, it rounds to the left of the decimal place.

=ROUND(B4,-1)

Total Amounts with Small Difference

In the worksheet shown below, there is a small difference between the sum of the Total column and the Received column.

• In column C, tax is calculated by multiplying sales price times TaxRate (cell F1)
  • =[@Cost]*TaxRate
• When payment is received, that amount is manually entered in column E
  • Amount entered always checked, to ensure it matches the Total due.

Why are the totals different, when the amounts are carefully entered, and checked for accuracy?

• In column F, a formula finds the difference between the Total and Received amounts.
• Even though they look the same on the worksheet, there is a small difference in most rows.
• This is caused by hidden decimal places in the Tax column.

Fix SUM Differences With ROUND Function

To fix the problem, use the ROUND function in the Tax calculation column.

• =ROUND([@Cost]*TaxRate,2)

With the tax rounded to 2 decimal places, it is an exact match for the amount that's typed in the Received column.

• In each row, the Difference calculation shows a result of zero
• In the Total row, the sums in the two columns are exactly the same.

ROUNDDOWN Function

The Excel ROUNDDOWN rounds a number down, to a specific number of places from the decimal point. For example:

• Round a year down to the nearest decade

In the screen shot below, the publication year is rounded down to the nearest decade, by using -1 as the number of digits in the ROUNDDOWN function. Because the number is negative, it rounds to the left of the decimal place.

=ROUNDDOWN(B4,-1)

ROUNDUP Function

The Excel ROUNDUP function rounds a number up, to a specific number of places from the decimal point. For example:

• Round a price up to the nearest dime

In the screen shot below, the price is rounded up to the nearest dime, by using 1 as the number of digits in the ROUNDUP function.

=ROUNDUP(B4,1)

MROUND Function

The MROUND function rounds a number to the closest specified multiple. For example:

• Round a sales amount to the nearest nickel

In the screen shot below, the total sales amount is rounded to the nearest nickel, by using 5 as the multiple in the MROUND function. Instead of hard coding the multiple into the formula, it is entered in cell B9, where it can be changed easily.

=MROUND($B$7,$B$9)

FLOOR Function

The Excel FLOOR function rounds numbers down, toward zero, based on the multiple of significance that you specify.

FLOOR(number,significance)

For example:

• Round a year down to the nearest decade

In the screen shot below, the year is rounded down to the nearest decade, by using 10 as the significance in the FLOOR function.

=FLOOR(B4,10)

NOTE: In the FLOOR function, if the number is positive, and the significance is negative, the result is the #NUM! error. The FLOOR function works well in the music example above, because the song's year is always a positive number.

FLOOR.PRECISE or FLOOR.MATH Function

For Excel 2010, Microsoft recommends that you use the FLOOR.PRECISE function, instead of FLOOR.

For Excel 2013, use FLOOR.MATH, instead of FLOOR.

Both FLOOR.PRECISE and FLOOR.MATH return a number that is rounded down to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded down.

FLOOR.PRECISE(number,significance)

or

FLOOR.MATH(number,significance)

The three functions are compared in the following table. The results from FLOOR.PRECISE and FLOOR.MATH are the same, but FLOOR is different -- it rounds toward zero, and returns an error if the number is positive and the significance is negative.

CEILING Function

The Excel CEILING function rounds numbers up, toward zero, based on the multiple of significance that you specify. For example:

• Round a price up to the nearest quarter

In the screen shot below, the price is rounded up to the nearest quarter, by using 0.25 as the multiple in the CEILING function.

=CEILING(B4,0.25)

Sum Rounded Numbers

If a column has decimal numbers, you can use the ROUND function, to round each number individually, BEFORE calculating the total. This might give a different result than rounding the numbers AFTER summing them. .

In this example, there are decimal numbers in cells A1:A5

1.5

2.9

3.8

9.2

3.5

In cell A6, the formula rounds the total to 0 decimal places, AFTER summing them. It returns a result of 21.0

=ROUND(SUM(A1:A5), 0)

The formula in cell A7 rounds each number BEFORE summing them. It returns a result of 22.0

=SUMPRODUCT(ROUND(A1:A5,0))

Get the Sample Files

1. Rounding Examples: To follow this tutorial, you can get the rounding functions sample file. The zipped file is in xlsx format, and does not contain macros. Some functions will only work in newer versions of Excel.
2. 7 Ways to Round: Get the 7 Ways to Round sample workbook, to follow along with the 7 Ways to Round slide show. The zipped file is in xlsx format, and does not contain any macros.
3. Column Sum Differences: Get this tax calculation column sum difference sample workbook. The zipped file is in xlsx format, and does not contain any macros.

Excel Functions Tutorials

30 Functions in 30 Days

Functions List

AVERAGE

SUM / SUMIF

Subtotal Feature

VLOOKUP