Last year I started visiting some REDDIT Excel groups. A few weeks ago I saw an interesting Excel question and provided a simple two step solution.

 

The Question

“the smallest 12 values in column D become rank 1…NEXT 12 lowest averages become rank 2 etc”

 

 

Kevin’s Solution

Step1 RANK

My first step is to rank all of the individual average values: =RANK(D6,$D$6:$D$48,1)

I used a 1 in Rank’s 3rd argument so that the smallest number would be ranked as 1.

 

 

Step2 APPROXIMATE MATCH

Now we bin the rank value above using this =MATCH(F6,{1,13,25,37,49},TRUE)

There are two important things happening inside this MATCH function:

  1. lookup_array, 2nd argument, is array constant! Values are inside formula not in a range of cells
  2. match_type, 3rd argument, is TRUE. It’s an approximate match (not exact match). We are binning!

 

 

COMBINE STEPS INTO 1 FORMULA

We could easily combine the two steps above into only 1 formula

=MATCH(RANK(D6,$D$6:$D$48,1),{1,13,25,37,49},TRUE)

 

 

SUMMARY

Different Bin Sizes?

The original requirement was to have groups of 12. If the groups need to have different sizes then we can easily adjust the values in our array constant. For example {1,5,31,35,49}

Array Constant Isn’t Necessary

Also, it’s important to note that we don’t have to use an array constant. We could easily put our bin values in a range (see Solution 1c in my Excel file below)

 

 

Bart Titulaer’s Solution

Bart Titulaer’s solution is a better solution if the bin sizes remain constant!

=ROUNDUP(RANK(D8,$D$6:$D$48,1)/12,0)  

How does it work? RANK the value in cell D8 (608.33). Wrap it with ROUNDUP.

  1. Highlight RANK(D8,$D$6:$D$48,1) and press F9 key. You get 27.
  2. 27 divided by 12   27/12   gives us 2.25
  3. ROUNDUP function rounds 2.25 up to 3 !!

Brilliant! Thanks Bart for the solution!

 

 

Excel File & YouTube Video

Download my Excel file here. Watch my video here.

 

 

Reddit

The group where I found the question  https://www.reddit.com/r/learnexcel/

Related group https://www.reddit.com/r/excel/

 

 

About Me

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.

About 20 years ago I remember seeing a friend working on an Excel spreadsheet. It looked interesting. I thought to myself “What if I could learn more about Microsoft Excel than I know about Chess & Spanish?”

At the time it seemed like a distant possibility but I started learning and I’ve never looked back 🙂

I’m a Data Analyst at McKinsey & Company.

Check out my YouTube videos and my blog posts.

Post a comment