XL7: PivotTable Calculated Data Source Error


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.


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
  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.


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 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.


Id. de artículo: 151692 - Última revisión: 02/14/2017 - Revisión: 1

Microsoft Excel 95 Standard Edition, Microsoft Excel 95a