Weighted Average Excluding Zeros and Using every Other cell in a row.

Good Afternoon,


Above is the data I am trying to get a formula for. What I am trying to do is get a weighted average for sales columns i.e. (April - August). I have assigned weights as below.

April = 1
May = 2
June = 3
July = 4
August = 5

I then multiply the sales for each month by their respective weights: So for Dave:

April = 23214.95 x 1 = 23214.95
May = 19006.90 x 2 = 38013.80
and so on till august.

I then add these products all up and to get the weighted average I divide this total by the sum of the weights, which in this case is 15.

My long and probably inefficient formula for this is :

=SUM(SUM(A2*1)+(C2*2)+(E2*3)+(G2*4)+(I2*5))/15.

This does work. However the problem comes with Paul and John. Some of the months they have had NO sales. Using the formula above works but gives a distorted Weighted Average because it still divides by the sum of ALL the weights (15), even though some of the months had 0 sales.

What would be the best formula to use so that I could divide by the sum of all the weights only for months where there were sales. So in Paul's case weights that would be summed are:

May = 2
June = 3
August = 5
Total of = 10.

The result would go in the Weighted Average Column.

The formula would have to skip the columns entitled "Margin" as well.

Appreciate the help. Can provide any more info needed.

Thanks in Advance,

Sam.

Assuming your data above is in A1:L4.

Then in a separate area list the indices, for example in R2 enter April Sales, in R3 enter May Sales and so on.
Next to those enter the respective weights, 1, 2, 3, etc.

Now in M2 enter formula:

=SUMPRODUCT(SUMIF(INDEX($R$2:$S$6,0,1),$B$1:$L$1,INDEX($R$2:$S$6,0,2)),$B2:$L2)/SUMPRODUCT((SUMIF(INDEX($R$2:$S$6,0,1),$B$1:$L$1,INDEX($R$2:$S$6,0,2))*($B2:$L2>0)))

copied down.

NBVC said:

Assuming your data above is in A1:L4.

Then in a separate area list the indices, for example in R2 enter April Sales, in R3 enter May Sales and so on.
Next to those enter the respective weights, 1, 2, 3, etc.

Now in M2 enter formula:

=SUMPRODUCT(SUMIF(INDEX($R$2:$S$6,0,1),$B$1:$L$1,INDEX($R$2:$S$6,0,2)),$B2:$L2)/SUMPRODUCT((SUMIF(INDEX($R$2:$S$6,0,1),$B$1:$L$1,INDEX($R$2:$S$6,0,2))*($B2:$L2>0)))

copied down.

Click to expand...

Thank you very much. the problem i had with this is that i have over 600 customers, so to accommodate the separate customers using the R2 column would take many many rows, this is assuming i have understood your advice.

i have tried the following:

i inserted a row above row 1 which is my header row. in there i put the weights above each sales month.

then in the weighted average column i put this: