Posts by baddyswing

    Hi DrRandyman,

    One way could be to create a reference table where every possible letter grade has a corresponding integer value (i.e. score). You can then use VLOOKUP formula to retrieve the score for each grade in S2:S23 and then average them. Since the average result could be non-integer, you would need to round it. Final step is to take the rounded score and reference the matching letter grade (using INDEX and MATCH formula).

    I have attached an example of how this could be done.

    In the case that every grade has equal weighting as each other (e.g. getting grade B is mildly worse than grade A and getting grade C is also equally mildly worse than grade B), then for a scale containing a total of 14 letter grades, the numerical values would be 1 to 14.

    Hope this sounds clear.

    Best regards,


    When you say "remaining amount of quizzes", do you mean those quizzes in columns J-O that have grades that are not the lowest grade? So in the case of row 4, there would be 5 remaining quizzes? I'm guessing when you want to get the average, you don't mean average quiz grade per student?

    Did you get any kind of error message popping up? If so, can you please post up a screenshot of the error message? Or was it something like #N/A in the cell where the function is called from? To help identify the source of the issue, need to first narrow down which line in the code is failing. For example, it could be failing on line 3, 5 or 8 (or on multiple lines). Or it could be having troubles with the argument "day".

    Where you have,

    1. desWS.Cells(rngList(Val), 9) = v2(i, 2)

    Is it possible to firstly read all the contents from column 9 in desWS worksheet into a variable, and then write into this new variable and finally write back out to the desWS worksheet at the end after the second loop? Something like this:

    I made an assumption that the number of rows for column 9 in desWS is the same as column B.

    Is this what you mean?

    I'm posting this on a computer that doesn't have Excel so I haven't test this code yet.