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).