Referring to Cells in Calculations

How to: |

You can refer to columns and rows using a form of cell notation that identifies the intersection of a row and a column as ®, c).

Syntax: How to Use Cell Notation for Rows and Columns in a RECAP Expression

A row and column can be addressed in an expression by the notation:

E(r,c)

where:

- E
- Is a required constant.
- r
- Is the row number.
- c
- Is the column number. Use an asterisk (*) to indicate the current column.

Example: Referring to Columns Using Cell Notation in a RECAP Expression

In this request, two RECAP expressions derive VARIANCEs (EVAR and WVAR) by subtracting values in four columns (1, 2, 3, 4) in row three (PROFIT). These values are identified using cell notation (r,c).

```
TABLE FILE REGION
SUM E_ACTUAL E_BUDGET W_ACTUAL W_BUDGET
FOR ACCOUNT
3000 AS 'SALES' OVER
3100 AS 'COST' OVER
BAR OVER
RECAP PROFIT/I5C = R1 - R2; OVER
" " OVER
RECAP EVAR(1)/I5C = E(3,1) - E(3,2);
AS 'EAST--VARIANCE' OVER
RECAP WVAR(3)/I5C = E(3,3) - E(3,4);
AS 'WEST--VARIANCE'
END
```

The output is shown as follows.

E_ACTUAL E_BUDGET W_ACTUAL W_BUDGET -------- -------- -------- -------- SALES 6,000 4,934 7,222 7,056 COST 4,650 3,760 5,697 5,410 ------ ------ ------ ------ PROFIT 1,350 1,174 1,525 1,646 EAST--VARIANCE 176 WEST--VARIANCE -121

Note: In addition to illustrating cell notation, this example demonstrates the use of column numbering. Notice that the display of the EAST and WEST VARIANCEs in columns 1 and 3, respectively, are controlled by the numbers in parentheses in the request: EVAR (1) and WVAR (3).

Information Builders |