Standardised testing: Calculating the statistical data (Min, Q1, Median etc.)

The statistics for National, Organisation or State will need to obtained from the relevant body. School statistics can be formulated using a spreadsheet. 

The following formulae can be copy-pasted into Excel (see here for example), where X1:Xn is the range of the cells in which the results are stored, are as follows:

• Min: =MIN(X1:Xn)
• Q1: =QUARTILE(X1:Xn,1)
• Median: =MEDIAN(X1:Xn)
• Q3: =QUARTILE(X1:Xn,3)
• Max: =MAX(X1:Xn)

Add in the formulas

At the end of the first column with values enter the above formulas. Ensure you select the correct cell range. Copy the formula across to the other rows. Save the spreadsheet with a different name if you want to keep it for reference.

Click here to download a copy of the below data with the formulas.

Change the values to whole numbers:

1. Select the cells with the formula > right-click on the selected area > select 'Format cells'.
2. Select Number and set Decimal places to '0' and click OK

What are the Min, Q1, Median, Q3 and Max values?

The mean is the sum of all the numbers in the set divided by the amount of numbers in the set. Whereas the median is the middle point of a number set, in which half the numbers are above the median and half are below. If there are an even number of numbers in a set, to establish the median add the two middle numbers and divide by 2.