Edufocus.co.ke

The KCSE mean grade is calculated by considering several factors. It begins with the inclusion of points from the three compulsory subjects: Kiswahili, English, and Mathematics. The scores for the fourth and fifth subjects are determined by selecting the highest two scores from the three science subjects available. The sixth subject's score is derived from the best-performing humanity subject. Lastly, the seventh subject's score is determined by choosing the highest value among the second-best humanity subject, the third science subject, and any technical subject completed.

Here is the VBAcode that does the selection:

Private Sub ProcessData()
    Dim shData As Worksheet
    Dim shSubpos As Worksheet
    Dim shSubGrade As Worksheet
    Dim gradeSys As Worksheet
    Dim shBroad As Worksheet
    Dim subp As Worksheet
    Dim lrow As Long
    Dim i As Long, j As Long
    Dim wf As WorksheetFunction

    Set wf = Application.WorksheetFunction
    Set subp = ThisWorkbook.Sheets("subpoints")
    Set gradeSys = ThisWorkbook.Sheets("gradingSystem")
    Set shData = ThisWorkbook.Sheets("DATA")
    Set shSubpos = ThisWorkbook.Sheets("SUBJECTPOSITION")
    Set shSubGrade = ThisWorkbook.Sheets("SUBJECT GRADE")
    Set shBroad = ThisWorkbook.Sheets("BROADSHEET")
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    lrow = shData.Cells(shData.Rows.Count, "A").End(xlUp).Row

    For j = 3 To 14
        For i = 3 To lrow
            Dim cell As Range
            Set cell = shData.Cells(i, j)
            
            ' Copy cell A and B
            shSubGrade.Cells(i, 1).Resize(, 2).Value = shData.Cells(i, 1).Resize(, 2).Value
            subp.Cells(i, 1).Resize(, 2).Value = shData.Cells(i, 1).Resize(, 2).Value
            shBroad.Cells(i, 1).Resize(, 2).Value = shData.Cells(i, 1).Resize(, 2).Value
            
            ' Copy the respective subject grades
            For Each subject In Array("eng", "maths", "kis", "bio", "chem", "phy", "kis", "geog", "hist", "comp", "agric", "bst")
                Set gradeRange = gradeSys.Range(subject)
                If IsEmpty(shData.Cells(i, j)) Then
                    shSubGrade.Cells(i, j).Value = "X"
                Else
                    shSubGrade.Cells(i, j).Value = wf.VLookup(cell, gradeRange, 2)
                End If
            Next subject
            
            ' Map grades to points
            Dim grade As String
            grade = shSubGrade.Cells(i, j).Value
            Select Case grade
                Case "A": subp.Cells(i, j).Value = 12
                Case "A-": subp.Cells(i, j).Value = 11
                Case "B+": subp.Cells(i, j).Value = 10
                Case "B": subp.Cells(i, j).Value = 9
                Case "B-": subp.Cells(i, j).Value = 8
                Case "C+": subp.Cells(i, j).Value = 7
                Case "C": subp.Cells(i, j).Value = 6
                Case "C-": subp.Cells(i, j).Value = 5
                Case "D+": subp.Cells(i, j).Value = 4
                Case "D": subp.Cells(i, j).Value = 3
                Case "D-": subp.Cells(i, j).Value = 2
                Case "E": subp.Cells(i, j).Value = 1
                Case Else: subp.Cells(i, j).Value = ""
            End Select
        Next i
    Next j

    ' Calculate and populate the rest of the data as needed

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Private Sub btnProcessResults_Click()
    ProcessData
    subjectRank
    grade
    broadsheet
    analyzeGrade
    shBroad.UsedRange.EntireColumn.AutoFit
    Application.AutoRecover.Enabled = False
End Sub

Private Sub CommandButton1_Click()
    ProcessData
    subjectRank
    grade
    broadsheet
    analyzeGrade
    shBroad.UsedRange.EntireColumn.AutoFit
    Application.AutoRecover.Enabled = False
End Sub

There are three main methods here: 

ProcessData:

It begins by assigning a letter grade to the marks per subject entered in excel worksheet then from letter grade points are awarded based on a grading system. 

 shSubGrade.Cells(i, j).Value = wf.VLookup(cell, gradeRange, 2)

Thereafter,  the top seven subjects are selected based on points. It includes the accumulation of points for these chosen subjects and the calculation of the total marks attained across the seven subjects. In simpler terms, this process identifies the most favorable subjects, adds up their points, and determines the overall marks achieved in those seven subjects. Here the VBA code for processing data: