Selective weighted average

[UndwaterExcelWeaver]

In the table below I need to be able to calculate the weighted average of salt results using only the recipes that have a salt result present. While I have total recipe lbs for all recipes, I do not have salt results for all recipes. So, I need to be able to exclude those recipes that do not have salt results. Can someone help me with a formula to accomplish this? Thanks

Nancy Plant Mass Balance 1023.xlsm
	E	F	G	H	I	J	K
144				Lbs	Moisture	Fat	Salt
145	Nancy Water Packed Big Size			4,830	60.28	16.95	0.88
146	Nancy Marinade			9,720	61.37	16.85	1.02
147	Nancy Water Packed <2oz			16,272	61.00	16.90	0.82
148	Nancy Thermoform			118,538	60.64	17.57	1.06
149	Nancy Water Packed <2oz NT II			663	61.00	16.90
150	Nancy Water Packed Big Size NT II			0	0.00	0.00
151	Nancy Thermoform NT II			31,910	60.30	17.28	0.76
152	Nancy Marinade NT II			0	0.00	0.00
153	Burrata			0	0.00	0.00
154	FS Weighted av (065)			181,933	60.65	17.40	0.98
19

 

[offthelip]

try :

=SUM(K2:K11)/COUNT(K2:K11)

 

[UndwaterExcelWeaver]

try :

=SUM(K2:K11)/COUNT(K2:K11)

I need the weighted average utilizing the total lbs of each in column H.

 

[offthelip]

what about::

=SUMPRODUCT(K2:K11,H2:H11)/((COUNT(K2:K11)*SUMIF(K2:K11,">0",H2:H11)))

 

[UndwaterExcelWeaver]

what about::

=SUMPRODUCT(K2:K11,H2:H11)/((COUNT(K2:K11)*SUMIF(K2:K11,">0",H2:H11)))

That yields a 0.16, which is obviously not correct. it should be in 1.09 if calculated correctly.

 

[offthelip]

How can the weighted averge of salt ever be greater than any of the values of salt?, perhaps you need to explain what you mean by the weighted average you are expecting
Note I wasn't quite sure what range you were trying to "average" and I put in 2 to 11 check it with 2 to 10 and see if that is what you are expecting
try:

=SUMPRODUCT(K2:K11,H2:H11)/((SUMIF(K2:K11,">0",H2:H11)))

 

[UndwaterExcelWeaver]

How can the weighted averge of salt ever be greater than any of the values of salt?, perhaps you need to explain what you mean by the weighted average you are expecting
Note I wasn't quite sure what range you were trying to "average" and I put in 2 to 11 check it with 2 to 10 and see if that is what you are expecting
try:

=SUMPRODUCT(K2:K11,H2:H11)/((SUMIF(K2:K11,">0",H2:H11)))

I apologize, I went to a different tab to gather the actual results, so I calculated the weighted average for the wrong date. The actual weighted average for the example in this listing should be 0.9751. There is not a huge variance on this day because there is only one product without salt analysis and it is only 663 lbs, but there are days with much larger variances and multiple products without salt analysis.

 

[offthelip]

so how are you calculating 0.9751??

 

[UndwaterExcelWeaver]

so how are you calculating 0.9751??

sumproduct of values in column K against all the inventories in column H except "Nancy Water Packed <2oz NT II" volume of 666 lbs because there is no salt associated with it. I used a different spreadsheet to copy and paste in just those rows with analysis.

 

[UndwaterExcelWeaver]

I believe I figured it out. Using this formula in cell K154. I believe this omits values in column H if there is no analysis in column K.

=SUMPRODUCT(K145:K153,$H$145:$H$153)/SUMIF(K145:K153,">0",H145:H153)