RANK

RANK(comparison_value, array, ascending=False)

Determines the rank of a value within an array, with support for ascending or descending order.

The RANK function searches for comparison_value within array and returns its rank as a 1-based integer. By default, ranks are assigned in descending order (largest value → rank 1). If ascending is True, ranks are assigned in ascending order (smallest value → rank 1).

Null values in the array are treated as positive infinity (i.e., larger than any non-null value). If comparison_value is not found in the array, the formula evaluates to an error value.

Parameters:

• comparison_value (Operand | bool | int | float | str) –

  The value to rank within the array. Must be a primitive (non-array) type compatible with the elements of array.

  Supported types:

  • BOOLEAN

  • INTEGER

  • DECIMAL

  • STRING

  • DATE

  • TIME

  • DATETIME

• array (Operand) –

  The array to search within. Must be an array of the same type as comparison_value.

  Supported types:

  • BOOLEAN_ARRAY

  • INTEGER_ARRAY

  • DECIMAL_ARRAY

  • STRING_ARRAY

  • DATE_ARRAY

  • TIME_ARRAY

  • DATETIME_ARRAY

• ascending (bool | Operand) –

  Optional boolean indicating whether to rank in ascending order (True) or descending order (False). Defaults to False. Only BOOLEAN values are accepted; otherwise, a ValueError is raised.

  Supported types:

  • BOOLEAN

Return type:

Formula

Returns:

A formula object that evaluates to an INTEGER representing the 1-based rank of comparison_value within array.

If comparison_value is not found, the formula evaluates to an error value.

Supported types:

• INTEGER

Raises:

• ValueError – If array is not an array type

• ValueError – If comparison_value type does not match the element type of array

• ValueError – If ascending is not a BOOLEAN

Examples

Basic usage in descending order:

RANK(3, [1, 4, 3, 3, 2])
# Returns 2
# Explanation: In descending order → [4, 3, 3, 2, 1]
# The first 3 appears at position 2 → rank = 2

Ranking a value not at the extremes:

RANK(2, [1, 4, 3, 3, 2])
# Returns 4
# Explanation: In descending order → [4, 3, 3, 2, 1]
# 2 appears at position 4 → rank = 4

Using ascending order:

RANK("B", ["A", "D", "B", "C"], ascending=True)
# Returns 2
# Explanation: In ascending order → ["A", "B", "C", "D"]
# "B" appears at position 2 → rank = 2

With boolean values:

RANK(True, [False, True, True])
# Returns 1
# Explanation: In descending order → [True, True, False]
# True is ranked highest → rank = 1

Value not found in array:

RANK(10, [1, 2, 3])
# Evaluates to #ERROR