Help with ranking based on criteria (RANKEQ, COLLECT)

curiouslearner

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!

Genevieve P.

Hi @curiouslearner

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