Contextures

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.

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.

round with formatting total incorrect

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.

round with actual value in formula bar

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

round with formatting currency

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

round with formatting decrease decimals

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

round with formatting 2 decimals

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.
  • long numbers rounded - decimals

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

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

long numbers rounded - Scientific

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.

round with formatting large numbers

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.

round with formatting custom formatting

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.

round with formatting launch number format

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.

round with formatting custom format

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.

round with formatting custom format thousands

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

round with formatting custom format examples

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.

round with formatting custom format examples

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:

  • Round a sales amount to the nearest cent
  • Round an age to the nearest decade

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)

ROUND right

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)

ROUND right

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)

ROUND right

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)

ROUND right

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)

ROUND right

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)

ROUND right

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.

ROUND right

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)

ROUND right

Download the Sample File

To follow this tutorial, you can download 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.

Excel Functions Tutorials

30 Functions in 30 Days

Functions List

AVERAGE

SUM / SUMIF

Subtotal Feature

VLOOKUP

Search Contextures Sites

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: August 16, 2016 12:54 PM
Contextures RSS Feed