WhatsApp Channel Join Now
Telegram Group Join Now

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



From that we need to write formula for average, grade, point, division and position 

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

Next Post Previous Post
No Comment
Add Comment
comment url

popundar ads