The use of Microsoft Excel spreadsheet to analyse results is definitely what a teacher would find useful.
I have created a template http://lily_lee68.webs.com/Mark%20analysis%20template.xlsx
which you just have to enter the names of the students and the marks for the different classes. The information will be automatically transferred to the Mark analysis worksheet.
Please note:
1. You can rename the worksheet e.g. class 1 to 2EA. The formula will automatically be adjusted.
2. Just clear the cells where there is no marks or the extra rows of students - Do not delete the rows.
3. As your total mark might not be 100 you would have to change the formula for column H the total mark which I have mark in red below:
= SUM(G4/100*100)
4. In the Mark Analysis worksheet, just clear the rows which there are no classes.
5. Take note that Class 6 - 10 are for Normal Academic classes as their grading system is different.
Schools which use the same grading system as O level for N level can use this template:
I have noted below how analysis of the results can be made using simple calculation, the conversion to grade as well as how the data from various worksheets (different classes) can be collated on a single worksheet for analysis.
Simple calculation by addition. Just click on cell and then fx and a pop up box will appear. Click on sum and highlight the cells which you want to add up.
You can also input the formula:
=SUM(B4:F4)
in which B4;F4 represent the cells from B4 to F4 which Jane scored in the test for the 5 sections.
To calculate the percentage, you can click on the cell and then input the formula
= SUM(G4/50*100)
in which G4 is the cell which the total is shown and 50 being the maximum mark the students can achieve. Thus if the total mark is 30 then you will have to replace 50 with 30. Modify cell accordingly.
To reflect the grades for O level, you can input the formula:
=IF(H4>74.9,"A1",IF(H4>69.9,"A2",IF(H4>64.9,"B3",IF(H4>59.9,"B4",IF(H4>54.9,"C5",IF(H4>49.9,"C6",IF(H4>44.9,"D7",IF(H4>39.9,"E8","F9"))))))))
To reflect the grades for N level, you can input the formula:
=IF(I26>74.9,1,IF(I26>69.9,2,IF(I26>64.9,3,IF(I26>59.9,4,IF(I26>49.9,5,6)))))
H4 being the cell which shows the % mark. Modify cell accordingly.
To show the number of students who got A1, input the formula =COUNTIF(I4:I7,H11)
I4:I7 are the cells which contain the grades which the students attained and H11 showing the grade A1. Modify cell accordingly.
To calculate the percentage of students scoring distinction, enter the formula
=IF(I20=0,0,(I11+I12)/I20*100)
with I20 being the cell which the Total was shown and I11 showing the numberwith A1 and I12 showing the number with A2. Modify cell accordingly.
=IF(I20=0,0,SUM(I11:I16)/I20*100)
with I20 being the cell which the Total was shown and I11:I16 being the cells with number of students scoring A1 to C6. Modify cell accordingly.
=IF(I20=0,0,SUM(I17:I19)/I20*100)
with I20 being the cell which the Total was shown and I17:I19 being the cells with number of students scoring D7 to F9. Modify cell accordingly.
For the MSG, enter the formula
=IF(I20=0,0,IF(OR(MID($B$3,2,1)="N",MID($B$3,2,1)="T"),(I11*1+I12*2+I13*3+I14*4+I15*5+I16*5+I17*6+I18*6+I19*6)/I20,(I11*1+I12*2+I13*3+I14*4+I15*5+I16*6+I17*7+I18*8+I19*9)/I20))
with I20 being the cell which the Total was shown and I11:I19 being the cells with number of students scoring A1 to F9. Modify cell accordingly.
Transferring data from one worksheet to another
Finally, to collate all the data from the different class to a worksheet for final analysis.
To transfer data from Class A worksheet to Mark Analysis worksheet.
Select the target cell on the Mark Analysis worksheet and type in the + symbol.
Then click on the label button at the bottom for Class A and then click on the cell which you want the data to be transferred to the target cell and press enter which will being you back to the Mark Analysis worksheet.
You can also enter the formula in the target cell:
=+'Class A'!I11
with Class A being the worksheet and I11 being the cell which you are transferring the data from. Modify cell accordingly.
Sample mock http://lily_lee68.webs.com/MOCK.xlsx
No comments:
Post a Comment