Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Rounding Numbers in Excel

| | Information Helpful? Why Not Donate.

Excel Rounding. Round Number in Excel. Round Up, Down & to a Multiple

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help

Excel Rounding

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.

Round Numbers

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;

CEILING
FLOOR
ODD
EVEN
TRUNC
DOLLAR
INT

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 [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

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