XL7: PivotTable Calculated Data Source Error

• Microsoft Excel for Windows 95, version 7.0, 7.0a

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)

   =IF(ISERROR(A2/B2),"NA",A2/B2)

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.