How to generate statistical analysis using Excel

If my mail is representative, teachers are frequently expected to provide a statistical analysis of their pupils’ grades but aren’t given the know-how.

In this article I’ll offer some simple ways of looking at student results which can be emulated in any version of Excel.

Classes these days seem to have anywhere between 30 and 60 children but, simply to save space here, the example has 15. Similarly, the number of exam subjects has been confined to six and the number of grades to four. Once you know how the formulas work you can obviously expand these limitations to suit.

Teachers tell me that current letter grades A to G, plus N and U are usually given numeric equivalents, which can vary with the subject. So, just to make an example, let’s say that an A in maths is worth 50 and a D in art is worth six.

Anywhere on a worksheet, create a table as in the above screenshot. Here, it is in the range J1:Q6. Copy and paste the subjects from B1:G1 into L1:Q1. Under the subject names enter their numeric values in the range L2:Q2.

In the range K3:K6, enter the values of an A, B, C, and D. Here, it’s 5, 4, 2, 1. Again, these are arbitrary values to create an example. The actual values to be assigned to each subject and each grade are up to you.

To multiply the subject values by the grade values you only need one formula. Enter =$K3*L$2 in cell L3. Drag this formula along to cell Q3 and then immediately down to Q6. That completes this table.

Now, in a third table, and again with just one formula, you can convert the students’ letter grades to numeric ones. Here that table is in the range, A19:G34. Copy and paste the grade labels from B1:G1 into B19:G19. Copy the students’ names from A2:A16 and paste into A20:A34. In cell B20 enter:
=INDEX($J$1:$Q$6,MATCH(B2: B16,$J$1:$J$6,),MATCH(L:L, $J$1:$Q$1,))

As this is an array formula, use Ctrl & Shift & Enter instead of just Enter. Excel will add curly brackets to acknowledge it’s an array formula. Drag this formula across to cell G20 and then immediately down to G34.

If you wish, you can add a totalling formula =SUM(B20:G20) in cell H20 and drag this down to H34.
You may wish to sort the student results, but don’t do it with the current formulas in place or Excel will get in a muddle. To preserve this table’s formulas, right-click on the worksheet tab and choose ‘Move or Copy’ and check the ‘Create a copy’ option. On the new worksheet, highlight the table A19:H34. Right-click and choose Copy.

Right-click on cell A19 and choose Paste Special, Values.

Now you can safely highlight the table, A19:H34 and do a Custom Sort of values, from largest to smallest, sorting on Total after you have checked ‘My data has headers’ option.

So much for the individual students, but how well did the class as a whole do? Again, we can see this in a table that requires entering only one formula.

Copy B1:G1 to B38:G38; and J3:J6 to A42. In cell B39 enter =SUM(IF(B$2:B$16=$A39,1)) and press Ctrl & Shift & Enter instead of Enter. Drag this formula along to cell G39 and then immediately down to G42. If you wish to total the number of each grade earned by the class, enter =SUM(B39:G39) in cell H39 and drag down to H42.

You could also calculate the percentage of each grade the class achieved out of all the results. Enter:
=H39/SUM($H$39:$H$42) in cell I39 and drag down I42. Next, format I39:I42 as a percentage.