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