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.
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 🙂
Check out my YouTube videos and my blog posts.