Microsoft Office Tutorials and References

In Depth Information

**Using SUBTOTAL Instead of SUM with Multiple Levels of Totals**

places, leaving the integer 6. The formula then multiplies this result

by 20 to arrive at 120.

•
Cell D13 is equivalent to using CEILING with a significance of 20.

The formula divides 135 by 20, giving 6.75. Next, the formula adds just

less than 0.5 to make sure that any value greater than 6 is rounded up

to 7. Finally, the result is multiplied by 20 to arrive at 140.

Figure 11.14.

Figure 11.14.
A combination of

A combination of ROUND

can replace any of the eight

other functions used for rounding.

ROUND and

and INT

INT can replace any of the eight

other functions used for rounding.

In Excel 2003 and earlier, functions such as MROUND were not part of the

core Excel. They were enabled when someone installed the Analysis

Toolpack. Because new Excel users might never have installed the Analysis

Toolpack, some people avoided using MROUND and instead wrote the formulas

as shown in
Figure 11.14
.
Now that Microsoft has elevated all the Analysis

Toolpack functions to be part of the core Excel product, it is safe to use

those functions.

Using

Using
SUBTOTAL

with Multiple Levels of Totals

Consider the data set shown in
Figure 11.15
.
This report shows a list of in-

voices for each customer. Someone has manually inserted rows and used the

SUBTOTAL
Instead of

Instead of
SUM

SUM
with Multiple Levels of Totals