Contiguous Weighted Average Excluding Zeroes

• Hi,

Is there a way to calculate a contiguous Weighted Average for rows, omitting/subtracting weights that have zero values? Something to the effect of

W=Weight, V=Value

[(W1*V1)+(W2*V2)+(W3*V3)+(W4*V4)]/(Wtotal-Wn) , where Wn corresponds to a Vn that = 0

The only method that's popping up in my head right now is a ton of IF's for each scenario where Vn=0, since its about 5 contiguous rows that I'm doing this for, and I've already implemented a 3 array sumproduct that uses 1's and 0's for significance in a third column. These 5 rows are subtotals of the 1's and 0's rows, so doing the same thing would include all the intermediate rows.

I'm attaching a scrap book for reference. Theforum.ozgrid.com/index.php?attachment/69597/

Please let me know if there is anything I need to elaborate on.

Thanks!

Files

• Re: Contiguous Weighted Average Excluding Zeroes

Do you mean:

=SUMPRODUCT(A3:A13,B3:B13)/SUMIF(B3:B13,"<>0",A3:A13)

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

• Re: Contiguous Weighted Average Excluding Zeroes

Sorry, I forgot to add in that the rows in between aren't actually blank, I meant to say that the rows with values will be subtotals. I've already got a method to filter out the intermediate rows (sumproduct, A:A, B:B, C:C)/(Sumproduct, B:B, C:C) where C has 1s and 0's that cancel out the 0 values in B

If I use that method again it will throw off the grand total, so I was wondering if there was a way to model the formula that I mentioned above.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!