So I have a spread sheet that shows where people sit, and the section that would

Question

So I have a spread sheet that shows where people sit, and the section that would be located in. The desk location is split up by 3 sections. Example, 3.2.852 means they are located in building 3, second floor, desk number 852. We have sections for each person for evacuation purposes that is based on the number. so if they sit in desks 500-699 on the 3rd floor they will be taken to rally point 3.3B. If they are on the second floor of building 3 in desks 500-699 they will be taken to rally point 3.2B, and so on and so forth.

Here's what my sheet looks like

I have a formula right now that will auto fill the employee's section based on the floor number and cube number they sit in, but it only works for building 3 and only of there is no other text after the desk number. So for Q, it gives me the #VALUE! error.

=LOOKUP(--MID(D4,3,7),{1.1,1.2,1.3,1.5,2.1,2.3,2.5,2.7,3.1,3.3,3.5,3.7},{"3.1B 100-199","3.1BH 200-299","3.1A 300-499","3.1AH 500-699","3.2A 100-299","3.2AH 300-499","3.2B 500-699","3.2BH 700-899","3.3A 100-299","3.3AH 300-499","3.3B 500-699","3.3BH 700-899"})

I can organize building 3 this way just fine, but I also have to keep track of building 1 and building 4 and they only have one floor. whenever I plug the formula it gives me that last section right IF they were located in building 3. How or what can I do so my formula will recognize the building number and ignore any texts after the desk location????

Name Section Desk Ashley 1.1 1.1.998 John 3.1BH 200-299 3.1.220 Ali 3.3B 500-699 3.3.564 Q 3.2A 100-299 3.2.185 or Training Rooms Chris 4.1 4.1.578

Explanation / Answer

I'm not sure I fully understand, but I'll give this a shot.

* the building and floor info (1.1, 3.1, 3.3, 3.2, 4.1) should always be the prefix of your rally point. If this is correct, you'd start with =LEFT(D2,3) but that only works if you never have more than 9 floors or 9 buildings... if that isn't true, we'll revisit and use a different formula that will separate the string at the periods, and pull out each piece.

Then for the rally point, you want to do that based on the desk number. In your example, all of the desk numbers are between 100 and 999, e.g. 3 digits. We'll assume for the sake of this exercise that your documentation of desks 1-99 are still 3 digits, e.g. 001, 015, 089, etc. To get that number, use =VALUE(MID(D2,5,3))

The next question is whether all rally points are the same size- e.g. regardless of building or floor, you always want to group people into up to 300. This may not match your reality, because if you have 301 desks in building 5 floor 1, then you would be sending 300 people to rally point 5.1A, and only one person to rally point 5.1.B.... but again for the sake of the example, we'll assume all rally points are 300 desks.

Take the value from the last equation, and divide it by 300 and just grab the integer (whole number). Here is a full formula for the rally point number, including the last equation:

=INT((VALUE(MID(D2,5,3))-1)/300)+1

Basically, desks 1-300 will be given a value of 1, desks 301-600 will be given a value of 2, etc.

So the last step is to convert that value to a capital letter:

=CHAR(x+64)

full equation for the rally point letter:

=CHAR(INT((VALUE(MID(D2,5,3))-1)/300)+1+64)

Full equation for the whole ball of wax:

=VALUE(MID(D2,5,3)) & CHAR(INT((VALUE(MID(D2,5,3))-1)/300)+1+64)

Hth:-))

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.