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

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.

Welcome to the forum.

Is this an Excel or Google Sheets query?

That’s not what I asked. Which will you be using with this file? The platform will determine the solutions offered.

I find that I must understand the calculation outside of Excel (or other programming language) before I can program the calculation into the spreadsheet. It looks like weighted input should be the product of weight and input (so E11=C11*D11 and copied down). I could not tell how you intended to calculate the objective column. Should it be the same as the unweighted example above (results 900, 711 2/3, 750, 711 2/3, 711 2/3)? If so, I would expect the same formula (references adapted) should work in F11:F15. If not, we probably need a better description of what goes here. I'm also not sure what the calculation should look like in the weighted objective column, though I might guess that it should be the product of the objective and weight (So, G11=C11*F11).

If you can help us understand the arithmetic that should go into these cells, we should be able to help with spreadsheet formulas (at this point, I'm not expecting to need anything special that is unique to Google Sheets) for these.

crossposted: https://www.mrexcel.com/board/thread...ystem.1158047/

Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this.

I have added the crosspost reference for you today. Please comply with this and all our rules in the future