Help with ranking based on criteria (RANKEQ, COLLECT)
Hello,
I am trying to rank location names by the number of activities they've accomplished within their specific region.
Dummy data:
When I searched the forum, I found a few examples of RANKEQ with COLLECT. However, I receive errors when I insert this:
=RANKEQ([Total Activities]@row, COLLECT([Total Activities]:[Total Activities], Region:Region, "Gulf Coast", 0))
Can you point me in the right direction? Thank you!
Best Answer
-
You're very close! It looks like you just have one of the closing parentheses in the wrong place.
This is the structure:
=RANKEQ(number to rank, range with numbers, order of rank)
We're replacing the middle range portion with a COLLECT function to filter the range, like so:
=RANKEQ(number to rank, COLLECT(...), order of rank)
Which spelled out becomes:
=RANKEQ(number to rank, COLLECT(range with numbers, criteria, "criteria"), order of rank)
In your formula, we're missing the closing ) for the Collect function, before moving on to the order. Now, the "Criteria" in the Collect function can be manually typed in, like you have, OR we could reference a cell, which is what I would suggest.
Try:
=RANKEQ([Total Activities]@row, COLLECT([Total Activities]:[Total Activities], Region:Region, Region@row), 0)
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
You're very close! It looks like you just have one of the closing parentheses in the wrong place.
This is the structure:
=RANKEQ(number to rank, range with numbers, order of rank)
We're replacing the middle range portion with a COLLECT function to filter the range, like so:
=RANKEQ(number to rank, COLLECT(...), order of rank)
Which spelled out becomes:
=RANKEQ(number to rank, COLLECT(range with numbers, criteria, "criteria"), order of rank)
In your formula, we're missing the closing ) for the Collect function, before moving on to the order. Now, the "Criteria" in the Collect function can be manually typed in, like you have, OR we could reference a cell, which is what I would suggest.
Try:
=RANKEQ([Total Activities]@row, COLLECT([Total Activities]:[Total Activities], Region:Region, Region@row), 0)
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you, thank you!
Help Article Resources
Categories
Check out the Formula Handbook template!