Weighted AverageIFs Statement Using SumProduct

3
votes

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")
1
excelexcel-formulavba
could you mock up some test data and expected output? – Scott Craner
Hey Scott, as you can see by my reputation, I'm not too familiar with this site. How would I upload test data? – user8517443
create a simplified version of the data in excel, copy and paste the text in the original post using Edit. Highlight the new text and hit Ctrl-K to format as code. – Scott Craner
so you want to get the average price based on the common name, but wieghted by the total Fct? – Scott Craner
SumProduct? using an extra column of 1s and 0s to act as the IF element – CFO

1 Answers

3
votes

use SUMPRODUCT divided by a SUMIF:

=SUMPRODUCT((F2:F9="ACC - Finished Goods")*H2:H9,J2:J9)/SUMIFS(H2:H9,F2:F9,"ACC - Finished Goods",J2:J9,"<>0")

enter image description here