Why does my code return incorrect values for my weighted average calculations?-CodePudding

this is my first question on StackOverflow, so bear with me if I get some of the procedure/posting standards incorrect.

My goal is to assign each student in the dataset a weighted average based on their test and quiz scores. Tests constitute 90% of this weighted average, and quizzes make up the remaining 10%. There is an additional caveat that if a test score is below 60, this score receives an additional 5 points (without changing the original test score). This entire process is to be done using a VBA programmed macro.

The problem I am having is that the calculated averages are wrong. (see picture, some scores are over 100 which should not be possible) I have tried to isolate the error by looking at the individual test and quiz averages, and so far I believe the error lies in the calculation of the test average but I'm not quite sure what exactly the problem is. Even when I remove the if statement that accounts for the "curve" I still end up with scores over 100. This makes me think there is an issue with the way I am calculating the averages. I have provided the code for my macro below. If there are any other problems with how I wrote this macro, please let me know I am still new to VBA.

Sub SetTwo()
Dim gradeAvg As Integer
Dim testAvg As Double
Dim quizAvg As Double
For i = 2 To 104
    gradeAvg = 0
    testAvg = 0
    quizAvg = 0
    For j = 6 To 13
        If j <= 8 Then
            If Worksheets("Set2").Cells(i, j) < 60 Then
            testAvg = testAvg   Worksheets("Set2").Cells(i, j)   5
            Else
            testAvg = testAvg   Worksheets("Set2").Cells(i, j)
            End If
        Else
        quizAvg = quizAvg   Worksheets("Set2").Cells(i, j)
        End If
    Next j
    testAvg = (testAvg / 300) * 90
    quizAvg = (quizAvg / 100) * 10
    gradeAvg = testAvg   quizAvg
    Worksheets("Set2").Cells(i, 13) = gradeAvg
Next i
End Sub

Here is a sample of the dataset, with the averages calculated incorrectly

CodePudding user response：

This is a tad bit embarassing. After playing around with the code a little more, I found my issue. It seems the bound for my inner for loops was wrong. I included the cell that contains the "average" value which ended up distorting my calculations and resulted in my calculated values going over 100.

For those interested, I have included the code for the correct macro below:

Sub SetTwo()
Dim gradeAvg As Integer
Dim testAvg As Double
Dim quizAvg As Double
For i = 2 To 104
    gradeAvg = 0
    testAvg = 0
    quizAvg = 0
    For j = 6 To 12
        If j < 9 Then
            If Worksheets("Set2").Cells(i, j) < 60 Then
            testAvg = testAvg   Worksheets("Set2").Cells(i, j)   5
            Else
            testAvg = testAvg   Worksheets("Set2").Cells(i, j)
            End If
        Else
        quizAvg = quizAvg   Worksheets("Set2").Cells(i, j)
        End If
    Next j
    testAvg = (testAvg / 300) * 90
    quizAvg = (quizAvg / 100) * 10
    gradeAvg = testAvg   quizAvg
    Worksheets("Set2").Cells(i, 13) = gradeAvg
Next i
End Sub

CodePudding user response：

the following sentence has 1 extra, it calculates including the "Average" cell

For j = 6 To 13

Should be j = 6 To 12

Hope it helps ! I did simulations and went good.