I have a table of data (test scores) for students at the top is the maximum score they could have achieved for each test. On the left side I have average all their test scores to give a grade. (see image below)
However, I realised that since all the tests are worth different amounts, the average would be skewed a lot more if they missed a test with lots of questions. eg missing a test with 30 really easy questions would have 6 times the impact on their overall grade than missing a test with 5 harder ones...
What I want to do is convert each cell to a percentage before finding the average. One way I have done it in the past is to set up a new sheet with each cell = score/marks to give a percent for every value and then add those up, but I hoped there was a cleverer way to do it with a formula.
At the moment I Sum cells e7:g7 and divide by the total points (sum of cells e4:g4)
But I want the sum of (e7/e4 + f7/f4 + g7/g4...) instead.
Is there a way to do this without writing down every single cell as I have a lot of columns... obviously it also needs to check that I am not dividing by zero in the columns that have not already been filled in.
Thanks
