Microsoft KB Archive/152984

= How to Use STDDEV or STDDEVP with a PivotTable =

Article ID: 152984

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q152984





SUMMARY
The PivotTable STDDEV or STDDEVP functions provide standard deviation statistics against data. STDDEV is best used when data is a sampling of the overall data. STDDEVP, on the other hand, is for use when the information included is all the data (entire population). The standard deviation functions are a measure of the dispersal of values from the average (the mean). The standard deviation functions provide a useful measure of the consistency of data and can ensure reliability in forecasting or trend analysis.

This article provides an example of using the STDDEVP function in a Pivot Table. The data is a fictional rendering of sales data for two quarters over a period of three years. The data is limited to two quarters in a year for the sake of keeping the sample small. The resulting PivotTable will return the deviation for each quarter based upon monthly sales revenue. This result will be returned with STDDEVP and further expanded upon by returning the percentage difference in comparison to the same quarter of the previous year.



MORE INFORMATION
The following steps provide an example:

  In a new Microsoft Excel worksheet, enter the following values:        A1: Year   B1: Quarter  C1: Month   D1: Net Revenue A2: 93    B2: 1        C2: Jan     D2: $514,731 A3: 93    B3: 1        C3: Feb     D3: $514,850 A4: 93    B4: 1        C4: Mar     D4: $515,816 A5: 93    B5: 2        C5: Apr     D5: $516,057 A6: 93    B6: 2        C6: May     D6: $516,920 A7: 93    B7: 2        C7: Jun     D7: $517,082 A8: 94    B8: 1        C8: Jan     D8: $520,945 A9: 94    B9: 1        C9: Feb     D9: $521,751 A10: 94   B10: 1       C10: Mar    D10: $522,098 A11: 94   B11: 2       C11: Apr    D11: $522,921 A12: 94   B12: 2       C12: May    D12: $523,324 A13: 94   B13: 2       C13: Jun    D13: $523,594 A14: 95   B14: 1       C14: Jan    D14: $527,254 A15: 95   B15: 1       C15: Feb    D15: $527,431 A16: 95   B16: 1       C16: Mar    D16: $528,162 A17: 95   B17: 2       C17: Apr    D17: $528,851 A18: 95   B18: 2       C18: May    D18: $528,996 A19: 95   B19: 2       C19: Jun    D19: $529,536  Click in any of the cells in the table and click PivotTable on the Data menu. In Microsoft Excel 97 or in Microsoft Excel 98 Macintosh Edition, click PivotTable Report on the Data menu. In Step 1 of 4 of the PivotTable Wizard, options are listed for the source of the PivotTable data. "Microsoft Excel list or database" should be the default option selected. Click Next. Step 2 of 4 displays the range of the data. Microsoft Excel may have the correct range already selected, $A$1:$D$19. If this is not the case, you can either type in the range, or you can use the pointer to select the range. Click Next. Step 3 of 4 displays the basic PivotTable structure and outside of the structure to the right, the field names from the data range: YEAR, QUARTER, MONTH, and NET REVENUE. Click the QUARTER field box and drag the field to the PivotTable section labeled ROW. Drag the YEAR field box to the PivotTable section labeled COLUMN.</li> Drag the NET REVENUE field to the PivotTable section labeled DATA. Again, drag the NET REVENUE field to the PivotTable section labeled DATA.

A SUM OF NET REVENUE and SUM OF NET REVENUE2 field box should be in the DATA section.</li> Move the pointer over the SUM OF NET REVENUE field box in the Pivot Table DATA section. Double-click the SUM OF NET REVENUE field box. This step opens the PivotTable Field dialog box.</li> In the Name box, replace SUM OF NET REVENUE with DEVIATION FROM PREVIOUS YEARS QUARTER. In the Summarize By list, click StdDevp. Click Options, and in the Show Data As list, click % Difference From. In the Base Field box, click Quarter, and in the Base Item box, click "(previous)."</li> Click Number. In the Decimal Places box, change the decimal places to 0 (zero), and then click OK.</li> In Step 3 of 4, double-click the SUM OF NET REVENUE2 field box. This step opens the PivotTable Field dialog box.</li> In the PivotTable Field dialog box, change the name of SUM OF NET REVENUE2 to QUARTERLY REVENUE, and then click OK.</li> In Step 3 of 4, click Next.</li> In Step 4 of 4, click Finish.</li></ol>

Analyzing the Results
The PivotTable now reflects the desired result. In this scenario, the STDDEVP is a reflection of the deviations from the average of revenue based upon the underlying monthly data of a quarter. The quarter reflects the whole revenue amount and not a sampling, hence the use of STDDEVP over STDDEV. The result is taken a step further by performing a comparison of deviations of the same quarter from a previous year, accomplished by using the % Difference From option. Microsoft Excel performs a STDDEVP function on all quarters. However, when using the % Difference From option, there is no previous quarter to the first quarter for comparison. Therefore, the first quarter of each year is blank.

When creating the PivotTable, there is a relatively easy aspect to determining what it will be displayed. In this PivotTable and all PivotTables, Step 3 of 4 in the PivotTable Wizard allows you to visualize the result. The page, row, and column fields will, by default, always present a single instance of every unique item in the data table field item supplied. The PivotTable Data Field will summarize the fields placed there by indexing the page, row, and column fields for the appropriate data.

<div class="references_section">

Microsoft Excel 7.0
For more information about PivotTable functions in Microsoft Excel, click Answer Wizard on the Help menu and type:

pivot table summary functions

Microsoft Excel 5.0
For more information about PivotTable functions in Microsoft Excel, click the Search button in Help and type:

Pivottable field command (data menu)

Additional query words: 5.00a 5.00c 97 XL97 8.00 98 XL98 XL

Keywords: kbhowto kbualink97 KB152984

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.