Calculating the probability of one student scoring the highest in the class on an exam.

#1
Hi Everyone, had no luck on the web (I may not be wording my searches well for this).
Given a distribution like this for past performance, how can I calculate the probability for each student of that student scoring the highest on an exam tomorrow? Excel-based solutions preferred. Thanks!

1639082967591.png 1639082967591.png
 

hlsmith

Less is more. Stay pure. Stay poor.
#2
Hmmm, yeah this isn't a standard scenario that I can think of an analog to. We also don't know what the highest score will be.

Each person has a distribution of test scores, so you could see how many standard deviations away a score of say 95 would be or perform 5 one-sample ttests and compare the output. That doesn't quite get at your question, but may get the thought juices flowing. Another issue is that scores are likely 0-100 bound, right?
 
#3
Thanks for looking. I know we can do it for two students head to head, so I was hoping there would be a similar way for all 5 at once.
 

katxt

Active Member
#7
A Monte Carlo approach would probably work.
Assume that the test scores are a good indicator of the final exam score.
Shuffle the test scores among the tests with replacement, keeping the scores for each test together. Work out the average for each student. Record which student has the highest score.
Repeat, say 5000 times.
Find the proportion of the 5000 times each student was top. That is the probability you want.
You would need a spreadsheets with some Monte Carlo functions.
 

katxt

Active Member
#8
Or, a second simpler Monte Carlo idea without MC software, if you think the scores are roughly normal.
For each student make a range for their mean using =NORMINV(RAND(),mean,SD/SQRT(15)) with the 15 for the 15 tests.
Find which student has the maximum mark =MATCH(MAX(mark range),mark range,0)
Copy down say 2500 times.
Collate.
Here's what it looks like with your sample data
1639186493392.png
Should give you a good idea.
 
#9
Student Grade Study with additional columns, +/-1.65σ and +/-1.28σ, the 95% and 80% confidence range for each student.

For a generic test, it can be said with 95% certainty these students will achieve a score in the +/-1.65 range. Or equivalently, that 95% of their scores on a test fall in this range. The range can be narrowed by trading away confidence the scores will appear in a narrower range band, shown with the 80% range of +/-1.28 standard deviations from mean.

Untitled.png

The merit of a test could also be measured against the history of test results in a similar manner. A good test would produce the least variance of student scores from their means, while a poor test would result in student scores farther away from their respective averages.
 
Last edited:

katxt

Active Member
#10
The merit of a test could also be measured against the history of test results in a similar manner. A good test would produce the least variance of student scores from their means, while a poor test would result in student scores farther away from their respective averages.
But the question is "What is the probability of student 3 (say) being top in the exam tomorrow?"
 
#12
But the question is "What is the probability of student 3 (say) being top in the exam tomorrow?"
Yes that was the question, I may have wandered a bit :p

It looks to me like two guys throw a pair of dice, but one of them has a bonus +1, what is the probability he will come out on top?
 

hlsmith

Less is more. Stay pure. Stay poor.
#15
Student Grade Study with additional columns, +/-1.65σ and +/-1.28σ, the 95% and 80% confidence range for each student.

For a generic test, it can be said with 95% certainty these students will achieve a score in the +/-1.65 range. Or equivalently, that 95% of their scores on a test fall in this range. The range can be narrowed by trading away confidence the scores will appear in a narrower range band, shown with the 80% range of +/-1.28 standard deviations from mean.

View attachment 3871

The merit of a test could also be measured against the history of test results in a similar manner. A good test would produce the least variance of student scores from their means, while a poor test would result in student scores farther away from their respective averages.
I was thinking something along this line, but then felt that the coverage could falsely give security on the projection. In particular, those with greater varying previous scores may seem like their coverage could include the highest score, but in reality they could likely have a much lower score as well.
 
#16
I did a little spreadsheet work on the dice model interpretation, didn't get to the end of it though.

Somebody mentioned this might be a calculus integration problem, something like maybe having two probabilities a={mean:64, stdev:15}, b={mean:72, stdev:13} and working out the overlap from there?
 
Last edited:
#17
Thank you everyone! The problem with averaging out monte carlo is that I'd be back to the mean, no? I supposed we could monte carlo it out and capture ceiling scores. I was thinking that since monte-carlo is based on Mean and STDev which we already have, there might be a simple formulaic way to do it.
 

katxt

Active Member
#18
The problem with averaging out monte carlo is that I'd be back to the mean, no?
No, I don't think so. The output from the Monte Carlo is the ID# of the student with the highest exam score. So the output looks like 2 4 3 2 3 2 1 2 3 2 ... and from this you can get the proportion (probability) of each student being top.
One basic problem is that although a student's test average is probably a good indicator of their expected exam score, we need some idea of the random variation that a student may have about that expected value.
 
#19
No, I don't think so. The output from the Monte Carlo is the ID# of the student with the highest exam score. So the output looks like 2 4 3 2 3 2 1 2 3 2 ... and from this you can get the proportion (probability) of each student being top.
One basic problem is that although a student's test average is probably a good indicator of their expected exam score, we need some idea of the random variation that a student may have about that expected value.
Meaning you wouldn't want to just full on... Norm.INV(RAND(),Mean,STD) for fear of randomizing 1-99 as a huge variation?
 

katxt

Active Member
#20
The SD of the 15 test marks for a student includes the between test variability and isn't really a measure of the uncertainty of their final exam score. So what SD to use? My reply #8 used SD as s/sqrt(n) but on reflection this is too small, but the raw SD of the tests is probably too large. The most likely person is probably the same but the actual probability of being top will change as the exam uncertainty changes.
Is this real data? Are you running a sweep?