Cumulative Average of other percentages (non-contiguous range)

The formula with the AVERAGEIFS function that you wrote suggests that you are not reading the documentation on that function.  That function requires 3 arguments (or 5, or 7…), and you are supplying only 2 arguments (a range and a literal).  (The arguments are different than those for the AVERAGEIF function.)

Here are links to all the Microsoft documentation for Excel's built-in functions: listed alphabetically and listed by category 

Community member pefird gave reasonable suggestions.  But if you do not want to change the structure of the worksheet/workbook that much…

Because Excel formulas cannot determine the current fill color of a cell*, the formulas have no simple way to determine which rows contain the values that you want to average.  And it is preferable to avoid "hard-coding" the specific cells you want to use.

* Technically, they can, but it involves creating a custom function in VBA.  That increases complexity, requires a different file format, and raises security concerns.

This structure of the data is currently not well-suited for the AVERAGEIFS function.  The rows to be summarized are neither consistently spaced, nor is there a consistent number of them, nor is there any simple identifier for those rows.

I suggest that you insert a column somewhere before the current column G (the first student's data).  This new column can be hidden, once it is populated.  For each "important" row, you would load the column with a combination of a semester identifier  and a row-type identifier. 

Or you can use column F and change the formatting to hide these values.  I can suggest values of S1S (semester 1 summary) for row 6 and S1CS (semester 1 course summary) for rows 7, 20, 24, and 41.

See the attached workbook for this latter kind of solution.  As you did not attach a workbook to your post (or make a workbook available through a file-sharing service), and the images were of low quality, I set up only part of the data, and part of the visual formatting.