The Weighted Average can be calculated by the use of the function:

=SUMPRODUCT(values; weights) / SUM(weights)

Weight and Value arrays need to be of the same length

• they can be on the same sheet just use a different column,
• you can multiply column by column, column by row, row by column, row by array,...
• The order of weights applied to each value need to be in order in the two arrays you specify

Note: if weights are already normalized then you don't need to divide by the sum of the weights (you will be dividing by 1)

Your formula currently is:

Sum(points / maxPoints * weights / sum(weights))

However you can transform it to this form without changing the output:

Sum(points / maxPoints * weights) / sum(weights)

With this you can easily calculate:

SUMPRODUCT(K23:N23;POWER(K22:N22;-1);K21:N21)/SUMIF(K23:N23;">=0";K21:N21)

Update:

If you have many rows with "point", then you need to combine absolute and relative references to calculate:

SUMPRODUCT(K23:N23;POWER(K$22:N$22;-1);K$21:N$21)/SUMIF(K23:N23;">=0";K$21:N$21)

Make your initial range reference absolute, while the other is relative, i.e.:

b4 = average($a$1:a4)

You can paste that 1500 times an it will always increment the end of the range while keeping the beginning pinned to A1 due to the dollar signs in that reference.