fbpx
Search
Close this search box.

Conditional Rank, the easy way [quick tip]

Share

Facebook
Twitter
LinkedIn

Yesterday, my mate from across the ditch, Hui posted about conditional rank formula (RANKIFS) using awesome SUMPRODUCT.

Of course, not everyone can whip up a sumproduct formula like that. On a scale of One to Hui of Excel awesomeness, you would need to be at least an H to write sumproduct or countifs formulas shown in that post. So does it mean, you can’t conditional rank if you don’t know your X from L?

Don’t worry. We got you covered. You can still get your conditional ranks, without inception level array formulas. Simple, use pivot tables instead.

Two level (or multi-level ranking) with Pivot Tables

Let’s say you have data like this and you want to know what the department level rank of each student.

The process for creating these ranks is ridiculously simple. Just,

  1. Insert a pivot table from your data
  2. Add level and detail fields to row labels area (in this case, we shall add Department and Student to row labels area)
  3. Add field on which you want to rank to values area (ie Score)
  4. Right click on value field and go to “Show values as” > “Rank…”
  5. Set up base field as the lower level (ie Student in our case)
  6. Bingo, your ranking problem is sorted.

Here is a quick demo of how to use ranking feature in Pivot Tables.

Download Example Workbook:

Click here to download workbook with the few pivot table ranking scenarios. Check it out to learn more.

 

Do you use pivot tables for ranking?

I use them all the time for such complex, filter driven ranking in my reports. While we could use formulas to get similar results, I think Pivots shine when it comes to ranking, as they naturally resolve ties (without complex deduplication logic) and give results in short time. The only problem is we need to refresh them whenever there is a change in data. But it is a small price to pay for avoiding complex formulas.

What do you think?  Do you use pivot tables for ranking your data? Share your stories in the comments section.

Also, check out our Pivot Table Central for all things pivot, right from basics to ninja-level stuff.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

8 Responses to “Conditional Rank, the easy way [quick tip]”

  1. Andy Ferguson says:

    Another, equally easy, way is to use a helper column to deal with the conditions, then use the standard RANK formula.

    It's far less awesome than either a pivot table or a SUMPRODUCT / COUNTIFS formula, but it is easy, and everyone's sheet has room for an extra column or two....(or 16,000) 🙂

    • Hui... says:

      @Andy

      The problem with helper Columns is that you need a Column for every Criteria Combination to segregate each Criteria

      • Andy Ferguson says:

        ...or an IF(AND(...

        I take your point, but my experience is usually that my customer only wants to see the data ranked one way at a time, sometimes being able to change the basis at will (eg Period / YTD).

        Again - not claiming it's better!

  2. Hui... says:

    Ha Ha

    I actually hadn't even noticed Ian's Rank.
    Must be another Ian as I would be in Engineering anyway.

    Nice answer anyways Chandoo

    I really should use Pivot Tables more shouldn't I?

  3. Chihiro says:

    Neat solution! I tend to use RANKX in PowerPivot DAX to solve conditional rank.

    But this is quick and easy solution for all to use. 🙂

  4. Sahil says:

    I use RANKX as well for solving condition rank. I never knew this solution, Thanks 🙂

Leave a Reply