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 (r, 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).