How to write : IF X is > or < to..... but more complex

Question

So I'm running a small roleplay campaign with custom calculators. And knowing my friends, they don't want to do simple math, even if it's just 2+2. Problem is...I am bad with sheets/math myself

What I want to do:

• Every 5 levels, a player can increase their Mana OR Stamina, by + 4. (Base Mana/Stamina is 20)
• If Mana AND Stamina boxes are HIGHER than 20 at level 5, I will have a separate cell that states "You have used too many".
• If Mana AND Stamina boxes are LOWER than 24 at level 5, I will have the separate cell state "You have used too little" (do I have this right?)

My issue is...how would I do this for anything 10/15/20..etc. ? When at level 10+ the cells will be different depending on what they have allocating or not.

Is what I want too complex for sheets?

Answer 1

you can use IF:

=IF(AND(B2>=1;    B2<10);     "01-10"; 
 IF(AND(B2>=11;   B2<21);     "11-20"; 
 IF(AND(B2>=21;   B2<51);     "21-50"; 
 IF(AND(B2>=51;   B2<101);    "51-100";
 IF(AND(B2>=101;  B2<151);   "101-150"; 
 IF(AND(B2>=151;  B2<201);   "151-200"; 
 IF(AND(B2>=201;  B2<301);   "201-300";
 IF(AND(B2>=301;  B2<501);   "301-500"; 
 IF(AND(B2>=501;  B2<801);   "501-800"; 
 IF(AND(B2>=801;  B2<1001);  "801-1000";
 IF(AND(B2>=1001; B2<1223); "1001-1222"; 
 IF(AND(B2>=1223; B2<1569); "1223-1568"; 
 IF(AND(B2>=1569; B2<1800); "1569-1800";)))))))))))))

you can use IFS:

=IFERROR(ARRAYFORMULA(
 IFS(B2:B>=1569; "1569-1800";
     B2:B>=1223; "1223-1568";
     B2:B>=1001; "1001-1222"; 
     B2:B>=801;   "801-1000";
     B2:B>=501;   "501-800";
     B2:B>=301;   "301-500";
     B2:B>=201;   "201-300";
     B2:B>=151;   "151-200";
     B2:B>=101;   "101-150";
     B2:B>=51;     "51-100";
     B2:B>=21;     "21-50";
     B2:B>=11;     "11-20";
     B2:B>=1;      "01-10"));)

you can use VLOOKUP:

=IFERROR(ARRAYFORMULA(VLOOKUP(B2:B, {{1,      "01-10"  };
                                     {11,     "11-20"  };
                                     {21,     "21-50"  };
                                     {51,     "51-100" };
                                     {101,   "101-150" };
                                     {151,   "151-200" };
                                     {201,   "201-300" };
                                     {301,   "301-500" };
                                     {501,   "501-800" };
                                     {801,   "801-1000"};
                                     {1001, "1001-1222"};
                                     {1223, "1223-1568"};
                                     {1569, "1569-1800"}}, 2)), )

you can use IF on array:

=ARRAYFORMULA(IFERROR(
 IF((B2:B>=1)    * (B2:B<11),     "01-10", 
 IF((B2:B>=11)   * (B2:B<21),     "11-20", 
 IF((B2:B>=21)   * (B2:B<51),     "21-50", 
 IF((B2:B>=51)   * (B2:B<101),    "51-100",
 IF((B2:B>=101)  * (B2:B<151),   "101-150", 
 IF((B2:B>=151)  * (B2:B<201),   "151-200", 
 IF((B2:B>=201)  * (B2:B<301),   "201-300",
 IF((B2:B>=301)  * (B2:B<501),   "301-500", 
 IF((B2:B>=501)  * (B2:B<801),   "501-800", 
 IF((B2:B>=801)  * (B2:B<1001),  "801-1000",
 IF((B2:B>=1001) * (B2:B<1223), "1001-1222", 
 IF((B2:B>=1223) * (B2:B<1569), "1223-1568", 
 IF((B2:B>=1569) * (B2:B<1800), "1569-1800", ))))))))))))), ))

you can use CHOOSE:

=ARRAYFORMULA(IFERROR(CHOOSE(MATCH(B2:B, 
 {1, 11, 21, 51, 101, 151, 201, 301, 501, 801, 1001, 1223, 1569}), 
 "01-10",
 "11-20",
 "21-50",
 "51-100",
 "101-150",
 "151-200",
 "201-300",
 "301-500",
 "501-800",
 "801-1000",
 "1001-1222",
 "1223-1568",
 "1569-1800"), ))