Rank is a financial function that provides a rank value for a value in a specified range.
Rank is processed by Financial Reporting and does not depend on the database connection.
Rank([Reference], Order, Unique)
The range of cells, rows, or columns to rank, with letters identifying columns and numbers identifying rows. For example, specify [A,1:5] to rank the values for rows 1 through 5 in column A.
You can use the .ifNN property with a range of cells to assign numbers to any cells with nonnumeric values so that those cells can be ranked. For example, you can use .ifNN(-1) to assign the value -1 to any cell with a missing value.
Indicates the order by which the values are ranked. The lowest value ranked in ascending order receives a rank result of 1. The largest value ranked in descending order receives a rank result of 1. The order can be indicated by any of the following keywords or values:
The keywords are not case-sensitive.
Do not enclose the number or keyword indicating order in quotation marks.
(Optional) A Boolean keyword indicating how to treat equal values in the Reference parameter where:
This formula in column B ranks the values in rows 1 through 5 in column A in descending order:
The result might be as follows:
When two values are equal, they receive the same rank value. In the example above, Cola and Beer have the same value and therefore the same rank.
This formula in column B assigns the value of -1 to any nonnumeric value so it can be ranked:
In the following result, the missing value now has a rank of 5:
The following example builds on the previous example explaining how the new "unique" parameter affects the results:
This formula in column B assigns the value of -1 to any nonnumeric value so it can be ranked, and also indicates that each ranking should be unique:
Rank([A,1:5].ifNN(-1), descending, true)
In the following result, the missing value now has a rank of 5, and Beer has a value of 3 (even though it has the same data value as Cola):