rank
Returns the rank of a value in a set of numbers.
The input can be a list or table region (e.g. a column).
When using the parameter defaults, a result of 1 means that the target value is the largest number in the set, a result of 2 means it's the second largest, and so on.
The optional parameters allow you to define the ranking order (ascending or descending), the ranking type (equal or average), and how to handle null or empty values.
Parameters
INPUT LIST (list or table region)
A list or table region of numbers.
VALUE (number)
The target value — i.e. the number to rank. This number must be in the input list/region.
ORDER (Boolean)
Optional. Whether to sort in descending order (true) or ascending order (false).
For descending, the largest value = rank 1; for ascending, the smallest value = rank 1.
Default: true
EQUAL RANK (Boolean)
Optional. Whether to use equal ranking (true) or average ranking (false).
This applies when multiple values in the set match the target value. Equal ranking returns the highest rank for those values. Average ranking takes the actual ranks of those values and returns an average.
Default: true
IGNORE NULLS (Boolean)
Optional. Whether to ignore null or empty values (true) or count them as a zero (false).
Default: true
Examples
If the optional parameters are unspecified, the default values apply.
ATL in Script | Result | Notes |
|---|---|---|
| 2 | 5 = second largest number. |
Set the third parameter to false to change the ranking order to ascending.
ATL in Script | Result | Notes |
|---|---|---|
| 3 | 5 = third smallest number. |
Set the fourth parameter to false to apply average ranking.
ATL in Script | Result | Notes |
|---|---|---|
| 1 | Equal ranking applies (default). |
| 1.5 | Average ranking applies. |
Note
The target value (4) appears twice in the input set. These two 4s are ranked 1 and 2, so the function calculates the sum total of the rankings and returns the average.
Set the fifth parameter to false to count null/empty values as zeros.
ATL in Script | Result | Notes |
|---|---|---|
| 1 | The null is ignored. |
| 2 | The null is counted as zero. |
Using rank with table data
Assume a "Describe Row in Context" project with this data:
Branch | Sales | |
|---|---|---|
Row 1 | Boston | 807,284.86 |
Row 2 | New York | 1,204,851.24 |
Row 3 | Philadelphia | 1,029,563.48 |
Row 4 | Baltimore | 756,912.75 |
Here's how you might use rank with this data:
[[FocusRowName]] ranked [[numToWords(rank(SalesColumn, Sales), 'ordinal')]] for sales.
Note
SalesColumn returns a column of Sales values, whereas Sales returns the Sales value for the focus row. The rank function returns a number, which is converted using numToWords.
The output text for Row 1:
Boston ranked third for sales.
The output text for Row 2:
New York ranked first for sales.
