29 Jan 2018

## Video 00174 Ranking Values into Tiers in Excel

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)

### 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!

## Reddit

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

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