Supported Excel Functions

Logical

Lookup Functions

Math and Statistical

Rounding

Text

XLOOKUP

LAMBDA

Math and Statistical functions

Syntax

MAX(number1, [number2], ...)

number1 Required.
number2,... Optional.

Returns the largest value in a set of values.
This is the Adaptive Calculation Engine implementation of the Excel MAX function.

Example 1

Example 2

Syntax

MIN(number1, [number2], ...)

number1 Required.

number2,... Optional.

Returns the smallest value in a set of values.
This is the Adaptive Calculation Engine implementation of the Excel MIN function.

Example 1

Example 2

Syntax

AVERAGE(number1, [number2], ...)

number1 Required.
number2,... Optional.

Returns the average (arithmetic mean) of the arguments.
It takes into consideration only the present NUMERIC parameter values and requires that at least one such value is present to avoid a runtime #DIV/0! error.

This is the Adaptive Calculation Engine implementation of the Excel AVERAGE function.

Example 1

Example 2

Syntax

SUM(number1, [number2], ...)

number1 Required.
number2,... Optional.

Ads values.
This is the Adaptive Calculation Engine implementation of the Excel SUM function.

Example 1

Example 2

Syntax

SUMIF(range, criterion_operator, criterion_value, [sum_range])

range Required.
The range of compute fields to evaluate with the criterion_operator and criterion_value.

criterion_operator Required.
Can be one of the following: “>“; “<“; “> =“; “<“; “<=“; “<>“.

criterion_value Required.
Determines which fields will be counted.

sum_range Optional.
The range of compute fields, whose values will be summed .

Returns the sum of compute field values in a set, filtered by a single criterion.

If the criterion_operator equals = or <>, the wildcard characters can be used as the criterion_value: the question mark (?) and asterisk (*). A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

Note

If the sum_range argument is omitted, SUMIF will use the range argument instead. A compute fields range representing the range argument can be unordered in this case.

This is the Adaptive Calculation Engine implementation of the Excel SUMIF function.

Example 1

Example 2

Syntax

COUNT(value1, [value2], ...)

value1 Required.
value2,... Optional.

Counts the number of fields that contain numbers.
This is the Adaptive Calculation Engine implementation of the Excel COUNT function.

Example 1

Example 2

Syntax

COUNTA(value1, [value2], ...)

value1 Required.
value2,... Optional.

Counts the number of fields that are not empty.
This is the Adaptive Calculation Engine implementation of the Excel COUNTA function.

Example 1

Example 2

Syntax

COUNTBLANK(value1, [value2], ...)

value1 Required.
value2,... Optional.

Counts the number of fields that are empty.
This is the Adaptive Calculation Engine implementation of the Excel COUNTBLANK function.

Example 1

Example 2

Syntax

COUNTIF(range, criterion_operator, criterion_value)

rangeRequired.
The range of compute fields to count by evaluating their values with the criterion_operator and criterion_value.

criterion_operator Required,
Can be one of the following: “>“; “<“; “> =“; “<“; “<=“; “<>“.

criterion_value Required.
Determines which fields will be counted.

Returns the number of compute fields in a range, filtered by a single criterion.

Note

A compute fields range representing the range argument can be unordered in COUNTIF function.

This is the Adaptive Calculation Engine implementation of the Excel COUNTIF function.

Example 1

Example 2

Syntax

MAXIFS(max_range, criteria_range1, criterion_operator1, criterion_value1, [criteria_range2, criterion_operator2, criterion_value2], ...)

max_rangeRequired.
The range of compute fields from which the maximum will be determined.

criteria_range1Required.
The range of compute fields to evaluate with the criterion_operator1 and criterion_value1.

criterion_operator1Required,
Can be one of the following: “>“; “<“; “> =“; “<“; “<=“; “<>“.

criterion_value1Required.
Determines which fields will be compared.

criteria_range2, criterion_operator2, criterion_value2, ...Optional.
Additional evaluation ranges of compute fields and their associated criteria. Note that filtering will result in the intersection of these.

Returns the maximum value in a set of compute field values, filtered by a set of criteria.

Notes

MAXIFS will return 0 if none of the criteria are satisfied.
max_range and all of the criterion ranges must be the same size. If they are not, MAXIFS will return a #VALUE error as a display value.

This is the Adaptive Calculation Engine implementation of the Excel MAXIFS function.

Example 1

Example 2

Syntax

MINIFS(min_range, criteria_range1, criterion_operator1, criterion_value1, [criteria_range2, criterion_operator2, criterion_value2], ...)

min_rangeRequired.
The range of compute fields from which the minimum will be determined.

criteria_range1Required.
The range of compute fields to evaluate with the criterion_operator1 and criterion_value1.

criterion_operator1Required.
Can be one of the following: “>“; “<“; “> =“; “<“; “<=“; “<>“.

criterion_value1Required.
Determines which fields will be compared.

Returns the minimum value in a set of compute field values, filtered by a set of criteria.

Notes

MINIFS will return 0 if none of the criteria are satisfied.
min_range and all of the criterion ranges must be the same size. If they are not, MINIFS will return a #VALUE error as a display value.

This is the Adaptive Calculation Engine implementation of the Excel MINIFS function.

Example 1

Example 2

Syntax

SUMIFS(sum_range, criteria_range1, criterion_operator1, criterion_value1, [criteria_range2, criterion_operator2, criterion_value2], ...)

sum_rangeRequired.
The range of compute fields to be summed.

criteria_range1Required.
The range of compute fields to evaluate with the criterion_operator1 and criterion_value1.

criterion_operator1Required.
Can be one of the following: “>“; “<“; “> =“; “<“; “<=“; “<>“.

criterion_value1Required.
Determines which fields will be compared.

Returns the sum of compute field values in a set, filtered by a set of criteria.

Notes

SUMIFS will return 0 if none of the criteria are satisfied.
sum_range and all of the criterion ranges must be the same size. If they are not, SUMIFS will return a #VALUE error as a display value.

This is the Adaptive Calculation Engine implementation of the Excel SUMIFS function.

Example 1

Example 2

Syntax

AVERAGEIFS(avg_range, criteria_range1, criterion_operator1, criterion_value1, [criteria_range2, criterion_operator2, criterion_value2], ...)

avg_rangeRequired.
The range of compute fields to average.

criteria_range1Required.
The range of compute fields to evaluate with the criterion_operator1 and criterion_value1.

criterion_operator1Required.
Can be one of the following: “>“; “<“; “> =“; “<“; “<=“; “<>“.

criterion_value1Required.
Determines which fields will be compared.

Returns the average of compute field values in a set, filtered by a set of criteria.

Notes

AVERAGEIFS will return the #DIV/0! error value as a display value if none of the criteria are satisfied.
avg_range and all of the criterion ranges must be the same size. If they are not, AVERAGEIFS will return a #VALUE error as a display value.

This is the Adaptive Calculation Engine implementation of the Excel AVERAGEIFS function.

Example 1

Example 2

Syntax

COUNTIFS(criteria_range1, criterion_operator1, criterion_value1, [criteria_range2, criterion_operator2, criterion_value2], ...)

criteria_range1Required.
The range of compute fields to evaluate with the criterion_operator1 and criterion_value1.

criterion_operator1Required,
Can be one of the following: “>“; “<“; “> =“; “<“; “<=“; “<>“.

criterion_value1Required.
Determines which fields will be compared.

Returns the number of times all criteria are met.

Notes

COUNTIFS will return 0 if none of the criteria are satisfied.
criteria_range1 and all other criterion ranges must be the same size. If they are not, COUNTIFS will return a #VALUE error as a display value.
A compute fields range representing the criteria_range1 argument of COUNTIF can be unordered if the additional evaluation ranges are omitted.

This is the Adaptive Calculation Engine implementation of the Excel COUNTIFS function.

Example 1

Example 2

Syntax

MEDIAN(number1, [number2], ...)

number1 Required.
number2,... Optional.

Returns the median of the arguments. The median is the number in the middle of a set of numbers.

Notes

If there is an even number of numbers in the set, then MEDIAN calculates the average of the two numbers in the middle.
If the data set contains no duplicate data points, MEDIAN returns the #N/A error value.

This is the Adaptive Calculation Engine implementation of the Excel MEDIAN function.

Example 1

Example 2

Syntax

MODE.SNGL(number1, [number2], ...)

number1 Required.
number2,... Optional.

Returns the mode of the arguments as a single value. The mode is the most frequently occurring, or repetitive, value in a set of numbers.

Notes

If the data set contains no duplicate data points, MODE.SNGL returns the #N/A error value.

This is the Adaptive Calculation Engine implementation of the Excel MEDIAN function.

Example 1

Example 2

Syntax

RANK.EQ(value, range, [order])

valueRequired.
The value whose rank will be determined.
rangeRequired.
The range of compute fields to consider for ranking.
orderOptional.
A number specifying how to rank the value. If the order is 0 (zero) or ommited, RANK.EQ ranks the value as if the range was sorted in descending order. If the order is any nonzero value, RANK.EQ ranks the value as if the range was sorted in ascending order.

Returns the rank of a specified value in a dataset.
In a sorted list of numbers, the rank of the number equals its position.

Notes

RANK.EQ gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers sorted in ascending order, if the number 7 appears twice and has a rank of 5, then 8 would have a rank of 7 (no number would have a rank of 6).

This is the Adaptive Calculation Engine implementation of the Excel RANK.EQ function.

Example 1

Example 2

Syntax

RANK.AVG(value, range, [order])

valueRequired.
The value whose rank will be determined.
rangeRequired.
The range of compute fields to consider for ranking.
orderOptional.
A number specifying how to rank the value. If the order is 0 (zero) or ommited, RANK.AVG ranks the value as if the range was sorted in descending order. If the order is any nonzero value, RANK.AVG ranks the value as if the range was sorted in ascending order.

Returns the rank of a specified value in a dataset.
if more than one value has the same rank, the average rank is returned.

Notes

RANK.AVG gives duplicate numbers the average rank.

This is the Adaptive Calculation Engine implementation of the Excel RANK.AVG function.

Example 1

Example 2