Apr 09 2020 09:24 AM
Apr 21 2020 07:47 PM
I couldn't think of a way to do this without helper rows. On the attached workbook, you enter your scores in yellow, and the final grade updates.
Apr 21 2020 08:55 PM
Below are 2 solutions. I used the sheet layout given by DhaniCole. But instead of the helper row I divided out the sum of the weighted values that actually got used.
The first is all written out with the weighted values 'hard coded' as you had in your original question:
=(B5*0.25+C5*0.25+D5*0.125+E5*0.125+F5*0.125+G5*0.125)/(0.25*ISNUMBER(B5)+0.25*ISNUMBER(C5)+0.125*ISNUMBER(D5)+0.125*ISNUMBER(E5)+0.125*ISNUMBER(F5)+0.125*ISNUMBER(G5))
The second (and what I recommend) makes use of a row that defines the weighting values ($B$4:$G$4 as was done in DhaniCole's sheet):
=SUMPRODUCT(B5:G5,$B$4:$G$4)/SUMPRODUCT(ISNUMBER(B5:G5)*$B$4:$G$4)