RankDense
The RankDense function assigns ranks to values in a specified column. Like Rank function output, the sequence begins with rank 1 and assigns the same rank to duplicate values. Unlike Rank function output, however, RankDense returns a continuous sequence that doesn't skip numbers after multiple values in the referenced column are assigned the same rank. As a result, there are no gaps in the ranking value sequence.
RankDense output assigns ranks based on date, time, numeric, alphabetic, or alphanumeric order, depending on the type of data in the referenced column.
Syntax
RankDense([column], [direction])
Function arguments:
- column (optional) - the column referenced to determine rank
-
direction (optional) - the directional order in which the referenced column's values are ranked
-
“asc”- ranks in ascending order (with the smallest value assigned rank 1) -
“desc”- ranks in descending order (with the largest value assigned rank 1)
-
If a direction argument isn't specified, the referenced column's values are ranked in ascending order by default. If neither argument is specified, the function assigns ascending ranks based on row number.
Example
A table lists the female name trends in Hawaii for the year 2018. You can use the RankDense function to rank names based on their popularity, which is reflected by their values in the Name Count column.
RankDense([Name Count], “desc”)
With [Name Count] as the column argument and "desc" as the direction argument, the name with the largest value in the Name Count column is assigned rank 1, the name with the next largest value is assigned rank 2, and so on.
After duplicate values are assigned the same rank, the ranking continues with the next chronological number, leaving no gaps in the ranking value sequence. For example, notice how the RankDense column assigns rank 4 to the names Mila and Olivia because they have the same Name Count value (50). The name Mia follows as the sixth value in the descending order of values, but it's assigned rank 5 to preserve the continuous sequence.