if and formula

[wereman]

I'm a teacher making a school timetable and my problem is that there are three teachers in Grade 1 and two classrooms which means that one is always free. How can I write a formula to determin which one is free.
If Monday first period has Ms White in class 1 and Ms Blue in class 2 and Ms Green is therefore free. The problem is further complicated by the fact that each teacher teaches two subjects and they teach it to both classes.

HELP!!!!

 

[wereman]

Dear RalphA

Thanks for your feedback. It's much easier to understand than the other post that I got (please HOTPEPPER, don't feel offended - not necessarily better, just simpler for my simple mind to understand)

Below see what I've done with your formuli. It's colour coded for you to keep track with what i'm trying to achieve. and for all intents, I have achieved it. But for one snag: the IF, AND and OR option only allows 7 options but as you can see, in Year 1, for example, not only do they need ENGLLISH, MATHS, SCIENCE, SPANISH and SOCIALES, there's also DRAMA, ART, MUSIC, IT, SPANISH HELPER, ENGLISH HELPER and at least two PHYSICAL EDUCATION options. Any ideas??<br>

timetable 2008.xls
	B	C	D	E	F	G	H	I	J	K
1	Monday		Tuesday		Wednesday		Thursday		Friday
2	Registration		Registration		Registration		Registration		Registration
3
4
5
6
7
8
9
10
11
12
13
14
2008 Master Timetable

<br><br><br>

timetable 2008.xls
	M	N	O	P	Q	R	S	T	U
1	Year 1			Year 2			Year 3
2	Susan M	Vicky	Gaby	Silvina S	Claudia M	Caro	Maggie	Liz	Alex
3	English	English	Spanish	Spanish	Spanish	English	English	English	Spanish
4	Maths	Maths	Sociales	Sociales	Sociales	Science	Science	Science	Sociales
5	Science	Science		Maths	Maths		Maths	Maths
6	Year 4			Year 5			Year 6
7	Moira	Silvina K	Claudia S	Eugenia	Araceli	John	Dougal	Daniela	Mabel
8	English	Maths	Spanish	Maths	Spanish	English	English	Maths	Spanish
9	Science		Sociales	Sociales		Science		Science	Sociales
10		Special teachers
11		Drama	Art	Music	IT	Sp Help	Eng Help	D-body
12		XXX	Paulina	Susan P	Bobby	Graciela	YYY	Melina
13	Physical Education
14	Pablo	****	Mariano	Diego	Pancho	Paula	Lorena	Cristina	Maru
2008 Master Timetable

 

[RalphA]

Wereman:

Hotpepper's solution is, undoubtedly, much better than mine, which makes use of nested IFs, something that is not usually accepted as good codeing practice by the programming community.

However, I, as you, am more confortable with the simpler and easier to understand code that uses IFs. And, I am happy to hear from you that you could take my formulas and achieve your goals...with the often expressed problem of "running out of IFs"!

The trick with neste IFs is this: Only seven nested IFs and one "parent" IF are allowed in one statement. But, you may concatenate up to thirty groups of these eight Ifs! Here's an example, using 12 total IFs:

=IF(A=1,A,IF(A=2,B,IF(A=3,C,IF(A=4,D,IF(A=5,E,IF(A=6,F,IF(A=7,G,IF(A=8,H,""))))))))&IF(A=9,I,IF(A=10,J,IF(A=11,K,IF(A=12,L,"")

Notice that if all the IFs in any group fail, it is imperitive to have the last result to be a blank, the "".

I hope this will suffice to allow you to complete your project successfully.

 

[wereman]

Dear RalpA
Thanks again for all your help. My project is coming along very nicely now. I have another (related) question:
Up until now most subjects are taught by single teachers which makes the allocation of time and place to each relatively easy (as your help proved). But now I have a situation in Grade 4 where both classes are taught at the same time by four teachers (Drama, Art, Music and IT - what I've called DAMIT). What I now want to do is write into my formula something like:
=if(A1="DAMIT",merge A1 with B1 and also concatenate A2 with B2 where the teacher names now read $AB$3 &" , " & $AC$3 &" , " & $AD$3 &" , "& $AE$3
Or something to that effect. Maybe I'll need a macro for this, right?

 

[RalphA]

Wereman:

I think that, as soon as a formula starts to get complicated, especially if due to repetive parts, it is probably time to look for a VBA solution. Me, I'm hardheaded (persistant, I call it), and think VBA is too complicated for me. So, I try to obtain a formula type solution; if it seems out of the question, I just give up, and try method of presenting the problem. Let me give this a try, for your four teachers, each of whom teach two subjects.

Calling the teachers t1, t2, t3, and t4, and the subjects, d, a, m, and i, let's begin by forming some tables:

aa1=t1: ab1=d:ac1=a
aa2=t2: ab2=a:ac2=m
aa3=t3: ab3=m:ac1=i
aa4=t4: ab4=i:ac2=d

After merging the groups of two consecutive cells, the formula to use would be something like this, for a the subjects in two consecutive cells, say, in cells B4 and B5:

=if(or(and(B4=$ab$1,B5=$ac$1),and(B4=$ab$2,B5=$ac$2),and(B4=$ab$3,B5=$ac$3),$aa$4,
if(or(and(B4=$ab$2,B5=$ac$2),and(B4=$ab$3,B5=$ac$3),and(B4=$ab$4,B5=$ac$4),$aa$1,
if(or(and(B4=$ab$3,B5=$ac$3),and(B4=$ab$4,B5=$ac$4),and(B4=$ab$1,B5=$ac$1),$aa$2,
if(or(and(B4=$ab$4,B5=$ac$4),and(B4=$ab$1,B5=$ac$1),andB4=$ab$2,B5=$ac$2),$aa$3,""))))

Note that one builds up the formula, writing the first and(), copying it three times, and overwriting what is necessary, then copying the first IF four times, and proceeding to overwrite the old values that change, which is fairly easy. Now, one can copy this formula across, in the merged cells. Done