Google Sheets: test with wheights system (weighted average with multiple variables)

[PhilEdward]

This year I will study for a national exam and hope to be approved for computer science. Excited, so I decided to plan my objectives considering the grades I expected to achieve on the essay and mathematics. Thus I made the following google spreadsheet. If your solution uses anything that google sheets doesn't support, I totally agree to move my sheets to excel. OK?

College exam help.png

Notice above on lines 1-2 that I selected the desired course, determined the required average - last year's minimun grade - and the sum of the 5 grades that I need.

Going down, on lines 3-9, there is my first attempt to provide the grades I expect to attain and get an estimate of the remaining grades for the blank cells on column C (Input). This consists simply of subtracting the total input (C9) from the passing score sum (D2) and then dividing by the number of blank cells on column C (countblank(C4:C8)). For this I just used a simple IF(input<>"" ; repeat ; calculate) to recognize when an input is filled or blank, if not blank the formula just repeats the input, if so it calculates.

At the end, everything went well and I got the result 711,6666667 divided equally by the 3 blank cells I left on the Input column.

--/--/--

Now the problem:

Later, I figured out that my brother wants to enter on a college that has a weights system, then I tried to implement it to my sheet just by adding a weights column where all the cells are required to be filled with numbers from 1-100 (notice that his college uses weights from 1-5, but I'm not sure if anyone would use anything higher than 5). But, after failing miserably, I noticed that I can't just divide equally if the equation results on a weighted average with multiple variables and it must also be taken into account that there are several combinations.

I can't even decide how many calculated columns I need.

If it was required to fill all the inputs or leave only 1 blank cell, I could get an easy result. However my intention is to provide only the grades on the subjects you have affinity with - essay and math in my case.

Is there any formula that could solve this problem?

--/--/--

I have been using sheets for a year, but this is my first time with formulas.