We all know what AverageIfs does. However, what if I want to turn my AverageIFs statement into a weighted average based on the data in another column. Suppose that in the Master tab, each row has a quantity in column H. As you can see right now, I'm averaging the prices in column J based on a criteria in column C. Problem: Each row has a unique quantity so a weighted average based on the quantities in row H that meet the criteria is more accurate than just a basic average price. Anyone have any ideas on how to alter this formula?
ProductHier Common Name Total Sls Total Fct 2017 avg price 2018 avg price
B1B1403A01 ACC - Finished Goods 2,448 2,612 99 118
B1B1403A02 ACC - Finished Goods 3,143 3,350 165 180
B1B1403A05 ACC - Finished Goods 709 613 162 152
B1B1403A06 ACC - Finished Goods 206 150 123 142
B1B1403A07 ACC - Finished Goods 1,714 1,441 152 142
B1B1403A08 ACC - Finished Goods 253 295 142 160
B1B1403A09 ACC - Finished Goods 2,447 2,435 88 98
B1B1403A11 ACC - Finished Goods 2,334 3,281 149 174
=SUMPRODUCT((Master!C2:C5000='Forecast Calc'!D2)*Master!H2:H5000,Master!J2:J5000)/SUMIFS(Master!H:H,Master!C:C,'Forecast Calc'!D2,Master!J:J,"<>0")
