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.
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:
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
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.
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.
If you reduce the column width, with General format applied, the numbers will be automatically rounded, in an attempt to fit.
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.
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
The ROUND function can be used to round a number to a specific number of places from the decimal point. For 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.
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.
The Excel ROUNDDOWN rounds a number down, to a specific number of places from the decimal point. For example:
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.
The Excel ROUNDUP function rounds a number up, to a specific number of places from the decimal point. For example:
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.
The MROUND function rounds a number to the closest specified multiple. For example:
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.
The Excel FLOOR function rounds numbers down, toward zero, based on the multiple of significance that you specify.
In the screen shot below, the year is rounded down to the nearest decade, by using 10 as the significance in the FLOOR function.
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.
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.
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.
The Excel CEILING function rounds numbers up, toward zero, based on the multiple of significance that you specify. For example:
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.
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.
Last updated: January 26, 2017 4:35 PM
Contextures RSS Feed