Translated from <Excel 2007 BIBLE>
Perhaps the most confusing aspect of an External table is the calculation of field pairs. Many External table users simply avoid involving computing fields and items. However, once you understand how they work, they are not complicated.
First, some basic definitions
- A Calculated field: a new field created from other fields in External table. If the source of your external table is a thin working table, you can use a calculated field to add a new column to the table, or create a formula to execute a desired calculation. A Calculated field must be in the value area of the External table. You cannot use calculated fields in row and column tags or in the Report filter area.
- One Computing Project: A computing project uses the content of other projects within a field in External table. If the source of your external table is a thin working table, you can use a computing project to insert one or more rows, or use the values in other rows to write a formula. A computing project must be in the row label, column label, or report filter area of the External table. You cannot use a computing project in the value area.
The formula used to create calculated fields and calculate projects is not a standard Excel Formula. In other words, you cannot enter formulas in cells. You enter these formulas in a dialog box, and they are stored together with the External table data.