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.

COLUMN(range)

The closest Analytica equivalent is:

@[ColumnIndex = range]

Excel's COLUMN function is somewhat specific to the spreadsheet paradigm, the an exact equivalent is not entirely applicable. But the position operator shown here is a close analogue. In Excel, a range identifies a cell or group of cells. In Analytica, an index label identifies a position along an index, and an array containing index labels identifies a group of positions. Thus, if range is a label, or an array of labels, appearing in the «ColumnIndex», then @[ColumnIndex = range] returns the column position of that range.

COLUMNS(array)

Analytica equivalents:

Size(ColumnIndex)

Sum(1, ColumnIndex)

The block of data referenced by the array' cell references in an Excel spreadsheet will typically be stored in a single variable in your Analytica model. Excel's Column function is basically asking how long that column index is. The two above forms are equivalent.

GETPIVOTDATA(data_field, pivot_table, field1, item1, field2, item2,...)

Analytica equivalent:

A[field1 = item1, field2 = item2,...]

where A is the multi-dimensional array in your Analytica model that corresponds to the «data_field» of the pivot_table in Excel. The «data_field» may not be relevant in your Analytica case, or it may correspond to a particular slice of a report along a particular index, in which case it becomes one additional field that you slice over.

When certain «data_field»s within a pivot table are accessed using GetPivotData function in Excel, you may get the grand total over a particular index. In this case, you would need to apply the Sum function in your Analytica model as appropriate.

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(link_location, friendly_name)

Hyperlinks in your Analytica document should appear in attributes such as Description, Help, and user-defined attributes, rather than in expressions or definition attributes. So, instead of using an Analytica function for this, the equivalent is to place the following text within the description attribute of your variable:

<a href="link_location">friendly_name</a>

From edit mode, you'll see this HTML-like syntax, but from browse mode, you'll see «friendly_name» underlined. Clicking on it will open the URL or external document (e.g., word file) in a separate web browser or application window. For example:

<a href="http://www.lumina.com">Lumina's Web Site</a>

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 Analytica. The areas in Excel are slices along the third dimension in the Analytica array. Thus, the equivalent is (where the array is called A):

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

INDIRECT(ref_text, a1)

There are a couple Analytica equivalents, depending specifically on what you are doing.

In one variation of this, you have a variable, h, that contains a handle to another variable, say A. Using h, you want to access the value of A. Hence, you are getting the value of A indirectly. This equivalent takes this form:

Variable h := Handle(A) { Or the handle may be derived from some computation }

Then to access the value:

Evaluate(h)

A second variation is where you have the name of the variable as a text string. Here you can use Evaluate directly:

Evaluate("A")

This usage entails some perils to watch out for -- in particular, when used in this fashion, Analytica's dependency maintenance mechanism does not know that your result depended on A. As a result, should the value of A change in the future, Analytica would not know that your result needs to be recomputed, and it would not get invalidated.

A third variation is a hybrid of the above two:

Evaluate(HandleFromIdentifier("A"))

Again, Analytica does not record the dependency from A to the result. The intermediate value here is a handle, obtained from the text identifier.

LOOKUP(lookup_value, lookup_vector, result_vector)

Analytica equivalent:

StepInterp(lookup_vector, result_vector, lookup_value, I, LeftLookup: true)

where I is the index common to «lookup_vector» and «result_vector».

LOOKUP(lookup_value, array)

This is a strange variation of Lookup in excel, where the search occurs along whichever dimension of array is the longest. If array has more rows than columns, then the search occurs vertically.

When you translate this to Analytica, you will most likely know which dimension the lookup should occur over. Suppose array is indexed by I and J, and you know the lookup should occur along the I index. Then the equivalent is:

StepInterp(I, array[@J = size(J)], lookup_value, I, LeftLookup: true)

MATCH(lookup_value, lookup_array, match_type)

Analytica equivalents when match_type is omitted or -1 are:

StepInterp(lookup_array, @I, lookup_value, I, LeftLookup: True)

CondMax(@I, lookup_array <= lookup_value, I)

Max(If lookup_array <= lookup_value Then @I Else Null, I)

Analytica equivalent when match_type is 0:

CondMin(@I, lookup_array = lookup_value, I)

Min(If lookup_array = lookup_value Then @I Else Null, I)

this is also an equivalent when there are no duplicates:

PositionInIndex(lookup_array, lookup_value, I)

Analytica equivalents when match_type is 1:

StepInterp(lookup_array, @I, lookup_value, I)

CondMin(@I, lookup_array >= lookup_value, I )

Min(If lookup_array >= lookup_value Then @I Else Null, I)

In the «match_type»<>0 cases, Excel assumes lookup_array to be in ascending order in the -1 case, or in descending order in the 1 case. If this is not the case, the StepInterp Analytica equivalents report an error, while Excel returns a somewhat arbitrary value. It is hard to know what Excel is actually doing in that case, hence it is hard to specify an Analytica equivalent for those cases.

When no value in the array matches, Excel returns #N/A. The different Analytica equivalents shows here differ in what is returned in that case. For example, in the -1 case, StepInterp yields 1 or size(I) depending on whether lookup_value is smaller than the smallest, or larger than the largest value in the array. CondMax returns -INF, and Max returns Null. This behavior when the value is not found is one reason to prefer one Analytica equivalent over another, depending on what would be most convenient in your case.

OFFSET(range, rows, cols, height, width)

This function is very specific to the spreadsheet paradigm and has no direct equivalent in Analytica. Generally, however, what Offset is used for is to extract a subset of an array that can then be operated on. Suppose range is a 2-D array, indexed by I and J, and you wish to extract a certain sub-rectangle of data, say from I = 7..12 and J = 8..14 (the full range of I and J might be 1..100 for example). You would thus do something like this:

 Index I2 := 7..12;
 Index J2 := 8..14;
 Variable subrange := range[I=I2,J=J2];
 { Do something with subrange, which now a 2-D array, for example: }
 Sum( Max( subrange,I2 ), J2 )

ROW

The closest Analytica equivalent is:

@[RowIndex = range]

Excel's ROW function is somewhat specific to the spreadsheet paradigm, the an exact equivalent is not entirely applicable. But the position operator shown here is a close analogue. In Excel, a range identifies a cell or group of cells. In Analytica, an index label identifies a position along an index, and an array containing index labels identifies a group of positions. Thus, if range is a label, or an array of labels, appearing in the «RowIndex», then @[RowIndex = range] returns the row position of that range.

ROWS(array)

Analytica equivalents:

Size(RowIndex)

Sum(1,RowIndex)

The block of data referenced by the array' cell references in an Excel spreadsheet will typically be stored in a single variable in your Analytica model. Excel's Row function is basically asking how long that row index is. The two above forms are equivalent.

RTD

There is no Analytica equivalent at this time. To interface with the external program from Analytica, you will probably want to use RunConsoleProcess.

TRANSPOSE(array)

In many cases, perhaps most cases where Transpose is used in a spreadsheet, the operation can be skipped entirely in your Analytica model. This is because the dimensions of an array in Analytica are identified by their index, not by whether they are in the row or column position. Your expressions specify which dimension you are operating over, and that will automatically allow you to operate on the correct dimension without having to transpose.

Occasionally, in the case of a square matrix, you may need to transpose the matrix, which in Analytica terms means that you are re-indexing each index with the other. For that, the Analytica equivalent is:

Transpose(array, I, J)

where I and J are the indexes of «array» that are being swapped.

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 Analytica 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».

In Analytica:

Index Fruit ::= ['Apple','Banana','Cherry','Date','Eggplant','Fig','Grape']

Variable Fruit_prices ::= Table(Fruit)

Decision Item_to_lookup ::= Choice(Fruit, 3)

Variable Item_price ::= Fruit_prices [ Fruit = Item_to_lookup ]

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.

See Also

• Excel to Analytica Mappings