How to make result and report in excel for school system
How to make result and report in excel
Make a result system in excel
- Start with writing number of students name, subjects
- Each one above supposed to have its own column
- After that add columns with title of Average, grade, point, division and position
Your layout should look like this below
Average formula
=IF(COUNT(D5:L5)<1,"X",IF(COUNT(D5:L5)<7,"INC",ROUND(AVERAGE(D5:L5),1)))
We used =IF(COUNT(D5:L5)<1,"X" to make a cell of average if no subject filled to write X
Note: my subject started from D5 to L5
We used IF(COUNT(D5:L5)<7, to make a cell of average if there are less than subject to write INC which means Incomplete
ROUND(AVERAGE(D5:L5),1))) - We use this to state average if above logic are not true
Grade
=IF(M5="X","X",IF(M5>=74.5,"A",IF(M5>=64.5,"B",IF(M5>=44.5,"C",IF(M5>=29.5,"D",IF(M5="","","F"))))))
Point for Division
=IF(COUNT(D4:L4)<1,"X",IF(COUNT(D4:L4)<7,"INC",SUM(IF(LARGE((D4:L4),{1,2,3,4,5,6,7})<29,5,IF(LARGE((D4:L4),{1,2,3,4,5,6,7})<44,4,IF(LARGE((D4:L4),{1,2,3,4,5,6,7})<64,3,IF(LARGE((D4:L4),{1,2,3,4,5,6,7})<74,2,1)))))))
What to know from above formula
=IF(COUNT(D4:L4)<1,"X",IF(COUNT(D4:L4)<7,"INC" - This formula is used to express if certain condition is met as explain above in average.
Sum large formula is used to count the best higher marks subject, within this formula has condition of counting only seven best subject as written 1 to 7
Division formula
=IF(M4="X","X",IF(O4="","",IF(O4="INC","INC",IF(O4>=34,"0",IF(O4>=26,"IV",IF(O4>=22,"III",IF(O4>=18,"II",IF(O4>=7,"I",""))))))))
From this formula the logic used here is agreed division from Necta examination, such as Division zero will be greater than or equal to 34, division four will be greater than or equal 26, division three will be greater than or equal to 22, division two will be greater than or equal to 18 while division will greater tan or equal to 7.
The excel works on all formula at once, for example division one will count from 7 to 17 because there is second logic of division two start with 18
Class position
=IF(M4="","",IF(M4="X","X",IF(M4="INC","INC",RANK(M4,M$4:M$58))))
- The formula Rank used for arranging students who did the best to those who did poor.
- From this formula rank will start from M4 to M58
Note: Dollar signs are used to ensure cells are not change
Your final result will be look like this
For report learning click link below
