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(ranges)

In Excel, this returns the number of multiple areas, for example:

AREAS( (A3:D10,F3:I10,K3:N10) ) → 3

A common situation where this would be used in a spreadsheet occurs when the data is really three-dimensional, organized as a series of 2-D tables. The third dimension is reflected as multiple areas. Thus, in Analytica, the ranges would really be a 3-D array, and the operation being replaced here is asking how many slices are in the third dimension. If the index for that third dimension in your Analytica model is named K, then Analytica equivalent is thus:

Size(K)

CHOOSE(index_num,value1,value2,...)

The most general Analytica equivalent is:

If index_num=1 Then value1
Else If index_num=2 Then value2
Else If index_num=3 Then value3
...
Else Null

When index_num is out-of-range, excel returns #VALUE!. Here we've used Null in place of Value (since #VALUE! does not exist in Analytica). Alternatively, you might replace Null with the Error function.

There are a several other very convenient methods for implementing the equivalent to Choose in Analytica that apply to specific cases. When value1, value2, ... all have the same dimensionality, a DetermTable provides this functionality, where value1, value2, ... are entered into each cell of the table. This organization often provides a very nice to use and transparent representation.

When the dimensionality of value1 is different from value2, etc., a DetermTable can still be used where references, e.g., \value1, are placed in each cell of the table. The users of that variable must de-reference the result. The webinar by Brian Parsonnet, viewable at Tariff Modeling.wmv, provides a very nice and powerful example of using this structure in a model.

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.

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 ]

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.