Excel to Analytica Mappings/Lookup Functions

ADDRESS(row_num,column_num,abs_num,a1,sheet_text)

Excel's Address function is very specific to the spreadsheet framework, producing a cell address. Analytica models do not have a concept of cell addresses, so this function is not applicable.

In an Analytica model, if you want to store the "address" of a datum in an array, you would record the index values that specify its coordinates. To some extent, the closest analogue would be:

Slice(I,row_num)

which returns the label for the given row. In an N-D array, you could conceptually construct an array with N elements, one for each dimension. Analytica's ArgMin and ArgMax functions do something along these lines to specify a coordinate when performing the ArgMin or ArgMax over more than one dimension.

AREAS

CHOOSE

COLUMN

COLUMNS

GETPIVOTDATA

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

Analytica equivalent (when range_lookup is omitted or false):

table_array [ ColumnIndex = lookup_value ]
table_array [ ColumnIndex = loopup_value, @RowIndex = row_index_num ]   

The Analytica equivalent of HLOOKUP and VLOOKUP is the subscript operator, A[I=x] . In Excel, your table_array is a 2-D array, but when you are doing the equivalent in Analytica, you may be working with a 1-D or a 2-D table array. With HLOOKUP, Excel conceptualizes your table as running horizontally, from left to right. Analytica doesn't have a notion of it being left-to-right or top-to-bottom, instead Analyica cares only about what index corresponds to that dimension -- in other words, what index are you searching along. The first form above is appropriate when your table array is 1-D, or when you want the entire slice of the table. The second form returns the row of interest. Note that Excel considers the column index to be row 1, so you may need to subtract one from the row_index_num.

When range_lookup is true (approximate match), the equivalents are:

StepInterp( ColumnIndex, table_array, lookup_value, ColumnIndex, LeftLookup:true )
StepInterp( ColumnIndex, table_array[@RowIndex=row_index_num-1], lookup_value, ColumnIndex, LeftLookup:true )

This form finds the largest value in ColumnIndex that is less or equal to lookup_value. The first form is appropriate when your table_array 1-D, or when you want the entire slice for the value found. The second form is the more faithful equivalent.

HYPERLINK

INDEX(range,row_num,column_num)

Analytica equivalent:

range [ @RowIndex=row_num, @ColumnIndex = column_num ]

When the Index function is used in an Excel spreadsheet, the range parameter normally identifies an entire table somewhere on the spreadsheet. In Analytica, you should have such a table in its own variable (named range in the equivalent).

Excel has a second variation of the Index function, in which several ranges are specified. This usage in Excel looks like this:

Index( (area1,area2,area3,...), row_num, column_num, area_num )

For example:

Index( (A6:D10, F6:I10, K6:N10), 4, 3, 2 )

When this usage arises, you usually will have a 3-D array in Analyica. The areas in Excel are slices along the third dimension in the Analyica array. Thus, the equivalent is (where the array is called A):

A [ @RowIndex=row_num, @ColumnIndex=column_num, @AreaIndex=area_num ]

INDIRECT

LOOKUP

MATCH

OFFSET

ROW

ROWS

RTD

TRANSPOSE

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Analytica equivalent (when range_lookup is omitted or false):

table_array [ RowIndex = lookup_value ]
table_array [ RowIndex = loopup_value, @ColumnIndex = col_index_num ]   

The Analytica equivalent of HLOOKUP and VLOOKUP is the subscript operator, A[I=x] . In Excel, your table_array is a 2-D array, but when you are doing the equivalent in Analytica, you may be working with a 1-D or a 2-D table array. With HLOOKUP, Excel conceptualizes your table as running vertically, from top to bottom. Analytica doesn't have a notion of it being left-to-right or top-to-bottom, instead Analyica cares only about what index corresponds to that dimension -- in other words, what index are you searching along. The first form above is appropriate when your table array is 1-D, or when you want the entire slice of the table. The second form returns the row of interest. Note that Excel considers the row index to be column 1, so you may need to subtract one from the col_index_num.

When range_lookup is true (approximate match), the equivalents are:

StepInterp( RowIndex, table_array, lookup_value, RowIndex, LeftLookup:true )
StepInterp( RowIndex, table_array[@ColumnIndex=row_index_num-1], lookup_value, RowIndex, LeftLookup:true )

This form finds the largest value in RowIndex that is less or equal to lookup_value. The first form is appropriate when your table_array 1-D, or when you want the entire slice for the value found. The second form is the more faithful equivalent.

Comments


You are not allowed to post comments.