I am trying to create an excel file where the grades are calculated automatically. The teachers will enter the Max marks available for an assignment in the top row, and individual student marks in each subsequent row.

Now I want to make a formula that can automatically divide the marks obtained by the max marks for each assignment and find the average of all the results. This would make it so that if a teacher has had 10 assignments, each assignment would automatically be worth 1/10th of the total grade. If the teacher has 5 assignments, each assignment would be worth 1/5th of the total grade. This is the part I am stuck at and I know if I can sort this out the rest of the sheet would be a piece of cake.

I want to make this to make the lives of my teachers easier. I also have no experience with VBscripts but am trying to work it out using standard excel formulae.

So far I have found that if I use the Quotient formula it gives the Value error. If I use a formula that divides every single marks cell by the max marks cell, I get the Div0 error for any empty cells. If I use the SUM of all marks obtained and divide by the SUM of all max marks the answer is wrong (clearly it would be). aaaand I've run out of ideas!

Please help! Thanks!

EDIT:

The formula I used to calculate the grades is:

=(C10/C8 D10/D8 E10/E8 F10/F8 G10/G8)/COUNT(C8:G8)*30

Where row 10 is the marks of the student named 0, row 8 is the max marks dividing by the count and multiplying by 30 is taking the average and multiplying by the weight of the classwork.

CodePudding user response：

Try this formula.

=SUM(IFERROR(C10:K10/C8:K8,0))/COUNT(C8:K8)*30

If you are on non 365 version of excel then confirm the formula to array entry with CTRL SHIFT ENTER.

CodePudding user response：

Are you aware there is a shortcut for:

C10/C8 D10/D8 E10/E8 F10/F8 G10/G8

You can use:

=SUMPRODUCT(C10:G10,1/C8:G8)

(In other words, you can use SumProduct() as SumDivision())