This chapter is from the book
This chapter is from the book
This chapter is from the book
Changing Summary Calculations
When creating your pivot table report, Excel, by default, summarizes your data by either counting or summing the items. Instead of Sum or Count, you might want to choose functions such as Min, Max, and Count Numeric. In all, 11 options are available. However, the common reason to change a summary calculation is that Excel incorrectly chose to count instead of sum your data.
Understanding Why One Blank Cell Causes a Count
If all the cells in a column contain numeric data, Excel chooses to sum. If just one cell is either blank or contains text, Excel chooses to count.
In Figure 3.25, the worksheet contains more than 60,000 numeric entries in column N and a single blank cell in N2. The one blank cell is enough to cause Excel to count the data instead of summing.
)
Figure 3.25 The single blank cell in N2 causes problems in the default pivot table.
In Excel 2007, the first clue that you have a problem appears when you click the check box for Revenue in the Fields section of the PivotTable Field List. If Excel moves the Revenue field to the Row Labels drop zone, you know that Excel considers the field to be text instead of numeric.
Be vigilant while dragging fields into the Values drop zone. If a calculation appears to be dramatically too low, check to see whether the field name reads Count of Revenue instead of Sum of Revenue. When you created the pivot table in Figure 3.26, you should have noticed that your company had only $68,613 in revenue instead of $10 million. This should be a hint to notice that the heading in B3 reads Count of Revenue instead of Sum of Revenue. In fact, 68,613 is the number of records in the dataset.
)
Figure 3.26 Your revenue numbers look anemic. Notice in cell B3 that Excel chose to count instead of sum the revenue. This often happens if you inadvertently have one blank cell in your Revenue column.
You can easily override the incorrect Count calculation. Activate the Data Field Settings dialog box by double-clicking on Count of Revenue and then change the Summarize Value Field By setting from Count to Sum, as shown in Figure 3.27.
)
Figure 3.27 Change the function from Count to Sum in the Data Field Settings dialog box.
Using Functions Other Than Count or Sum
Excel offers a total of 11 functions in the Summarize By section of the PivotTable Field dialog box. The options available are as follows:
- Sum—Provides a total of all numeric data.
- Count—Counts all cells, including numeric, text, and error cells. This is equivalent to the Excel function =COUNTA().
-
Average—Provides an average. Figure 3.28 shows a report detailing average sales per region and product line. An analyst might wonder why the average housekeeping sale in the West is $152 higher than in the Midwest.
Figure 3.28 Average sales per region and product line.
- Max—Shows the largest value.
- Min—Shows the smallest value.
- Product—Multiplies all the cells together. For example, if your dataset has cells with values of 3, 4, and 5, the product would be 60.
- Count Nums—Counts only the numeric cells. This is equivalent to the Excel function =COUNT().
-
StdDev and StdDevP—Calculate the standard deviation. Use StdDevP if your dataset contains the complete population. Use StdDev if your dataset contains a sample of the population. Figure 3.29 shows the results of two tests. Although the students averaged 87% on both tests, the math test had a higher standard deviation. Standard deviations explain how tightly results are grouped around the mean.
Figure 3.29 A low standard deviation on the science test means that all the students understand the concepts equally well. A higher standard deviation on the math test indicates that student scores were spread over a wider range.
- Var and VarP—Calculate the statistical variance. Use VarP if your data contains a complete population. If your data contains only a sampling of the complete population, use Var to estimate the variance.
)
)