Microsoft Office Tutorials and References
In Depth Information
Rounding to n significant digits
In practice, INT and TRUNC return different results only when using negative numbers. For
example, the following formula returns –14.0:
=TRUNC(-14.2)
The next formula returns –15.0 because –14.2 is rounded down to the next lower integer:
=INT(-14.2)
The TRUNC function takes an additional (optional) argument that’s useful for truncating decimal
values. For example, the following formula returns 54.33 (the value truncated to two decimal
places):
=TRUNC(54.3333333,2)
Rounding to n significant digits
In some situations, you might need to round a value to a particular number of significant digits.
For example, you might want to express the value 1,432,187 in terms of two significant digits
(that is, as 1,400,000). The value 84,356 expressed in terms of three significant digits is 84,300.
If the value is a positive number with no decimal places, the following formula does the job. This
formula rounds the number in cell A1 to two significant digits. To round to a different number of
significant digits, replace the 2 in this formula with a different number:
=ROUNDDOWN(A1,2-LEN(A1))
For nonintegers and negative numbers, the solution is a bit trickier. The following formula
provides a more general solution that rounds the value in cell A1 to the number of significant digits
specified in cell A2. This formula works for positive and negative integers and nonintegers:
=ROUND(A1,A2-1-INT(LOG10(ABS(A1))))
For example, if cell A1 contains 1.27845 and cell A2 contains 3, the formula returns 1.28000 (the
value, rounded to three significant digits).
 
Search JabSto ::




Custom Search