Create a 4-condition IF statement to display a certain value in a cell

Question

I'm trying to do something which I hope will be reasonably straightforward in Microsoft Excel. I'm using 2010.

I have two columns called Username and Year Group.

Username takes the format of something like, 11rennem01 or 10oxbech1. Year Group is just a single digit figure, one of 5/6/7/8.

In a third column, I would like to display the students' shared drives, which take the following format: \\server\student\IntakeYear\Username`. For example, the aforementioned students' paths will be:

(Year 5) 11rennem01 -> \\server\student\Intake2011\11rennem01
(Year 6) 10oxbech1 -> \\server\student\Intake2010\10oxbech1
(Year 7) 09clarco01 -> \\server\student\Intake2009\09clarco01
(Year 8) 08wilkbe01 -> \\server\student\Intake2008\08wilkbe01

Unfortunately, despite the numbers 11 and 10 at the start of the students' usernames matching up, there's a possibility, with a few students, that the number at the start of their username will be different to their Intake Year. As such, the only reliable value is the Year Group figure in the second column.

How can I go about writing such an IF statement?

Thanks in advance,

Answer 1

Assuming cell A1=Username, cell B1=Year Group, then in cell C1 you should write:

="\\server\student\Intake"&IF(B1=5;"2011";IF(B1=6;"2010";IF(B1=7;"2009";"2008")))&"\"&A1

and then copy cell C1 into as many cells of row C as you need

Answer 2

In column three enter the following code:

="\\server\student\Intake"&2016-B7&"\"&A7

Your output will be:

Username    Year Group  SharedDrive

11rennem01  5          \\server\student\Intake2011\11rennem01

Answer 3

change it from IF, which tends to look horrible once you nest a few.

As you have a small start point, I would use CHOOSE. In your case, use
=CHOOSE(A2,"Invalid","Invalid","Invalid","Invalid","2008","2009","2010","2011")
to construct the directory

Answer 4

I'd use either the LOOKUP function with a small table mapping Intake Years to share folders (http://support.microsoft.com/kb/324986), or a big IF like this:

=IF(A2 = "Year 5", "Intake2011", IF(A2 = "Year 6", "Intake2010", IF(A2 = "Year 7", "Intake2009", "Intake2008")))

The lookup table solution is better though, in my opinion.