how do I:- Statistical formula for exam passes?

[bikeref]

hello good folk.

• i have a spreadsheet of 100 students.
• each student can take 'n' exams.
• each student passes 'p' of their 'n' exams
• thus each student has a pass ratio 'r' as a % :- (p/n)*100
• each student needs to be ranked, 1st to 100.
• rank could simply be 'r' but this is not fair.
• not fair because its harder , for example, to pass 10 exams than it is to pass 1. ignoring complexity and length etc of exam.
• so all the 100 students 'r' % needs to be normalised (is that the right term?) to account for how many exams they each took compared to exams taken by everyone else , and the associated exams passed by each, then ranked 1st to 100 accordingly
• this yields a fairer 'league table'

how?

 

[ER_Neha]

Hello, I hope this helps.

Book1
	A	B	C	D	E
1	Student name	total no of exams	passed exams	percent	rank in class
2	ankit	10	10	100%	1
3	neha	10	9	90%	2
4	punit	10	8	80%	3
5	ajay	10	7	70%	4
6	rohan	10	6	60%	5
7	suman	10	5	50%	6
8	rekha	10	4	40%	7
9	jaya	10	3	30%	8
10	anita	10	2	20%	9
11	lalta	10	1	10%	10
12	sonia	10	5	50%	6
Sheet1

Cell Formulas
Range	Formula
D2:D12	D2	=C2/B2
E2:E12	E2	=RANK(D2,$D$2:$D$11)

 

[bikeref]

thanks.
and you're kind,
but that's not what i mean.
it is more complex than that. I'l type up a specimen worksheet.

 

[bikeref]

I'm struggling...
i have cohorts of students. each cohort has X number of students. each student takes varying number of courses Y so each cohort group has X*Y courses.lets call this Z.

how do i normalise each student results using all Z values acorss the population?

i want to do this because those taking fewerr exams do so because they are more difficult. so its an issue of "weighting" scores.

 

[ER_Neha]

I'm struggling...
i have cohorts of students. each cohort has X number of students. each student takes varying number of courses Y so each cohort group has X*Y courses.lets call this Z.

how do i normalise each student results using all Z values acorss the population?

i want to do this because those taking fewerr exams do so because they are more difficult. so its an issue of "weighting" scores.

Hello.
It will be helpful for understanding your questions.. if you can give a data in xl2bb with a sample data and the desired output.

 

[ER_Neha]

Just a sample set of data will be helpful

 

[bikeref]

that's why I'm struggling, i camt because i DO NOT KNOW how to do this


tell yiou what, use your spreadsheet and add a column called "cohort" and assign each individual to a cohort group number , say from 1 to 3. each cohort group has X number of students.
now you have the data.


i don't know what more i cam do. that's why here asking for help to setup the worksheet and formula to normalise data.

 

[bikeref]

im guessing that some kind of statistical formula are needed that consider each student score with respect to the number of courses they took with respect to the total courses across the population, standard deviation, mean, distribution whatever? standard deviation