Formula for calculating Student Advanced Standing

[taniab]

To be counted as having Advanced Standing, students must have grades of:
A's or B's and nothing lower
(so no C's, D's or E's).

AND
Their Behaviours must all be Oft (often) or Con (Consistent)
(So excluded if Behaviour contains som(Sometimes) or Sel (Seldom)

Students can study 3, 4 or 5 courses

How would use a count formula to include them if they meet all these criteria?

GRADES (Column J)	Behaviours (Colum K)
C C C B C	Oft Con Con Con Con
B C B B C	Con Con Con Con Con
B C B	Con Con Con Con Con
C C C	Con Con Con Con Con
A A A A	Con Con Con Con Con
C C C	Con Con Con Con Con
C C C	Con Con Con Con Con
B C C B C	Con Con Con Con Con
A B B C A	Con Con Con Con Con
C C C C C	Con Con Con Con Con
B C B	Con Con Con Con Con
A A A	Con Con Con Con Con
A A B	Con Con Con Con Con
B A B	Con Con Con Con Con
B B A B A	Con Con Con Con Con
C A B	Con Con Con Con Con
C C B	Oft Oft Oft Oft Oft
C C C A C	Con Con Oft Oft Oft
C B B C C	Oft Con Oft Oft Con
C C B	Con Con Con Con Con
B B B	Con Con Con Con Con
A B A B A	Oft Con Con Con Con
B A B B	Con Con Con Con Con
C C C C C	Oft Con Con Con Con
B C C C C	Con Con Con Con Con
C C C C C	Oft Con Oft Oft Oft
A C B	Con Con Con Con Con
A B A	Con Con Con Con Con
B C C C A	Con Con Con Con Con
B B B	Con Con Con Con Con
C B C	Con Con Con Con Con
C B C	Con Con Con Con Con
B C B	Con Con Con Con Con
B B B	Oft Oft Oft Oft Oft
C B B	Con Con Con Con Con
B B C B	Con Con Con Con Con
C B C	Oft Oft Oft Oft Oft
C A B A A	Oft Con Con Con Con
C C C	Con Con Con Con Con
B B B	Con Con Con Con Con
B B C	Con Con Con Con Con
C C B	Con Con Con Con Con
C A B	Som Som Som Som Som
A B A B B	Con Con Con Con Con
C C B C B	Oft Oft Som Oft Som
C C C C B	Oft Con Con Con Oft
C C C	Con Con Con Con Con

 

[mrshl9898]

=IF(AND(SUMPRODUCT(--ISNUMBER(SEARCH({"C","D","E"},J2)))>0=FALSE,SUMPRODUCT(--ISNUMBER(SEARCH({"Som","Sel"},K2)))>0=FALSE),"Advanced","")

Or the all kids are special approach :

="Advanced"

 

[taniab]

Thank you so much, that is amazing, it works so well!! Unfortunately, I've just been told behaviours are for each of the courses and there are up to 8 courses! So how would I add to the formula to include the Behaviours for each course!

Course Behaviours 7 and 8 (not shown below) are colum Q and R

	GRADES (J)	Behaviours Course 1 (K)	Behaviours Course 2 (L)	Behaviours Course 3 (M)	Behaviours Course 4 (N)	Behaviours Course 5 (O)	Behaviours Course 6 (P)
	C C C B C	Oft Con Con Con Con	Som Con Som Oft Som	Oft Con Con Con Con	Con Con Con Con Con	Oft Con Oft Oft Oft	Oft Oft Som Som Oft
	B C B B C	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con
	B C B	Con Con Con Con Con	Con Con Con Con Con	Oft Oft Oft Con Oft	Oft Con Con Con Con	Con Con Con Con Con	0
	C C C	Con Con Con Con Con	Som Oft Oft Som Som	Oft Con Con Con Con	Oft Oft Oft Oft Oft	Con Oft Oft Oft Oft	0
Advanced	A A A A	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Oft Con	Con Con Con Con Con	Con Con Con Con Con
	C C C	Con Con Con Con Con	Oft Oft Oft Som Som	Oft Oft Con Con Con	Oft Oft Oft Con Oft	Oft Oft Oft Oft Oft	0
	C C C	Con Con Con Con Con	Con Con Oft Con Con	Oft Oft Oft Oft Oft	Som Oft Oft Oft Som	Con Con Con Con Con	0
	B C C B C	Con Con Con Con Con	Con Con Con Oft Con	Oft Con Con Oft Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con
	A B B C A	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con
	C C C C C	Con Con Con Con Con	Con Con Con Con Con	Con Oft Con Oft Con	Con Con Con Con Con	Con Con Con Con Oft	Con Con Con Con Con
	B C B	Con Con Con Con Con	Som Con Oft Oft Oft	Som Con Con Con Con	Oft Con Con Oft Con	Con Con Con Con Con	0
Advanced	A A A	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	0
Advanced	A A B	Con Con Con Con Con	Con Con Con Con Con	Oft Oft Oft Oft Oft	Con Con Con Con Con	Con Con Con Con Con	0
Advanced	B A B	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con		Con Con Con Con Con
Advanced	B B A B A	Con Con Con Con Con	Oft Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Oft Con Con Con Con	Con Con Con Con Con
	C A B	Con Con Con Con Con	Con Con Con Con Con	Som Oft Oft Oft Oft	Con Con Con Con Con	Con Con Con Con Con	0
	C C B	Oft Oft Oft Oft Oft	Oft Oft Oft Con Oft	Oft Oft Con Oft Con	Oft Oft Oft Con Oft	Con Con Con Con Con	Con Con Con Con Con
	C C C A C	Con Con Oft Oft Oft	Oft Con Con Con Con	Oft Oft Som Oft Som	Som Som Som Oft Som	Con Oft Oft Oft Oft	Con Con Con Con Con
	C B B C C	Oft Con Oft Oft Con	Con Oft Con Oft Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con
	C C B	Con Con Con Con Con	Oft Oft Con Oft Oft	Oft Con Som Con Som	Oft Oft Oft Con Oft	Con Oft Con Con Con	Con Con Con Con Con
Advanced	B B B	Con Con Con Con Con	Con Con Con Con Con	Con Con Oft Oft Con	Con Con Con Con Con	Oft Oft Oft Oft Oft	Con Con Con Con Con
Advanced	A B A B A	Oft Con Con Con Con	Oft Con Oft Con Oft	Con Con Con Con Con	Con Con Oft Con Con	Oft Con Con Con Oft	Con Con Con Con Con
Advanced	B A B B	Con Con Con Con Con	Oft Oft Con Con Con	Con Con Con Con Con	Oft Con Oft Oft Oft	Con Con Con Con Con	Con Con Con Con Con

 

[Toadstool]

Reqs changed

 

[Toadstool]

Hi Taniab,

Does this do what you want?

Taniab.xlsx
	J	K	L	M	N	O	P	Q	R	S
1	GRADES	Behaviours	Behaviours Course 1	Behaviours Course 2	Behaviours Course 3	Behaviours Course 4	Behaviours Course 5	Behaviours Course 6	Behaviours Course 7	Behaviours Course 8
2		C C C B C	Oft Con Con Con Con	Som Con Som Oft Som	Oft Con Con Con Con	Con Con Con Con Con	Oft Con Oft Oft Oft	Oft Oft Som Som Oft	Oft Oft Som Som Oft	Oft Oft Som Som Oft
3		B C B B C	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con
4		B C B	Con Con Con Con Con	Con Con Con Con Con	Oft Oft Oft Con Oft	Oft Con Con Con Con	Con Con Con Con Con	0	0	0
5		C C C	Con Con Con Con Con	Som Oft Oft Som Som	Oft Con Con Con Con	Oft Oft Oft Oft Oft	Con Oft Oft Oft Oft	0	0	0
6	Advanced	A A A A	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Oft Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con
7		C C C	Con Con Con Con Con	Oft Oft Oft Som Som	Oft Oft Con Con Con	Oft Oft Oft Con Oft	Oft Oft Oft Oft Oft	0	0	0
8		C C C	Con Con Con Con Con	Con Con Oft Con Con	Oft Oft Oft Oft Oft	Som Oft Oft Oft Som	Con Con Con Con Con	0	0	0
9		B C C B C	Con Con Con Con Con	Con Con Con Oft Con	Oft Con Con Oft Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con
10		A B B C A	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con
11		C C C C C	Con Con Con Con Con	Con Con Con Con Con	Con Oft Con Oft Con	Con Con Con Con Con	Con Con Con Con Oft	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con
12		B C B	Con Con Con Con Con	Som Con Oft Oft Oft	Som Con Con Con Con	Oft Con Con Oft Con	Con Con Con Con Con	0	0	0
13	Advanced	A A A	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	0	0	0
14	Advanced	A A B	Con Con Con Con Con	Con Con Con Con Con	Oft Oft Oft Oft Oft	Con Con Con Con Con	Con Con Con Con Con	0	0	0
15	Advanced	B A B	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con		Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con
16	Advanced	B B A B A	Con Con Con Con Con	Oft Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Oft Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con
17		C A B	Con Con Con Con Con	Con Con Con Con Con	Som Oft Oft Oft Oft	Con Con Con Con Con	Con Con Con Con Con	0	0	0
18		C C B	Oft Oft Oft Oft Oft	Oft Oft Oft Con Oft	Oft Oft Con Oft Con	Oft Oft Oft Con Oft	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con
19		C C C A C	Con Con Oft Oft Oft	Oft Con Con Con Con	Oft Oft Som Oft Som	Som Som Som Oft Som	Con Oft Oft Oft Oft	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con
20		C B B C C	Oft Con Oft Oft Con	Con Oft Con Oft Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con
21		C C B	Con Con Con Con Con	Oft Oft Con Oft Oft	Oft Con Som Con Som	Oft Oft Oft Con Oft	Con Oft Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con
22	Advanced	B B B	Con Con Con Con Con	Con Con Con Con Con	Con Con Oft Oft Con	Con Con Con Con Con	Oft Oft Oft Oft Oft	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con
23	Advanced	A B A B A	Oft Con Con Con Con	Oft Con Oft Con Oft	Con Con Con Con Con	Con Con Oft Con Con	Oft Con Con Con Oft	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con
24	Advanced	B A B B	Con Con Con Con Con	Oft Oft Con Con Con	Con Con Con Con Con	Oft Con Oft Oft Oft	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con	Con Con Con Con Con
Sheet1 (2)

Cell Formulas
Range	Formula
J2:J24	J2	=IF(AND(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K2,"C",""),"D",""),"E",""))=LEN(K2),LEN(SUBSTITUTE(SUBSTITUTE(L2&M2&N2&O2&P2&Q2&R2&S2,"Som",""),"Sel",""))=LEN(L2&M2&N2&O2&P2&Q2&R2&S2)),"Advanced","")

 

[taniab]

mmm that didn't work, but I just put this L2&M2&N2&O2&P2&Q2&R2&S2 into the first formula and it seems to be working now.

Thank you thank you thank you

=IF(AND(SUMPRODUCT(--ISNUMBER(SEARCH({"C","D","E"},J2)))>0=FALSE,SUMPRODUCT(--ISNUMBER(SEARCH({"Som","Sel"},K2&L2&M2&N2&O2&P2&Q2&R2&S2 )))>0=FALSE),"Advanced","")

 

[taniab]

More requirements!!!
Behaviours are either
Consistently -con
Often - oft
Sometimes - som
Seldom - sel
Not Assessed - n/a

Advanced must now include attendance score. So same formula as below, but attendance must now be included and must beover 92%. Attendance percentage is in E11 (see screenshot)

=IF(AND(SUMPRODUCT(--ISNUMBER(SEARCH({"D","E"},J11)))>0=FALSE,SUMPRODUCT(--ISNUMBER(SEARCH({"Som","Sel","n/a"},K11&L11&M11&N11&O11&P11*Q11&R11)))>0=FALSE),"Advanced","")

I need to apply two more tests to the same set of data. So if it meets the above criteria it is advanced.

If it meets these criteria, standing is Good. The problem with this one is that some students study 3, some 4, some up to 8 courses, so to get the % of behaviours/attributes and also the % of C's, there needs to be a count as well. Sometimes they might be studying 6 courses, get a grade for 4 courses, but Behaviours/Attributes for all 6 courses. If no behaviours/Atrributes then the cell will be empty.

• Commitment Standard Obtain at least 50% combined ‘Consistently’ and ‘Often’ attributes on Semester report.
• Academic Standard Obtain a minimum “C” grade in 60% or more Courses i.e. for 5-day program, 3 or more “C” Course grades. For 3-day program, 2 or more “C” Course grades.
• Attendance Standard 90% attendance or better in all six courses studied (ATAR or General 5 day Courses) or the equivalent for the three day Voc. Program.

If it meets these criteria, standing is Conditional
Commitment Standard Obtain less than 50% combined ‘Consistently’ and ‘Often’ attributes on Semester report.
Academic Standard Obtain less than minimum “C” grade in 60% or more Courses i.e. for 5-day program, less than 3 “C” Course grades. For 3-day program, less than 2 “C” Course grades.
Attendance Standard Below 90% attendance in all six courses studied (ATAR or General 5-day Courses) or the equivalent for the three day Vocation Program.