Variance and Percentiles, Is This Formula Any Good?

[CAM]

I've just written this bizarre thing whilst experimenting with some data analysis methods on A-Level mock exam results (example results here changed for the sake of anonymity ), am I on the right track? Looking at standard deviation and percentiles.

 

The grades are averaged using the scale A* = 6 counting down to E = 1 and U=0. I add up this score and divide it by the number of grades to give an average. Then I use this average to work out and display the grade for the whole cohort for the bottom 25% of students (it rounds down to 0d.p. or it breaks the VLOOKUP).

 

=VLOOKUP(TRUNC(PERCENTILE.INC($AJ$2:$AJ$117,0.25),0),LOOKUPS!$A$2:$B$9,2,FALSE)

 

This gives a Bottom 25% score of a C. Then I do the same for the upper 25% by replacing 0.25 with 0.75 to give a A. Then use STDEV.P on the entire range of averages to give the standard deviation of 0.97.

 

So from these figures can I say that most students generally get between a C and an A but our predictions may vary by about 1 grade by a very narrow margin?

[Seb1780]

I'm looking into it as I'm not au fait with PERCENTILE.INC but to start off with I would change the "FALSE" in your VLOOKUP to "TRUE" and this will work without the truncation provided your lookup values are arranged in order.

[CAM]

If I don't use TRUNC and rely on VLOOKUP's true argument then it fails. Something about Excel handling decimal places badly in VLOOKUP. http://support.microsoft.com/kb/315961

[LosOjos]

I'd use INDEX and MATCH rather than VLOOKUP.

 

MATCH can be set to return an exact match or the nearest match that is greater/less than; very handy for these kinds of lookups as you can use ranges of values rather than having to create a table for every possible outcome!

 

MATCH returns a position rather than a value, so you couple it with INDEX to retrieve the value at that position.

[Seb1780]

Back to the question - percentiles and predictions.

 

This is a minefield - ask a bunch of statisticians how best to achieve this and 98.7% of the will give a different answer (this statistic may be made up). There will be talk of distributions, fish, things with two names and normality (whatever that is). For the most part this can be ignored.

 

From reading the OP I'm assuming you want to produce a report that says "In Subject A the mock results would suggest we will average a grade X and most people will get between grade W and grade Z" - which, of course, the statistical tools in Excel can work out from your data.

 

But, you have a relatively small sample size and very few potential outcomes so most of the statistical techniques will give you a true but totally irrelevant answer. For instance, from the data in the OP I can state with confidence that 99.8% of your students will get an A-level between A* and U (based on average of C and SD of 0.97) because 99.8% of a normally distributed population falls within +/- 3 standard deviations of the average - true but useless!

 

For the most part when I do this kind of analysis I do one of two things:-

 

1 - Look at the distribution of the grades using pivot tables and charts - I might throw in an "average" grade but nothing more. This kind of analysis is used to identify those at the C/D boundary (for GCSE) and the potential effects of interventions.

 

2 - Look at the difference between what we have and what we would predict (using e.g. FFT) - this type of data is better suited to being treated as a normal distribution and can be useful to identify underperforming students / classes / teachers.

 

The PERCENTILE.INC function isn't really going to help with either of these analyses; it is simply ranking all the results from high to low (or vice versa) and telling you the score achieved by the student at whatever percentile you enter, if your data is skewed in any way (and A-level results certainly can be) this function will produce some rather unexpected results.

[CAM]

Sooo, not as useful as I'd hoped?

 

I noticed our Year 13s had fallen a grade since our last data collection was carried out (most recent round was one grade higher than the last). I figured comparing them would give me an idea how forwards or backwards the group is heading. Work out where most of the students are and their direction.

[Seb1780]

That's a good comparison to do.

 

How about calculating the difference (say D now and B earlier would be -2; A now and B earlier would be +1). Then for each student add up their total difference and looking at the students with the biggest differences first. Similar principles can be applied to subjects and, perhaps, classes within a subject.

[MattMitchell]

Most of our analyses are built around progress (KS3/4) or value-added (KS5), and on changes from one data point to the next.

 

Average grade does have a place, though, in a way: it's published in the performance tables, so it is something you have to look at.

 

Generally speaking, if you're taking averages, it's more useful to have an average of distance-to-target/VA estimate/progress measure, than it is to take an average grade.

 

Percentiles and confidence intervals tend not to be helpful in small cohorts, especially for individual subjects at A level unless you're in a large sixth-form college.

 

One good way of showing summaries of numbers/percentages of students above/at/below target (or value-added, etc) is to use a staggered bar chart (have a look at this for a good example How to Make a Diverging Stacked Bar Chart in Excel | Evergreen Data )

 

I know everyone wants to know things like A*-C%, etc, but they don't have any context to the information, so aren't an indicator of effectiveness. Even in the highest-performing of schools, comparison of progress/value-added against national will always give you more of a handle on how much impact a school/subject/teacher has on students' learning, and they're also (I think) the best way to identify students who need extra support/