Knowledge Base

XL7: PivotTable Calculated Data Source Error

PSS ID Number: 151692

Article Last Modified on 2/19/2002

The information in this article applies to:

This article was previously published under Q151692

SYMPTOMS

Under certain circumstances, you may receive incorrect values in the Subtotal and Grand Total cells in PivotTables that you create in Microsoft Excel 7.0 and 7.0a.

MORE INFORMATION

Only PivotTables that you create under the following circumstances are affected. Note that all five conditions must be true in order for the error to occur:

  1. You create the PivotTables in Microsoft Excel, version 7.0 or 7.0a Microsoft Excel 5.0 for Windows, and Microsoft Excel 5.0 for Macintosh are not affected.
  2. You create the Pivot Table from source data that is stored in Microsoft Excel, as opposed to data drawn from an external source, such as a database.
  3. The source data must include formulas or functions. This situation is most common when a field in the source data is calculated from other fields. For example, the field "% Profit" is calculated using the formula "=(Revenue-Cost)/Revenue", where Revenue and Cost are fields in the source data.
  4. A formula or function in the source data must return an error value, such as #VALUE!, #NUM!, #NAME?, #DIV/0!, and so on.
  5. The only operation that you use to summarize Pivot Table data fields is SUM. You do not select any other summary operation, such as COUNT or AVERAGE, in the PivotTable Field dialog box.

WORKAROUND

There are several methods you can use to work around this behavior.

Method 1

Remove the error values from your source data. You can remove the error values by correcting your data to remove the errors, or by using a function that checks for error values. The following is an example of a function that checks for error values.

Type the following function in every cell in your source data that could return an error: 
   =IF(ISERROR(Your Function),"NA",Your Function)
where Your Function is the formula or function you are using in your source data. For example, type the following formula in cell C2 for a formula =A2/B2 located in cell C2: 
   =IF(ISERROR(A2/B2),"NA",A2/B2)
If the formula is valid, the proper value will be returned. If the formula results in an error, instead of returning an error message, the function returns "NA." Note that "NA" is a placeholder message that you can change to meet your needs.

Method 2

If you have only one Pivot Table field in one dimension (there is only 1 row field or only 1 column field in your table), using the right mouse button (right-click), click that field and click the Pivot Table Field command. In the Subtotals category of the dialog box, click to select Count. This option will correct the error, and will not change the appearance of your PivotTable.

Method 3

If you have multiple fields, right-click the innermost row field (the field name that appears furthest to the right in the list of row fields), and click the Pivot Table Field command. In the Subtotals category of the dialog box, enable Count. This option will correct the error, and will add several rows that display data counts for your data at the bottom of the PivotTable. You can hide these rows prior to printing by using the Hide Rows command.

Additional query words: 7.00a pvtpatch

Keywords: KB151692
Technology: kbExcel95 kbExcel95a kbExcel95Search kbExcelSearch kbExcelWinSearch


Send feedback to Microsoft

© 2004 Microsoft Corporation. All rights reserved.