How'd They Do That: Points to Reach Next Level (Actual)

In How'd They Do That: Points to Reach Next Level (Simple) we reviewed a simple method for determining current ranking level, next ranking level, and the number of posts needed to reach that level.

In this post we'll go over the actual ranking system used at VBAX and how we can calculate current and next ranking level.

Here is the ranking system:

You'll notice that it contains two criteria for determining level: posts and articles. You need to meet both criteria in order to reach the stated level.

If you want to follow along, download the workbook:

Download sample workbook

Determine Current Ranking Level

Let's say I have 1,000 posts but only one article. My ranking can only be "Contributor" because even though I have enough posts for Master level, I only have enough articles for Contributor. Following me so far?

Because I have two criteria, calculating my current ranking is a three-step process. First, I need to determine my ranking based on number of posts. Then I need to determine my ranking based on number of articles. Finally, I need to take whichever is the lower of the two and look up my ranking.

Here is the entire formula:

=INDEX(Rank_Names,MIN(MATCH(INDEX(Rank_Names,MATCH(G11,Rank_Values,1)),
Rank_Names,0),MATCH(INDEX(Rank_Names,MATCH(H11,D3:D14,1)),Rank_Names,0)),0)

Let's reconstruct this formula as follows:

Rank based on number of posts:
=INDEX(Rank_Names,MATCH(G11,Rank_Values,1)) (returns "VBAX Master")

Rank based on number of articles:
=INDEX(Rank_Names,MATCH(H11,D3:D14,1)) (returns "VBAX Contributor")

Recall from the previous article that we used the MATCH function with a match_type parameter of 1 because we are looking for the value that is equal to or less than the lookup value.

Now we need the relative position of each ranking in the hierarchy, so we use the MATCH function to do so:

VBAX Master, number of posts position:
=MATCH(INDEX(Rank_Names,MATCH(G11,Rank_Values,1)),Rank_Names,0) (returns 8 )

VBAX Contributor, number of articles position:
=MATCH(INDEX(Rank_Names,MATCH(H11,D3:D14,1)),Rank_Names,0) (returns 4)

All we need to do at this point is take the minimum of the above two values, then return the corresponding rank at that position:

=MIN(MATCH(INDEX(Rank_Names,MATCH(G11,Rank_Values,1)),Rank_Names,0),
MATCH(INDEX(Rank_Names,MATCH(H11,D3:D14,1)),Rank_Names,0)) (returns 4)

Finally we look up the ranking level using the above to feed the INDEX function's second parameter:

=INDEX(Rank_Names,MIN(MATCH(INDEX(Rank_Names,MATCH(G11,Rank_Values,1)),
Rank_Names,0),MATCH(INDEX(Rank_Names,MATCH(H11,D3:D14,1)),Rank_Names,0)),0) (returns "VBAX Contributor")