IFS, OR, AND Formula help

[NealGowdy11]

Good morning all,

Im am banging my head against a wall trying to get this formula to work.. i think i am over thinking it and the solution is probably easier than i am thinking.

In column A i have a list of guest rooms:

201
202
203
204
301
302
303
304
401
402
403
401

ETC...

In column B id like the value to read "Level 2" if the cell in column A's value is between 200-299, "Level 3" is the cell in column 's value is between 300-399 etc.

Hope that makes sense.

Thank you

 

[Saba Sabaratnam]

You could use this formula.

=CHOOSE(LEFT(A1,1)*1-1,"Level 2","Level 3","Level 4")

Kind regards

Saba

 

[Saba Sabaratnam]

Can you please update version(s) of Excel that you are using so that we can provide right solutions for your version of Excel?

Kind regards

Saba

 

[Alex Blakenburg]

If you know you are not going past 9 levels, then
="Level " & LEFT(A1,1)

 

[Opaquer]

If you know you are not going past 9 levels, then
="Level " & LEFT(A1,1)

Wouldn't you be able to expand on that for more than 9 levels just in case by going

="Level "&LEFT(A1,len(A1)-2)

Assuming the last 2 digits are always going to be the room number on the floor, and everything else is the level of the room, this should work indefinitely right?

 

[Alex Blakenburg]

Wouldn't you be able to expand on that for more than 9 levels just in case by going

="Level "&LEFT(A1,len(A1)-2)

Assuming the last 2 digits are always going to be the room number on the floor, and everything else is the level of the room, this should work indefinitely right?

Correct. I lost the debate I was having with myself on whether to edit my post and add that in 2 secs after I hit send.

 

[Opaquer]

Correct. I lost the debate I was having with myself on whether to edit my post and add that in 2 secs after I hit send.

All good! I'm still relatively new to the fancy stuff Excel can do and was also just seeing if I was right in my thinking or if I had missed something major :P

 

[Saba Sabaratnam]

You could use this formula to cater for 99 level

=LET(lev,LEN(A1),IFS(lev=3,"Level "&LEFT(A1,1),lev=4,"Level "&LEFT(A1,2)))

Kind regards

Saba