Standardising a set of test scores
Activity by Richard Bennett and Andrew Hamill, Chester School of Education
The file, Clsdata.xls, is a list of pupils accompanied by their results for three tests. Follow the steps below to standardise the scores and then re-group the pupils according to their standardised scores.
Once the data file has loaded carry out the following tasks:
Save the original file on a floppy disk and/or
print out.
Sort the data into alphabetical order
Enter the data for this child missed off the list: - Barlow, Jennifer – Test1 – 7, Test2 - 15, Test3 - 34
Make a new column showing the total marks for each child
Sort the class list in ascending order according to total marks
Save the sorted document (use Save As…
and give it another name) and print out a class list and group the children into sets of 6 according to their totals.
Create three new columns showing the standardised scores for each test (i.e. one new column for each existing column)
Make a new column showing the total of the standardised scores
Sort the class according to the standardised totals
Save and print out a new class list - regroup the children in sets of 6 according to the standardised totals
Compare this list with the previous - how many children have moved sets?
Consider the implications of this for your class teaching.
|
Task |
|
|
To sort the data |
Highlight all the cells containing data (but do not include the headings to the columns - surname, forenames, etc.) From the Data menu select Sort... Identify the column heading by which you want to sort and click OK |
|
To enter a formula in a cell |
Click in the cell in which you want to enter the formula Click in the Formula Bar at the top of the spreadsheet Type in = then the formula, e.g. = SUM( ) To indicate a cell or cells you want included in the formula, click or click and drag on the spreadsheet |
|
To copy the contents of a cell to a set of cells below it |
Click in the cell you want to copy, then drag down the column until you reach the final cell in which you want the copied cell to appear From the Edit menu select Fill, slide right and select Down |
Useful formulae
To sum the contents of a set of cells i.e. to add the contents of cells A1, A2, A3, A4
= SUM (A1 + A2 + A3 + A4) or = SUM (A1 ... A4)
To standardise a set of scores, select a number which would be easy to convert all the maximum marks into - e.g. 100. Then multiply each mark in that test by the required amount (e.g. if the maximum mark for a test was 20, all the individual marks need to be multiplied by 5 - if the maximum mark was 50, all marks should be multiplied by 2 and so on.)
To multiply the contents of cell A3 by 5 - = A3 * 5
Download the file - Clsdata.xls 
