DPL 9 Help

Selection Spreadsheet Functions

Selection Spreadsheet Functions

All of the arguments of these functions (x, y, z, s, and w) are expressions. Series and array subranges are allowed in any argument requiring a series or array (without subscripts).

DPLExcel (N/E means "no equivalent")Action
@choose(z,x,y,...)CHOOSE(z+l,x,y,...) The zth value in the list x,y,... After the first argument of @choose() is truncated to an integer, it must equal a number between 0 and the number of arguments minus 2. When it is 0, @choose(z,x,y,...) equals x. When it is 1, @choose(z,x,y...) equals y and so on.
@cols(s)COLUMNS(s) The number of columns in the series or array s. If the argument of @cols() is the name of a series, the function returns the number of elements in the series.
@hlookup(x,s,y)HLOOKUP(x,s,y+1) The element of the series or array s that is y rows below the element preceding the first element in the top row of s that is larger than x. If the second argument of @hlookup() is the name of a series, the last argument must be 0. When this occurs, the function returns the element preceding the first element of the series that is larger than the function's first argument. String lookups are for exact matches.
@index(s,x,y)N/E The element of the series or array s that is at the intersection of column x and row y
xlindex (s,x,y)INDEX(s, x+1, y+1) The element of the series or array s that is at the intersection of row x and column y. If x is 0 in Excel (-1 in DPL), the function refers to the entire column indicated by y, and vice versa.
xlmatch(x,s,y)MATCH(x,s,y) The position of element x in series or array s. If y is 1 or omitted, the function returns the value less than or equal to x (s must be sorted in ascending order). If y is 0, the function returns the first value that is exactly equal to x. If y is -1, the function returns the smallest value greater than or equal to x (s must be sorted in descending order).
@rows(s)ROW(s) The number of rows in the series or array s. If the argument of @rows() is the name of a series, the function returns the number 1.
xlsmall(array,k)SMALL(array,k) Returns the k-th smallest value in a data set. Use this function to return values with a particular relative standing in a data set.
xllarge(array,k)LARGE(array,k) Returns the k-th largest value in a data set. You can use this function to select a value based on its relative standing.
xlmedian(x,y,z,...)MEDIAN(x,y,z,...) Returns the median of the given numbers. The median is the number in the middle of a set of numbers; that is, half the numbers have values that are greater than the median, and half have values that are less.
@vlookup(x,s,y)VLOOKUP(x,s,y+1) The element of the series or array s that is y columns to the right of the element preceding the first element in the left column of s that is larger than x. If the second argument of @vlookup() is a series name, the last argument must be 0. When this occurs, the function returns the element preceding the first element of the series that is larger than the function's first argument. String lookups are for exact matches.

Notes: This information is subject to change. New functions are added from time to time. Contact technical support for the latest compatibility information.

Versions: DPL Professional, DPL Enterprise, DPL Portfolio

See Also

Spreadsheet Functions