Rounding and Truncating Numbers
Problem
Your calculations call for you to either round or truncate values in your spreadsheet.
Solution
Use one or more of the built-in functions shown in Table 7-6.
|
Function |
Syntax |
Description |
|---|---|---|
|
ROUND |
=ROUND(n, digits) |
Rounds the number n to the specified number of digits |
|
ROUNDUP |
=ROUNDUP(n, digits) |
Rounds the number n up to the specified number of digits |
|
ROUNDDOWN |
=ROUNDDOWN(n, digits) |
Rounds the number n down to the specified number of digits |
|
MROUND |
=MROUND(n, multiple) |
Rounds the number n to the nearest multiple |
|
CEILING |
=CEILING(n, multiple) |
Rounds the number n up to the nearest multiple |
|
EVEN |
=EVEN(n) |
Rounds the number n to the nearest even integer |
|
ODD |
=ODD(n) |
Rounds the number n to the nearest odd integer |
|
INT |
=INT(n) |
Rounds the number n to the nearest integer |
|
trUNC |
=trUNC(n, digits) |
Truncates the number n at the specified number of digits |
Discussion
In Excel you can format any cell displaying a value to display that value to a specified number of decimal places. For example, you can format a cell containing the value 4.5837450 to display the number to only two decimal places (that is, 4.58). Formatting a cell to display only a certain number of decimal places does not change the underlying value; it only changes what's shown in the cell. To actually change the value, you need to use the rounding or truncating functions shown in Table 7-6.
I pretty much use the ROUND function as my workhorse for rounding numbers. For example, to round the value 3.213 to one decimal place use =ROUND(3.213, 1); this returns 3.2. To round a value to the nearest integer, specify 0 for the number of digits. For example, to round 3.213 to the nearest integer use =ROUND(3.213, 0) this returns 3.
You can also use ROUND to round a value to the nearest significant digit to the left of the decimal place. To do so, specify a negative number of digits. For example, =ROUND(121.986,-1) returns a value of 120. The formula =ROUND(12839.31,-3) returns a value of 13,000.