Excel: Rounding off

 

Objectives:

 

 

Using Excel help (F1):

In the Search For box, type Round and press the Enter key.

In the Search Results list, click on Round a number and read the topics.

 

 

Excel Bible: Appendix A

 

Even though Excel appears to round numbers off automatically, it only rounds off the number that is displayed – it does NOT round off the underlying number!

 

Example

·         Enter the number 1.234 in cell A1.

·         Click on the $ button on the formatting toolbar to display the number with a $ and two decimal places. You will see $1.23 in the cell.

·         Enter the formula =A1*2 in cell A2.

·         Click on the $ button on the formatting toolbar.

·         You will see $2.47 in the cell, not $2.46, which is what you might expect to get if you multiply $1.23 times 2. However, Excel is not multiplying $1.23 times 2. It is multiplying 1.234 times 2, giving 2.468, which, when formatted with a dollar sign and 2 decimal places, is displayed as $2.47.

 

If you want the number that is stored in the cell to be the same as the number that is displayed in the cell, you need to use one of Excel's rounding functions.

 

Excel has the following functions for rounding numbers

·         Round(number, decimal places)

·         RoundDown(number, decimal places)

·         RoundUp(number, decimal places)

·         Even(number)

·         Odd(number)

·         MRound(number, multiple)

·         Trunc(number,num_digits)

·         Floor(number, multiple)

·         Ceiling(number, multiple)

 

In all of the following examples, the decimal places argument is interpreted like this:

·         If decimal places is a positive number, the number will be rounded to the right of the decimal point. For example, 1 decimal place will round to the nearest tenth, 2 will round to the nearest hundredth, 3 will round to the nearest thousandth, etc.

·         If decimal places is 0, the number will be rounded to the nearest whole number.

·         If decimal places is a negative number, the number will be rounded to the left of the decimal point. For example, -1 decimal place will round to the nearest 10, -2 will round to the nearest 100, -3 will round to the nearest 1,000, etc.

 

 

Round(number,decimal places)

Rounds a number to a specified number of digits. If a digit in the range 5-9 is to the right of the column that you are rounding to, the number will be rounded up. If the digit is in the range 0-4, the number will be rounded down.

 

Number is the number you want to round.

Decimal places specifies the number of digits to which you want to round the number.

 

=Round(10.44, 1) rounds to the nearest tenth, giving 10.4

=Round(10.45, 1) rounds to the nearest tenth, giving 10.5

=Round(10.45, 0) rounds to the nearest whole number, giving 10.

=Round(1234, -2) rounds to the nearest 100, giving 1200.

 

 

RoundUp(number, decimal places)

Always "rounds" up. If the number is negative, it will be rounded away from 0.

 

Number: The first argument is the number that you want to round off (this will be a cell reference or a formula).

Decimal places: The second argument is the number of decimal places that you want to round off to.

 

=RoundUp(20.125, 2) rounds up to 2 decimal places (hundredths), giving 20.13.

=RoundUp(20.125, 1) rounds up to 1 decimal place (tenths), giving 20.2.

=RoundUp(20.125, 0) rounds up to 0 decimal places (the next whole number), giving 21.

=RoundUp(20.125, -1) rounds up to the nearest 10, giving 30.

=RoundUp(-10.7, 0) rounds "up" (away from 0) to the nearest whole number, giving 11.

 

 

Even(number)

=Even(20.125) rounds up (away from 0) to the next highest even number, giving 22.

 

 

Odd(number)

=Odd(20.125) rounds up (away from 0) to the next highest odd number, giving 21.

 

 

RoundDown(number, decimal places)

This function doesn't really "round" at all – it just lops off (truncates) the extra digits. If the number is negative, it will be "rounded" towards 0.

 

Number: The first argument is the number that you want to round off (this will be a cell reference or a formula).

Decimal places: The second argument is the number of decimal places that you want to round off to.

 

=RoundDown(20.125, 2) truncates everything to the right of 2 decimal places (hundredths), giving 20.12.

=RoundDown(20.125, 1) truncates everything to the right of 1 decimal place (tenths), giving 20.1.

=RoundDown(20.125, 0) truncates all digits to the right of the decimal point (the previous whole number), giving 20.

=RoundDown(20.125, -1) truncates any decimal places and changes the ones digit to a 0, giving 20.

=RoundDown(-10.7, 0) truncates all digits to the right of the decimal point, giving -10.

 

 

The Trunc (truncate) function is identical to the RoundDown function:

 

Trunc(number, decimal places)

This function truncates (throws away) digits on the right side of the number without rounding.

Number is the number you want to truncate.

Num_digits is a number specifying the precision of the truncation. The default value for num_digits is 0 (zero) and will truncate all digits to the right of the decimal point, leaving a whole number.

 

=Trunc(10.5) truncates to the default number of decimal places (0), giving 10.

=Trunc(10.599, 1) truncates to 1 decimal place (tenths), giving 10.5.

 

 

Floor(number, multiple)

This function is similar to the RoundDown function, except that the second argument is not the number of decimal places – it is the multiple that you want to round to. The function always takes the number to the next highest multiple of the multiple argument. If number is negative, then multiple must also be negative, and the rounding will always be away from 0.

 

Number is the number you want to round.

Multiple is the multiple that you want to round to.

 

=Floor(2.1,2) rounds down to the next lowest multiple of 2, giving 2.

=Floor(2.0, 2) since 2.0 is already a multiple of 2, the number does not have to be changed, so the result is 2.

=Floor(-11, -5) rounds to the next "lowest" (closest from 0) multiple of -5, giving -10.

 

 

Ceiling(number, multiple)

This function is similar to the RoundUp function, except that the second argument is not the number of decimal places – it is the multiple that you want to round to (so this is actually more flexible than the RoundUp function). The function always takes the number to the next highest multiple of the multiple argument. If number is negative, then multiple must also be negative, and the rounding will always be away from 0.

 

Number is the number you want to round.

Multiple is the multiple that you want to round to.

 

=ceiling(2.1,2) rounds up to the next highest multiple of 2, giving 4.

=ceiling(2.0, 2) since 2.0 is already a multiple of 2, the number does not have to be changed, so the result is 2.

=ceiling(-11, -5) rounds to the next "highest" (farthest from 0) multiple of -5, giving -15.

 

 

MRound(number, multiple)

This function rounds a number to the nearest number that is a multiple of the second argument.

 

Number is the number you want to round.

Multiple is the multiple that you want to round to.

Both arguments must have the same sign or you will get an error. Note that sometimes this may cause the number to be rounded up, and sometimes it will be rounded down. If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. From the menu, choose Tools | Add-ins | Analysis ToolPak and click on OK.

 

=MRound(10, 3) rounds 10 to the nearest multiple of 3, so the result is 9.

=MRound(11, 3) rounds 11 to the nearest multiple of 3, so the result is 12.

=MRound(10.13, .05) rounds to the nearest nickel, so the result is 10.15.

 

 

INT(number)

This function rounds down (towards 0 if the number is positive, away from 0 if the number is negative) to the next lowest integer.

 

Number is the number you want to round down.

 

=Int(9.00001) rounds down to 9.

=Int(-9.00001) rounds down to -10.

 

 

 

 

Updated 2005.12.29