Current Special! Complete Excel
for Excel 97 - Excel 2003, only
30 Day Money Back Guarantee & Free
Excel Help for LIFE!
Got any Excel Questions? Free Excel Help
Excel allows us to easily round number for viewing via Format>Cells - Number - and then Number under Category. You can short cut this by using the Increase Decimals and Decrease Decimals on the Format toolbar, to the right of $ % , symbols. However, as with all formatting, the true underlying value of the cell is not changed. This matters when you add some numbers and you get a lower result than you expect. For example, Enter 1.6 and 1.6 in 2 cells, say A1 and A2. Now format these so no decimal places are showing and you get 2 and 2. Now add these together like =A1+A2 and you get a result of 3, not 4. You can change this and have excel work with all numbers as they are displayed via Tools>Options - Calculation and check Precision as displayed. BE WARNED, that this is a one-way method and the changes to ALL your numbers cannot be undone.
TIP. You can can automatically permanently change ALL numbers in a Workbook to their displayed value by checking Precision as displayed and then clicking OK. Now go back Tools>Options - Calculation and uncheck Precision as displayed
If you wish to actually change the true underlying value of number we can use one of the many ROUND Functions.
The ROUND Function will round numbers to a specified number of digits. If the number of digits is negative, it will round to the left of the decimal point. If zero it will round to the nearest Integer (whole number).
=ROUND(1.5,0) will result in a value of 2
=ROUND(1.4,0) will result in a value of 1
Round Numbers Up
The ROUNDUP function will round numbers up, away from zero.
=ROUNDUP(1.4,0) will result in a value of 2
Round Numbers Down
The ROUNDDOWN function will round numbers down, toward zero.
=ROUNDDOWN(1.6,0) will result in a value of 1
INT (although less flexible) can also be used. =INT(1.6) will also result in 1
Round to a Chosen Multiple
There is another round function in Excel that will round a number to the multiple we specify. This function is called MROUND. It is not available by default, so you need to go to Tools>Add-ins and check Analysis Toolpak.
=MROUND(22.5,10) will result in a value of 20
=MROUND(22.5,5) will result in a value of 25
Some Other Round Functions
Some of the other function that can used for rounding are;
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to firstname.lastname@example.org 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates