How to calculate L4+RWM

Right, first of all, let’s bust a few myths and kill some (very) bad practice. This is how NOT to calculate L4+RWM:

1) Do not assume it is the lowest of the three percentages achieving L4 in the individual subjects. It might be; it might not, and

2) Never, EVER calculate the mean average of the three percentages for L4 in the individual subjects. 

The latter is the most heinous data crime I can think of. If you do this, stop doing it. Stop it now.

If you are struggling to understand why, then imagine a cohort of 9 pupils, of which 3 achieve L4 in reading, 3 achieve L4 in writing, and 3 achieve L4 in maths. Using both the above ‘methods’, the answer would be 33%. But what happens if we are dealing with a different 3 pupils in each subject. Then the percentage achieving L4+ in reading, writing and maths is 0%. To calculate L4+RWM you have to start at pupil level and work up.

So, how to calculate L4+RWM. First, log onto NCA Tools and download the results summary file. The spreadsheet will have columns for pupil name, gender, DoB, and levels for reading, GPS (SPaG), and maths. You need to delete the GPS levels (they are not used in this calculation) and replace them the writing levels. You can cut and paste from another source but ONLY after you have made sure it’s in the same order. Might be best to manually type them in. Also, change the column heading to ‘writing level’ so it makes sense when you look back at it.

You should now have a spreadsheet with levels for reading, writing and maths for all pupils. If any pupils have an N or a B you should replace this with 0,1 or 2. It’s not that important which as far as the calculation is concerned, as none are L4+ anyway.

If any data is missing then this will affect your unvalidated RAISE. Changes should take effect in the validated RAISE, which be published in Spring 2015. So, if you want to calculate the true (final) L4+RWM figure, put their TA levels in; if you want your unvalidated RAISE figure then change them to 0. Probably best to make a copy and do both so you know where you stand. 

Now, click in the cell in the first blank column, to the right of the maths level column; and, for the first pupil, enter the following formula:

=if(and(E2>=4,F2>=4,G2>=4),1,0))

Assuming the levels for reading, writing and maths appear in columns E, F and G respectively. Please check this and modify column references in the formula accordingly.

Then copy the formula down to the last child. You can either copy the cell, select all the remaining cells, and paste. Or simply grap the bottom right corner of the cell containing the formula, when highlighted, and drag it down to the last pupil.

The formula will assign a value of 1 to those pupils with L4 or above in each of the individual subjects, and a 0 to those that don’t fulfill the criteria, e.g. are L4 in reading and writing but L3 in maths. If the pupil is L5 in all subjects they will get a 1; if they are L5 in two subjects and L3 in another they will be assigned a 0.

Then you just need to calculate percentage of pupils with a 1. You can count up the pupils with a 1 and divide by total number of pupils, or you can use a formula such as:

=sumif(H2:H30,1)/counta(H2:H30)

This will sum the 1s (i.e. those pupils that meet the criteria) and divide it by the total number of pupils. 

Please note: H2:H30 in the above formula is the range. It is an example. It assumes our 1 and 0 data exists in column H and that pupils range from row 2 to row 30 (i.e. there are 29 pupils). It may be that your formula is in a different column, and chances are you have more or fewer pupils, so please don’t just copy the above formula. Adjust it to take account of the column references and full range of pupils, or it won’t work.

You should now have a proportion figure in the cell containing your average formula (e.g. 0.86). To convert to a percentage simply click on the % button in the menu bar.

Finally, if you want to calculate L5+RWM, then start again in a new column and change the initial formula to: