We can calculate a weighted average by multiplying the values to average by corresponding weights then dividing the sum of results by the sum of weights. First of all add a helper column of Amount in the source data. First lets look at how the SUMPRODUCT function works.
First the AVERAGE function below calculates the normal average of three scores.
First of all add a helper column of Amount in the source data. Determine the weight of each data point. Calculate the SUMPRODUCT ie multiply each value by its weight and then calculate their sum in order to get. The basic formula for a weighted average where the weights add up to 1 is x1 w1 x2 w2 x3 w3 and so on where x is each number in your set and w is the corresponding weighting factor.