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.

 

[Firewalker]

Also, are you sure you want relative addresses on all this?

Not too sure what you mean by that.

Basically I'm looking for a way to calculate the percentage of students from a certain teacher who made a passing mark.

 

[Scott Huish]

Not too sure what you mean by that.

If you notice in the original formula I proposed the addresses have $ signs before the column and row, this keeps those cells the same when you copy them down. You don't have the $ signs this means when you copy them down the row reference will increment.

From the help file:

The difference between relative and absolute references

Relative references A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. For example, if you copy a relative reference in cell B2 to cell B3, it automatically adjusts from =A1 to =A2.


Absolute references An absolute cell reference in a formula, such as $A$1, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, and you need to switch them to absolute references. For example, if you copy a absolute reference in cell B2 to cell B3, it stays the same in both cells =$A$1.


Mixed references A mixed reference has either an absolute column and relative row, or absolute row and relative column. An absolute column reference takes the form $A1, $B1, and so on. An absolute row reference takes the form A$1, B$1, and so on. If the position of the cell that contains the formula changes, the relative reference is changed, and the absolute reference does not change. If you copy the formula across rows or down columns, the relative reference automatically adjusts, and the absolute reference does not adjust. For example, if you copy a mixed reference from cell A2 to B3, it adjusts from =A$1 to =B$1.

 

[Scott Huish]

Where does AG7:AG16 come into this? It is not in your sample.

It's also not clear how you mean what percentage of students per teacher have a passing mark as you have a percentage on every row. How does this work?

 

[Firewalker]

The (AG7:AG16) is a reference to the original sheet I've been working on. You can disregard that reference.


As far as the passing mark goes. The percentage you see for example is the students final mark. What I'm looking for is what percentage of students made higher than a specific grade for their final mark. (IE 85%)


Let's say I want to know what % of students from Teacher A's class made higher than 85%. Any student that missed time, etc. would have a blank would not be counted.

 

[Scott Huish]

But why are you not taking one percentage for all of Teacher A, why the multiple percentages for the same teacher?

 

[Firewalker]

The reason for this is each line represents a student.

Each percentage you see to the right on my sheet is the final score of the student. The list is sorted by student and the students have different teachers. Main goal is to calculate what percentage of students for each teacher made the specified mark.



My apologies for not being more clear on this. Kinda new the excel thing

 

[Scott Huish]

OK. I think this might be what you want:

List each teacher you want to check a percentage of passing students for in R2 downward.

Formula in S2:
=SUMPRODUCT(($D$2:$D$11=$R2)*($F$2:$L$11>0.85))/SUMPRODUCT(($D$2:$D$11=$R2)*ISNUMBER($F$2:$L$11))

Copy down.

Excel Workbook
	A	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 A	10	94%	90%	88%	90%	100%	77%	85%			89%			Teacher A	69.77%
3		Student B		Teacher A	10	85%	97%	100%	100%	94%	95%	100%			96%			Teacher B	83.33%
4		Student C		Teacher A	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 A	5	100%	67%								84%
7		Student F		Teacher A	1	100%									100%
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
Sheet

 

[Firewalker]

Hey Pepper,

Thanks for the help so far, we're getting closer. Still an issue with the formula though.

When I calculate the averages for each student I use this formula.

=IF((SUM(F2:N2))>0,AVERAGE(F2:N2),"")

Issue is that if there are no scores for the student the formula enters the "" value in the cell under the O column.

When the formula in S2 and S3 are run the first section of =SUMPRODUCT(($D$2:$D$11=$R2)*($O$2:$O$11>0.85)) returns a true value for the cells with ("") in them. This in essence is saying that no mark is a passing mark.


So the hangup with getting this formula to work is the fact that cells which have "" in them are being considered as being >0.85.

I've included the modified sheet for you to check out.

Thanks again for your help.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		FULL	ID	TL	10
2		StudentA		TeacherB	10	94%	90%	88%	90%	100%	77%	85%			89%			TeacherA	75.00%	shouldbe50%
3		StudentB		TeacherA	10	85%	97%	100%	100%	94%	95%	100%			96%			TeacherB	100.00%	shouldbe75%
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
Sheet1

 

[Aladin Akyurek]

S2:

Control+shift+enter...

=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)))

and copy down.

 

[Firewalker]

Tried that formula and got a DIV error

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

Any ideas?