Calculate Weighted Average with Conditions

[andrewc]

Hi,

I have sample data in A:G which I would like to use to calculate in column I the weighted average sales growth % each month.

The values used to weight the sales % numbers are those at the end of the previous month. For example, in Jan 2022 the values at Dec 2021 are used as weights for the average sales %.

When a product has a blank cell for its sales % in a particular month (e.g. Feb 2022 for Product 1 in my example), its value (200) will not be used in the next month to calculate the weighted average of the products that don't have a blank cell for Sales % (e.g. Products 2 and 3 for Feb 2022)

You'll notice in column I that I have a different formula in each cell to take account of this but can someone please suggest a formula that would accommodate the requirements mentioned above and which can be used in all circumstances?

Thanks!

[JohnTopley]

Non_365 offering:

in I4

=SUMPRODUCT(($B3:$F3)*($C4:$G4)*($C4:$G4<>"")*($B$2:$F$2="Value"))/SUMPRODUCT(($B3:$F3)*($C4:$G4<>"")*($B$2:$F$2="Value"))

[andrewc]

Thank you!