May 18 2021 09:58 AM
Hey so I'm trying to create a column where depending on the amount of points a column would display
Level 1, Level 2, Level 3
The point are distributed
Level 1 (0 - 219)
Level 2 (220 - 339)
Level 3 (340 - 480)
I've tried using =IF(AND(C2>=1,C2<-219"Level 1") but it's not executing.
Please help
@amit_bhola @Sergei Baklan ( i tagged you both because you gave really detailed answers on other issues)
May 18 2021 10:05 AM
=LOOKUP(C2,{0,220,340},{"Level 1","Level 2","Level 3"})
Or create a lookup table:
and use =VLOOKUP(C2,$J$2:$K$4,2)
May 19 2021 03:38 AM
May 19 2021 04:02 AM
The value in J4 in the screenshot should have been 340 instead of 240, but the formula should work.
See the attached sample workbook.
May 21 2021 11:41 AM
@Ade_On360 Hi, just for the academic purpose, if it is to be done using IF and AND, then the solution is as below/attached. Take care to close the bracket for AND function within IF function. When a function is used within another function, its bracket needs to be closed each time.
And yes, the lookup table method using VLOOKUP is recommended instead of IF function for no. of levels beyond two. Lookup method is easy to understand and it is also easy to manage the change of level defining numbers by just typing the new values into the lookup table cells.
=IF(AND(C2>=0,C2<220),"Level 1",IF(AND(C2>=220,C2<340),"Level2",IF(AND(C2>=340,C2<=480),"Level3","Level not defined")))