The row, column, or cell argument identifies a row, column, or cell in a grid. The syntax is:
FunctionName(GridName.GridElement[segment(range)].Property)Table 35. Argument Components
The name of a grid. For example: Difference (grid1.row[5], grid2.row[5]) returns the difference of two rows on grid1 and grid2. Optional: If GridName is not specified, the default is the current grid where the formula is entered. | |
One of the following keywords: row, col, column, or cell. For example, Max(row[1], row[2], row[3]) returns the maximum value of three rows. The keyword row and column or col is optional. The cell reference requires that row and column segment IDs be specified. For example, cell[2, A] refers to the cell that is the intersection between row 2 and column A. The keyword cell is optional. For example, [5,B] refers to the cell which is the intersection between row 5 and column B. Cell references can use the [row, col] syntax or [col, row] syntax. Optional. If a GridElement is specified, letters represent columns and numbers represent rows; for example: Max ([1,A], [2,A], [3,A]) | |
A row, column, or cell reference number of a grid. For an expanded row or column, you must specify the segment. For example, row[2] addresses row segment 2. Segments are enclosed in square brackets [ ]. | |
The rows, columns, or cell that are expanded from the specified segment. If range is specified, the system calculates the formula using only the specified range. For example, row[2(3:5)] uses only the 3rd through 5th rows of expanded segment 2. Optional: When range is not provided, all expanded cells are used. | |
One of the following keywords: average, averageA, count, countA, max, min, product, and sum. The property is used to aggregate the expanded rows, columns, or cells specified. Hyperion recommends that property is not specified when a reference is an argument. By not specifying the property, the function calculates the reference in the most appropriate way. For example, the following expression returns the average of the cells within rows 1 and 2: Whereas the following example first calculates the average of row[1], then the average of row[2], adds these 2 results, then divides by 2: Average(row[1].average, row[2].average) The default property for a row, column, or cell reference which is not used as a function argument is the sum property. For example, the following expression’s default property is sum: row[2]. For more information on properties, see Property Arguments. |
Because segment is the only required part of a reference, the following references are the same:
AverageA and CountA include #missing and #error cells in the calculation. For example, if row 1 is a segment row that expands to Qtr1 = 100, Qtr2 = 200, Qtr3 = #missing, and Qtr4 = 400, the following function returns the value four (4):
row[1].CountA
All other functions exclude #missing data or #error cells. For example, the previous example of row 1 that expands to Qtr1 = 100, Qtr2 = 200, Qtr3 = #missing, and Qtr4 = 400, returns three in this example:
row[1].Count