Microsoft Office Tutorials and References

In Depth Information

**Using the rounding functions**

Using the flexible MROUND function

Suppose you want to round a number to a multiple of something other than 10—for

example, rounding numbers to sixteenths so that when formatted as fractions they

never appear with a denominator larger than 16. The MROUND function rounds any

number to a multiple you specify.

The function takes the form =MROUND(
number
,
multiple
). For example, typing the

formula =MROUND(A1, .0625) rounds the number displayed in cell A1 in increments

of one-sixteenth. The function rounds up if the remainder after dividing
number
by

multiple
is at least half the value of
multiple
. If you want to apply this to an existing

formula, just wrap the MROUND formula around it by replacing A1 (in the example) with

your formula.

The INT function

The INT function rounds numbers down to the nearest integer. For example, the formulas

=INT(100.01)

=INT(100.99999999)

both return the value 100, even though the number 100.99999999 is essentially equal to

101. When a number is negative, INT also rounds that number down to the next integer. If

each of the numbers in these examples were negative, the resulting value would be
–101
.

AVERAGE vs. AVG

Some other spreadsheet programs use the AVG statistical function to compute

averages. In some previous versions of Excel, typing the formula =AVG(2, 4, 5, 8) results

in a #NAME? error. Excel now accepts AVG, although when you type the function, an

error dialog box appears, asking whether you want to change the function to AVERAGE.

That’s still kind of rude, but it works. Presumably, one reason why Excel doesn’t just

change AVG to AVERAGE for you is to remind you to start using the correct function

name.

When you use this function, Excel ignores cells containing text, logical values, or empty

cells, but it includes cells containing a zero value. You can also choose the AVERAGEA

function, which operates in the same way as AVERAGE, except it includes text and

logical values in the calculation.