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 may need to round a value to a particular number of significant digits. For
example, you may 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 non-integers 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 non-integers:
=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