XL7: PivotTable Calculated Data Source Error

ID: Q151692

The information in this article applies to:

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.

WORKAROUNDS

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.00 7.00a pvtpatch 

Keywords          : xlpivot 
Version           : 7.00 7.00a
Platform          : WINDOWS

Last Reviewed: May 5, 1997