Returning an absolute value if the numbers in a cell are between ______ and ______.

[Helener]

I hope I can explain myself. I have a spreadsheet that lists room numbers in a hospital. The room numbers in this facility have corresponding unit numbers (i.e. rooms 103 to 133 are considered Unit A1, rooms 203 to 233 are considered Unit A2 and rooms 303 to 333 are Unit A3). My spreadsheet is set up with the Unit name in Column F and the Room number in Column G. The Unit number is blank, as my download from another program will list room number only and not Unit number. I would like the formula to reference the room number in column G and return the corresponding Unit Number in Column F. My problem is as follows, the Room Nmbers have letters next to them (i.e. Room 108D, 328P or 215A). Is there a way to have the formula return the absolute value of A1 for rooms 103-133 even if there is a letter next to some of the room numbers? Any help is greatly appreciated. Thanks.

 

[yytsunamiyy]

couple of questions:

- do all rooms have 3-digit numbers?
- do you only have 3 Units A1-Ax or are there also Units B1-Bx?
- how do Room numbers and Units corellate?

 

[Helener]

Thanks for your reply. All rooms have 3 digit numbers. There are 2 Buildings, Building A which houses Units A1, A2 and A3 and Building B which houses Units B1, B2, B3 and B4. Unit A's room numbers are listed below. Unit B's rooms are Rooms 140 to 168 are on Unit B1, rooms 240 to 268 are on Unit B2, Rooms 340 to 368 are on Unit B3 and rooms 440 to 468 are on Unit B4. Does this help?

 

[T. Valko]

I hope I can explain myself. I have a spreadsheet that lists room numbers in a hospital. The room numbers in this facility have corresponding unit numbers (i.e. rooms 103 to 133 are considered Unit A1, rooms 203 to 233 are considered Unit A2 and rooms 303 to 333 are Unit A3). My spreadsheet is set up with the Unit name in Column F and the Room number in Column G. The Unit number is blank, as my download from another program will list room number only and not Unit number. I would like the formula to reference the room number in column G and return the corresponding Unit Number in Column F. My problem is as follows, the Room Nmbers have letters next to them (i.e. Room 108D, 328P or 215A). Is there a way to have the formula return the absolute value of A1 for rooms 103-133 even if there is a letter next to some of the room numbers? Any help is greatly appreciated. Thanks.

Try this...

="A"&MATCH(--LEFT(A1,3),{103,203,303})

 

[yytsunamiyy]

try:

Excel Workbook
	A	B
2	Unit	Room nr
3	A1	103
4	B1	156
5	A1	133
6	A1	128
7	A2	233
8	A2	203
9	B2	268
10	B2	240
Tabelle2

Cell Formulas
Range	Formula
A3	=IF(AND(VALUE(MID(B3,2,2))>=3,VALUE(MID(B3,2,2))<=33),"A","B")&LEFT(B3,1)
A4	=IF(AND(VALUE(MID(B4,2,2))>=3,VALUE(MID(B4,2,2))<=33),"A","B")&LEFT(B4,1)

 

[Helener]

Brilliant! It worked. Thanks to both of you. This saves me so much time.