- BY Kevin Lehrbass
- POSTED IN Gurus, Solutions
- WITH 0 COMMENTS
- PERMALINK
- STANDARD POST TYPE

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:

**lookup_array**, 2nd argument, is**array constant**! Values are inside formula not in a range of cells**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.

- Highlight
**RANK(D8,$D$6:$D$48,1)**and press F9 key. You get**27**. - 27 divided by 12
**27/12**gives us 2.25 - 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**.