Microsoft KB Archive/213914

= HOW TO: Use STDEV or STDEVP with a PivotTable in Excel 2000 =

Article ID: 213914

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213914



For a Microsoft Excel 98 and earlier version of this article, see 152984.

IN THIS TASK
SUMMARY
 * Calculate Standard Deviation
 * Analyze the Results

REFERENCES



SUMMARY
This step-by-step article describes how to use the STDEV function and the STDEVP function with a PivotTable in Excel 2000.

The PivotTable STDEV and STDEVP functions in Microsoft Excel calculate standard deviation based upon a set of data. You use STDEV when the standard deviation is based upon a sampling of the total data. You use STDEVP when the standard deviation is based upon the entire population of data given as arguments.

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 improve reliability in forecasting or trend analysis.

This article provides an example of how to use the STDEVP function in a PivotTable. 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 returns the deviation for each quarter based upon monthly sales revenue. This result will be returned with STDEVP and further expanded upon by returning the percentage difference in comparison to the same quarter of the previous year.

back to the top

Calculate the Standard Deviation
To use the STDEVP worksheet function to calculate the standard deviation of a data set, follow these steps:   In a new Microsoft Excel worksheet, type the following values:    A1: Year   B1: Quarter  C1: Month   D1: Net Revenue A2: 98    B2: 1        C2: Jan     D2: $514,731 A3: 98    B3: 1        C3: Feb     D3: $514,850 A4: 98    B4: 1        C4: Mar     D4: $515,816 A5: 98    B5: 2        C5: Apr     D5: $516,057 A6: 98    B6: 2        C6: May     D6: $516,920 A7: 98    B7: 2        C7: Jun     D7: $517,082 A8: 99    B8: 1        C8: Jan     D8: $520,945 A9: 99    B9: 1        C9: Feb     D9: $521,751 A10: 99   B10: 1       C10: Mar    D10: $522,098 A11: 99   B11: 2       C11: Apr    D11: $522,921 A12: 99   B12: 2       C12: May    D12: $523,324 A13: 99   B13: 2       C13: Jun    D13: $523,594 A14: 00   B14: 1       C14: Jan    D14: $527,254 A15: 00   B15: 1       C15: Feb    D15: $527,431 A16: 00   B16: 1       C16: Mar    D16: $528,162 A17: 00   B17: 2       C17: Apr    D17: $528,851 A18: 00   B18: 2       C18: May    D18: $528,996 A19: 00   B19: 2       C19: Jun    D19: $529,536  Select any cell in the table. On the Data menu, click PivotTable and PivotChart Report. In step 1 of the wizard, accept the defaults, and then click Next. In step 2 of the wizard, ensure that the Range is $A$1:$D$19, and then click Next. In step 3 of the wizard, click Layout.</li> Drag the QUARTER field (button) to the PivotTable section labeled ROW.</li> Drag the YEAR field to the PivotTable section labeled COLUMN.</li> Drag the NET REVENUE field to the PivotTable section labeled DATA.</li> Again, drag the NET REVENUE field to the PivotTable section labeled DATA.

SUM OF NET REVENUE and SUM OF NET REVENUE2 field boxes appear in the DATA section.</li> Double-click the SUM OF NET REVENUE field box.

The PivotTable Field dialog box opens.</li> In the Name box, type DEVIATION FROM PREVIOUS YEARS QUARTER .</li> In the Summarize by list, click StdDevp. Click Options.</li> In the Show data as list, click % Difference From.</li> In the Base field box, click Quarter, and then click (previous) in the Base item box.</li> Click Number, type 0 in the Decimal places box, and then click OK twice.</li> Double-click the SUM OF NET REVENUE2 field box.

The PivotTable Field dialog box opens.</li> In the PivotTable Field dialog box, type QUARTERLY REVENUE in the Name box, and then click OK.</li> Click OK to return to step 3 of the wizard, and then click Finish. The PivotTable includes the standard deviation data.</li></ol>

back to the top

Analyze the Results
In this example, the STDEVP is a reflection of the deviations from the average of revenue based upon the underlying monthly data of a quarter. The data used includes all of the data, not just a sampling; this is why you use the STDEVP worksheet function instead of STDEV.

In this example, you compare deviations of the same quarter from a previous year by using the % Difference From option. Excel performs an STDEVP function on all quarters. However, when you use the % Difference From option, there is no previous quarter to compare to the first quarter. Therefore, the first quarter of each year is blank.

back to the top

<div class="references_section">