# College entrance test with wheights system

#### PhilEd

##### New Member
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?

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!

### 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
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.

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
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.

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:

Replies
4
Views
107
Replies
2
Views
130
Replies
1
Views
202
Replies
6
Views
163
Replies
9
Views
196

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.

### Which adblocker are you using?

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

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