HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)Īnalytica equivalent (when «range_lookup» is omitted or false): table_array table_array In this case, you would need to apply the Sum function in your Analytica model as appropriate. 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. 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. Where A is the multi-dimensional array in your Analytica model that corresponds to the «data_field» of the pivot_table in Excel. GETPIVOTDATA(data_field, pivot_table, field1, item1, field2, item2.)Īnalytica equivalent: A Excel's Column function is basically asking how long that column index is. 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. Thus, if range is a label, or an array of labels, appearing in the «ColumnIndex», then = range] returns the column position of that range.Īnalytica equivalents: Size(ColumnIndex) Sum(1, ColumnIndex) In Analytica, an index label identifies a position along an index, and an array containing index labels identifies a group of positions. In Excel, a range identifies a cell or group of cells. But the position operator shown here is a close analogue. The closest Analytica equivalent = range]Įxcel's COLUMN function is somewhat specific to the spreadsheet paradigm, the an exact equivalent is not entirely applicable. The webinar by Brian Parsonnet, viewable at Tariff Modeling.wmv, provides a very nice and powerful example of using this structure in a model. The users of that variable must de-reference the result. 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. This organization often provides a very nice to use and transparent representation. '» are entered into each cell of the table. » all have the same dimensionality, a DetermTable provides this functionality, where «value1, value2. There are a several other very convenient methods for implementing the equivalent to CHOOSE in Analytica that apply to specific cases. Alternatively, you might replace Null with the Error function. Here we've used Null in place of Value (since #VALUE! does not exist in Analytica). When «index_num» is out-of-range, excel returns #VALUE!. The most general Analytica equivalent is: Size(K) CHOOSE(index_num, value1, value2.) If the index for that third dimension in your Analytica model is named K, then Analytica equivalent is thus: 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. The third dimension is reflected as multiple areas.
#Excel getpivotdata function true series
In Excel, this returns the number of multiple areas, for example:Ī 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. 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. In an N-D array, you could conceptually construct an array with N elements, one for each dimension. Which returns the label for the given row. To some extent, the closest analogue would be: 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. Analytica models do not have a concept of cell addresses, so this function is not applicable. 18 VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)ĪDDRESS(row_num, column_num, abs_num, a1, sheet_text)Įxcel's Address function is very specific to the spreadsheet framework, producing a cell address.13 OFFSET(range, rows, cols, height, width).12 MATCH(lookup_value, lookup_array, match_type).11 LOOKUP(lookup_value, lookup_vector, result_vector).8 HYPERLINK(link_location, friendly_name).7 HLOOKUP(lookup_value, table_array, row_index_num, range_lookup).6 GETPIVOTDATA(data_field, pivot_table, field1, item1, field2, item2.).1 ADDRESS(row_num, column_num, abs_num, a1, sheet_text).