RANK
RANK(value, list, [ascending])
Gives a value's place in a list, such as where a task's float stands among every task's. As in Excel, 1 is the largest unless ascending is TRUE, and tied values share a place, so two tasks tied for least float are both 1 and the next is 3.
Arguments
| Argument | What to give it |
|---|---|
| value | One value, usually this row's own field, like [Total float]. Blank gives blank. |
| list | The column to rank it in, like All[Total float]. Numbers, durations, dates or text, of the same type as value. Blanks are skipped. |
| ascending | Optional. TRUE makes 1 the smallest. Leave it out, or give FALSE, and 1 is the largest. |
Examples
Formula=RANK([Total float], All[Total float], TRUE)
1 for the task with the least total float in the schedule, 2 for the next, and so on.
Formula=RANK([Duration], SAME([Phase])[Duration])
1 for the longest task in its phase.
Formula=RANK([Finish date], All[Finish date]) <= 10
TRUE for the ten tasks that finish last. Dates are ranked by the day.
Formula on relationships=RANK([Lag], All[Lag])
On a relationship: 1 for the one with the longest lag.
Reach for something else when
| You want | Use |
|---|---|
| Where the value stands as a share, 0 to 1 | PERCENTRANK |
| The value at a given share of the list | PERCENTILE |
| The task in first place, not the place | ARGMIN, ARGMAX |
Common questions
What does "RANK compares a Number with a list of Duration" mean?
The value and the list are of different types. Unlike most places, a bare number isn't read as days here.
The editor refuses this=RANK(5, All[Duration])
Write the value as a duration, RANK(5d, All[Duration]), or rank this row's own field, RANK([Duration], All[Duration]).