Excel UMS function

Over the past decade, my departmental recording spreadsheets that track student data have evolved into pretty sophisticated affairs, making full use of Excel’s functionality to make my life easier.

As a teacher, though, Excel does lack a function that I use all the time — converting a raw score into a standardised score. In the most common circumstance, this means interpolating between grade boundaries to produce either a “UMS” score or an FFT-style score.

For instance, a student scores 34/90 on a paper with grade boundaries of 31 (grade C, 96 UMS) and 43 (grade B, 112 UMS). A score of 34 is therefore a B, but how many UMS? More than 96, but less than 112. The exact figure requires “linearly interpolating” between the 96 and the 112:

Student’s unified score = 96 + [ ((34 – 31) / (43 – 31)) x (112 – 96) ] = 96 + [ 3/12 x 16 ] = 100

Excel can do this, but it is a little cumbersome to type in every time, especially when all the numbers need collecting from different places first.

Instead, I wrote a custom function to do it for me:

Student’s unified score = UMS (student’s score, grade boundaries, ums values, decimal places)

All it does is the calculation above, but without the headache of having to code it every time. The only downside is that you have to save your Excel spreadsheet as a “macro-enabled” spreadsheet, with the extension .xlsm, but that’s a small thing.

Here’s an example of the function in use: UMS Function.xlsm

Feel free to copy the code into your own spreadsheets if you can find a use for it. To see the code, click “Developer” on the ribbon, and then “Visual Basic”. (If you can’t see “Developer”, you need to add it by clicking on “File” -> “Options” -> “Customize ribbon” and select the “Developer” check box.)

Note: when opening macro-enabled spreadsheets, it is a feature of Excel to disable macros and require users to choose to re-enable them each time. This is an annoying but prudent security feature, as it is possible to write macros that harm your computer. A sensible response is to leave the macros disabled on first opening a new file and to look at the macro’s code (as above) to check there’s nothing malicious going on. You can then reopen the file and choose to re-enable the macros knowing that it is safe to do so.

Note, the grade boundaries for the above are taken from AQA’s Uniform Mark Scale Converter Tool. Don’t get me started on 34/90 qualifying students for a B grade…

%d bloggers like this: