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

1. ## 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.  Register To Reply

2. ## Re: College entrance test with wheights system (weighted average with multiple variables)

Welcome to the forum. Is this an Excel or Google Sheets query?  Register To Reply

3. ## Re: College entrance test with wheights system (weighted average with multiple variables) Originally Posted by AliGW Welcome to the forum. Is this an Excel or Google Sheets query?
Many thnaks! This was made on google sheets. But I think that it could apply to Excel, since I don't use vba, macros or any programming, just formulas.  Register To Reply

4. ## Re: College entrance test with wheights system (weighted average with multiple variables)

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

5. ## Re: College entrance test with wheights system (weighted average with multiple variables) Originally Posted by AliGW Thats not what I asked. Which will you be using with this file? The platform will determine the solutions offered.
T-T Sorry. I'm having some translations and keyboard problems which keeps swapping words instead of correcting grammar.
I couldn't find any translation for "query", so I'm assuming that it is a feature that works in different manners on Google and Excel. Is that right?  Register To Reply

6. ## Re: Google Sheets: test with wheights system (weighted average with multiple variables)

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.  Register To Reply

7. ## Re: Google Sheets: test with wheights system (weighted average with multiple variables)

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.

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

8. ## Re: Google Sheets: test with wheights system (weighted average with multiple variables) Originally Posted by MrShorty 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.
I was organizing some visual explanation for your questions then I accidentaly got the solution T-T ;-; u-u
The keys were to figure out the total sum of the weighted objective, then getting the difference between it and the total sum of weighted input, then divide it by the sum of weights with blank inputs (wich in this case is 3+2+2 = 7) and multiply it by the weight of its line.

Attachment 712592

Attachment 712593  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 