CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
R_life_R
R_life_R

Reputation: 816

Calculate VIP levels in googlesheets

I have to solve a problem of allocating the right VIP level to users according to their monthly performance. If points are achieved, a user is moved to a specific level. I reach this easily with the filter function. Now, to keep his status from previous month, user must at least achieve a certain amount of points. If he does not, he is downgraded to previous level.

Example:

The VIP levels are the following:

+---------+----------+------------+------------+-------------------+
| level # | Level    | Min Points | Max Points | Keep level Points |
+---------+----------+------------+------------+-------------------+
| 1       | BRONZE   | 0          | 1875       | 0                 |
+---------+----------+------------+------------+-------------------+
| 2       | SILVER   | 1875       | 9375       | 1875              |
+---------+----------+------------+------------+-------------------+
| 3       | GOLD     | 9375       | 37500      | 9375              |
+---------+----------+------------+------------+-------------------+
| 4       | PLATINUM | 37500      | 93750      | 37500             |
+---------+----------+------------+------------+-------------------+
| 5       | DIAMOND  | 93750      | 187500     | 93750             |
+---------+----------+------------+------------+-------------------+
| 6       | ASTEROID | 187500     | 562500     | 187500            |
+---------+----------+------------+------------+-------------------+

For example, if a user generates 40000 points in the month, he is promoted to PLATINUM. It also means that next month, if he does not reach a superior level (DIAMOND OR ASTEROID), he needs to generate at least 37500 points to keep his level, or he will be downgraded to GOLD.

Taking a specific user example, I could build a model to find the right level for each month but using several rows to iterate through the months. (googlesheet here)

It can be summarized by this result:

+-------+---------+---------+---------+---------+---------+---------+
| Month | 07-2019 | 08-2019 | 09-2019 | 10-2019 | 11-2019 | 12-2019 |
+-------+---------+---------+---------+---------+---------+---------+
| UserX | 1765    | 15408   | 1383    | 1499    | 9379    | 4161    |
+-------+---------+---------+---------+---------+---------+---------+
| Level | BRONZE  | GOLD    | SILVER  | BRONZE  | GOLD    | SILVER  |
+-------+---------+---------+---------+---------+---------+---------+

Now, having hundreds of users, I want to find the formula to calculate the right level for each month without using several rows to do so (1 row per user only)

Could anyone help to find out the solution?

Upvotes: 1

Views: 160

Answers (1)

player0
player0

Reputation: 1

paste where you need it and drag to the right:

=ARRAYFORMULA(IF($A4:$A9="",,IFNA(VLOOKUP(B4:B9, 
 {0,      "BRONZE"; 
  1875,   "SILVER";
  9375,   "GOLD";
  37500,  "PLATINUM";
  93750,  "DIAMOND";
  187500, "ASTEROID"}, 2, 1))))

0

spreadsheet demo

UPDATE:

for 1 rank down use above formula in B13 and in C13 paste this formula and drag to the right:

=ARRAYFORMULA(IFNA(VLOOKUP(IF(IF($A4:$A9="",,IFNA(VLOOKUP(C4:C9, 
 {0, 1; 1875, 2; 9375, 3; 37500, 4; 93750, 5; 187500, 6}, 2, 1)))+1< IF($A4:$A9="",,IFNA(VLOOKUP(B4:B9, 
 {0, 1; 1875, 2; 9375, 3; 37500, 4; 93750, 5; 187500, 6}, 2, 1))),   IF($A4:$A9="",,IFNA(VLOOKUP(B4:B9, 
 {0, 1; 1875, 2; 9375, 3; 37500, 4; 93750, 5; 187500, 6}, 2, 1)))-1, IF($A4:$A9="",,IFNA(VLOOKUP(C4:C9, 
 {0, 1; 1875, 2; 9375, 3; 37500, 4; 93750, 5; 187500, 6}, 2, 1)))), 
 {1, "BRONZE"; 
  2, "SILVER";
  3, "GOLD";
  4, "PLATINUM";
  5, "DIAMOND";
  6, "ASTEROID"}, 2, 0)))

0

spreadsheet demo

Upvotes: 2

Related Questions