College entrance test with wheights system

PhilEd

New Member
Joined
Jan 10, 2021
Messages
2
Platform
  1. Mobile
  2. Web
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. NOTE: I am aware that there is a section intent to discuss about other applications rather than Excel, however my problem is limited to formulas only. 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 wheights system, then I tried to implement it to my sheet just by adding a wheights column where all the cells are required to be filled with numbers from 1-100 (notice that his college uses wheights 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 wheighted average.

I can't 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.

Here is the link to the sheet for you to give a closer look on formulas, comment or edit. Have in mind that the original page is blocked and the copies are free to edit to prevent doubts if many people start editing.


I would like to thank every attempt of support!
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,434
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Google Sheets: test with wheights system (weighted average with multiple variables)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

PhilEd

New Member
Joined
Jan 10, 2021
Messages
2
Platform
  1. Mobile
  2. Web
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Google Sheets: test with wheights system (weighted average with multiple variables)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Im sorry for that, I was desperate for a solution.

Anyways I got the solution and think it is easir to explain with these images:

Solved T-T ;-; U-U.png


Solution.png
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top