When to use SUM instead of Cell SUM

You typically use SUM rather than Cell SUM when you add items containing:

• analytic functions (e.g. Rank and NTILE)

• aggregated (sum total) items (e.g. AVG and VARIANCE)

Example - using SUM to calculate the average sales per employee

In this example, you use SUM to calculate an overall average sales figure per employee by region.

Figure 14-2 Using SUM to calculate the average sales per employee

Description of the illustration total_type1.gif

Key to figure:
a. The calculation item Avg sales per emp contains the calculation Sales SUM/No. of employees. For example, the value for the East region is 20,000 (i.e. 200,000/10).
b. In the Sales SUM and No. of employees columns, the Totals values contain the sums of the two columns.
c. In the column Avg sales per emp, the Totals value is calculated as 11,428 (i.e. 400,000/35).

In the figure above, the worksheet contains four items, including the calculation item Avg sales per emp. When you calculate the total for the Avg sales per emp item, you want to apply the calculation to the totals for the Sales SUM and No. of employees items. In other words, the intended total value for the Avg sales per emp item is 11,428 (i.e. 400,000/35).

Note: If you used Cell SUM in this example, you would sum the Avg sales per emp item column. This would result in the unintended total value 36,666 (i.e. 10,000 + 20,000 + 6,666).