Helped needed creating formula

[Firewalker]

The columns I've set up are column A is the name of the student. Column B is the name of the teacher and Column C is the student's score.

What I want to do is figure out what percentage of students made a mark higher than 85%. What's getting me is how to do this when there is more than one teacher. I'm thinking I might have to do a LOOKUP but I'm not sure.


Any suggestions for this scenario?


Thanks in advance.

 

[Scott Huish]

Formula in S2:

=SUMPRODUCT(--($D$2:$D$11=R2),--($O$2:$O$11>0.85),--(ISNUMBER($O$2:$O$11)))/SUMPRODUCT(--($D$2:$D$11=R2),--(ISNUMBER($O$2:$O$11)))

Although it would be sad, I presume 0 is a possible score, therefore I would use COUNT instead of SUM to determine if you are going to average or not.

=IF(COUNT(F2:N2),AVERAGE(F2:N2),"")

 

[Aladin Akyurek]

Tried that formula and got a DIV error

The previous formula should work except the fields with "" count as a passing mark.

Any ideas?

aaCondAvg Firewalker.xls
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	FULL	ID	TL	10
2	Student A		Teacher B	10	94%	90%	88%	90%	100%	77%	85%			89%			Teacher A	0.5
3	Student B		Teacher A	10	85%	97%	100%	100%	94%	95%	100%			96%			Teacher B	0.75
4	Student C		Teacher B	10	95%	91%	77%	100%	93%	100%	88%			92%
5	Student D		Teacher A	10	90%	100%	100%	93%	100%	100%				97%
6	Student E		Teacher B	5	100%	67%								84%
7	Student F		Teacher B	0
8	Student G		Teacher A	10	100%	69%	60%	73%	83%	90%	86%			80%
9	Student H		Teacher A	10	70%	59%	77%	90%	93%	85%				79%
10	Student I		Teacher B	10	83%	100%	100%	90%	100%	93%				94%
11	Student J		Teacher A	0
Sheet1

S2:

=SUM(IF($D$2:$D$11=R2,IF(ISNUMBER($O$2:$O$11),IF($O$2:$O$11>85%,1))))/SUM(IF($D$2:$D$11=R2,IF(ISNUMBER($O$2:$O$11),1)))

which must be confirmed with control+shift+enter (not just with enter) then copied down.

 

[Firewalker]

Thanks so much for the help pepper, the formulas work great.


I'm looking for additional data from the sheet as well.

What I'm looking for is to determine is

1. How many marks/scores that meet a certain criteria (IE 85% or above) from a certain teacher

2. What percentage of the total calls from a particular teacher met the above criteria

(IE Teacher A had 30 marks above 85% and 75% of teacher A's total marks were above 85%)

Any suggestions for this?

 

[Firewalker]

Was thinking about this formula I require.


Would a SUMPRODUCT formula be a good fit here?

 

[Firewalker]

I've included my updated sheet.

All I'm looking for is to determine how many perfect scores each teacher had.

thanks in advance.

test sheet 2.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		FULL	ID	TL	10								AVG	Averagepass
2		StudentA		TeacherB	10	94%	90%	88%	90%	100%	77%	85%	89%	TeacherA	50.00%
3		StudentB		TeacherA	10	85%	97%	100%	100%	94%	95%	100%	96%	TeacherB	75.00%
4		StudentC		TeacherB	10	95%	91%	77%	100%	93%	100%	88%	92%
5		StudentD		TeacherA	10	90%	100%	100%	93%	100%	100%		97%
6		StudentE		TeacherB	5	100%	67%						84%
7		StudentF		TeacherB	0
8		StudentG		TeacherA	10	100%	69%	60%	73%	83%	90%	86%	80%
9		StudentH		TeacherA	10	70%	59%	77%	90%	93%	85%		79%
10		StudentI		TeacherB	10	83%	100%	100%	90%	100%	93%		94%
11		StudentJ		TeacherA	0
12
13						Numberof100%scores
14						TeacherA
15						TeacherB
Sheet1