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
The INT function
The INT function rounds numbers down to the nearest integer. For example, the formulas
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
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.