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.

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


  • Re: Contiguous Weighted Average Excluding Zeroes

    Do you mean:


    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!