Ever see one of those ranking lists and wonder how to figure out how to calculate how many points are needed to reach the next level? Here's how.
Example: VBA Express
I'll take a simple example: the list of rankings from VBA Express.
Note that I've simplified their ranking system because most applications only have one criteria to determine rank. However, we will look at the actual ranking in the next post.
If you want to follow along, download the workbook:
The named ranges are as follows:
- Rank_Names = The list of ranking names
- CurrentValue = The cell containing the number of posts
- Rank_Values = The list of values that correspond to each rank
Current Ranking
Based on how many posts I have, I can easily calculate my current rank level:
=INDEX(Rank_Names,MATCH(CurrentValue,Rank_Values,1))
We use 1 for the MATCH function's match_type parameter, because we are looking for the value that is equal to or less than the lookup value. It's similar to using TRUE as the fourth parameter for VLOOKUP for things like looking up letter grades.
With a given value of 550, my ranking is "VBAX Expert". Now for the good part: how do I calculate the next level and how many posts I need to reach it?
The Next Level
I name the cell with my current rank "CurrentRank" (creative, no?). To get the next possible level I use this formula:
=INDEX(Rank_Names,MATCH(CurrentRank,Rank_Names,0)+1)
The MATCH function returns the relative position of my current ranking in the list of rankings. All I need to do is +1 this value to get the next ranking: VBAX Master.
Note that we use 0 for the MATCH function's match_type parameter because we know the current rank has to exist in the ranking list. (And before you say something: no, I didn't account for when the current rank is "VBAX Grand Master" which is the highest level.)
So how do I determine how many posts I need? I simply look up the value of the next rank and subtract my existing post total from it:
=INDEX(Rank_Values,MATCH(NextLevel,Rank_Names,0)) – CurrentValue
NextLevel is the named cell that shows the result of the previous formula.
In short, here's how we did it:
Current ranking level: =INDEX(Rank_Names,MATCH(CurrentValue,Rank_Values,1))
Next ranking level: =INDEX(Rank_Names,MATCH(CurrentRank,Rank_Names,0)+1)
Number of posts needed to achieve next rank: =INDEX(Rank_Values,MATCH(NextLevel,Rank_Names,0)) – CurrentValue
In the next post we'll look at the actual ranking system and see how we can calculate our current and next ranking levels.
About JP
Follow Me