Conditional Weighted Average Formula

[DarkNight]

Hello,

I'm looking to write a weighted average formula into column R in the below workbook. The Values are in $K$1:$Q$1. The Weights are in $K3:$Q3.
The condition: There is a header column in cells $K2:$Q2. The corresponding header between cells $C2:$I2 can't be 0 for the same row the formula is written on. If it is 0 than that value in columns K:Q shouldn't be considered in the weighted average.

thank you and let me know if you have any questions!

Help.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1										32%	200%	200%	60%	110%	100%	82%
2	Name	Apple	Banana	Pear	Blueberry	Apricot	Cherry	Grape		Apple	Banana	Pear	Blueberry	Apricot	Cherry	Grape	Weighted Average
3	Cillian Thornton	0%	36%	64%	0%	0%	0%	0%		$ -	$ -	$ 43,900	$ -	$ -	$ -	$ -
4	Luciano Atkinson	0%	0%	0%	41%	0%	59%	0%		$ -	$ -	$ -	$ 72,200	$ -	$ 49,100	$ -
5	Kiaan Brooks	0%	0%	0%	0%	85%	15%	0%		$ -	$ -	$ -	$ -	$ 85,000	$ -	$ -
6	Mack Ford	0%	0%	89%	0%	11%	0%	0%		$ -	$ -	$ -	$ -	$ -	$ -	$ -
7	Augustus Williams	0%	0%	0%	0%	0%	55%	45%		$ -	$ -	$ -	$ -	$ -	$ 86,400	$ 84,200
8	Dax Robinson	0%	17%	0%	0%	0%	83%	0%		$ -	$ 79,200	$ -	$ -	$ -	$ -	$ -
9	Elliot Page	46%	54%	0%	0%	0%	0%	0%		$ 70,100	$ -	$ -	$ -	$ -	$ -	$ -
10	Noel Peterson	0%	0%	0%	0%	0%	75%	25%		$ -	$ -	$ -	$ -	$ -	$ 81,000	$ 39,600
11	Clyde Valdez	0%	0%	15%	0%	0%	85%	0%		$ -	$ -	$ 90,700	$ -	$ -	$ 94,400	$ -
12	Leonidas Adkins	0%	26%	0%	74%	0%	0%	0%		$ -	$ 14,600	$ -	$ 69,100	$ -	$ -	$ -
13	Titus Silva	93%	0%	7%	0%	0%	0%	0%		$ 13,600	$ -	$ 14,500	$ -	$ -	$ -	$ -
14	Killian Barnett	86%	0%	0%	14%	0%	0%	0%		$ 77,300	$ -	$ -	$ 87,600	$ -	$ -	$ -
15	Andres Miles	77%	0%	23%	0%	0%	0%	0%		$ 52,600	$ -	$ 26,300	$ -	$ -	$ -	$ -
16	Cairo Moody	0%	0%	79%	0%	0%	21%	0%		$ -	$ -	$ -	$ -	$ -	$ 35,100	$ -
17	Yahir Frazier	0%	0%	0%	89%	0%	11%	0%		$ -	$ -	$ -	$ 27,200	$ -	$ 29,100	$ -
18	Sonny Nelson	0%	0%	29%	0%	0%	0%	71%		$ -	$ -	$ 80,900	$ -	$ -	$ -	$ 29,000
19	Collin Curtis	0%	34%	66%	0%	0%	0%	0%		$ -	$ 11,300	$ 91,300	$ -	$ -	$ -	$ -
20	Truett Fisher	0%	0%	0%	0%	0%	57%	43%		$ -	$ -	$ -	$ -	$ -	$ 11,400	$ 99,900
21	Misael Atkinson	0%	0%	0%	27%	0%	73%	0%		$ -	$ -	$ -	$ 20,100	$ -	$ 93,900	$ -
22	Drew Pierce	0%	0%	39%	0%	0%	61%	0%		$ -	$ -	$ 80,700	$ -	$ -	$ 27,500	$ -
Sheet1

 

[DanteAmor]

Try this

=PROMEDIO(SI(B3:H3>0,J3:P3,0)*($J$1:$P$1))

 

[AhoyNC]

Like this?

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1										32%	200%	200%	60%	110%	100%	82%
2	Name	Apple	Banana	Pear	Blueberry	Apricot	Cherry	Grape		Apple	Banana	Pear	Blueberry	Apricot	Cherry	Grape	Weighted Average
3	Cillian Thornton	0%	36%	64%	0%	0%	0%	0%		$ -	$ -	$ 43,900.00	$ -	$ -	$ -	$ -	21,950.00
4	Luciano Atkinson	0%	0%	0%	41%	0%	59%	0%		$ -	$ -	$ -	$ 72,200.00	$ -	$ 49,100.00	$ -	57,762.50
5	Kiaan Brooks	0%	0%	0%	0%	85%	15%	0%		$ -	$ -	$ -	$ -	$ 85,000.00	$ -	$ -	44,523.81
6	Mack Ford	0%	0%	89%	0%	11%	0%	0%		$ -	$ -	$ -	$ -	$ -	$ -	$ -	-
7	Augustus Williams	0%	0%	0%	0%	0%	55%	45%		$ -	$ -	$ -	$ -	$ -	$ 86,400.00	$ 84,200.00	85,408.79
8	Dax Robinson	0%	17%	0%	0%	0%	83%	0%		$ -	$ 79,200.00	$ -	$ -	$ -	$ -	$ -	52,800.00
9	Elliot Page	46%	54%	0%	0%	0%	0%	0%		$ 70,100.00	$ -	$ -	$ -	$ -	$ -	$ -	9,668.97
10	Noel Peterson	0%	0%	0%	0%	0%	75%	25%		$ -	$ -	$ -	$ -	$ -	$ 81,000.00	$ 39,600.00	62,347.25
11	Clyde Valdez	0%	0%	15%	0%	0%	85%	0%		$ -	$ -	$ 90,700.00	$ -	$ -	$ 94,400.00	$ -	91,933.33
12	Leonidas Adkins	0%	26%	0%	74%	0%	0%	0%		$ -	$ 14,600.00	$ -	$ 69,100.00	$ -	$ -	$ -	27,176.92
13	Titus Silva	93%	0%	7%	0%	0%	0%	0%		$ 13,600.00	$ -	$ 14,500.00	$ -	$ -	$ -	$ -	14,375.86
14	Killian Barnett	86%	0%	0%	14%	0%	0%	0%		$ 77,300.00	$ -	$ -	$ 87,600.00	$ -	$ -	$ -	84,017.39
15	Andres Miles	77%	0%	23%	0%	0%	0%	0%		$ 52,600.00	$ -	$ 26,300.00	$ -	$ -	$ -	$ -	29,927.59
16	Cairo Moody	0%	0%	79%	0%	0%	21%	0%		$ -	$ -	$ -	$ -	$ -	$ 35,100.00	$ -	11,700.00
17	Yahir Frazier	0%	0%	0%	89%	0%	11%	0%		$ -	$ -	$ -	$ 27,200.00	$ -	$ 29,100.00	$ -	28,387.50
18	Sonny Nelson	0%	0%	29%	0%	0%	0%	71%		$ -	$ -	$ 80,900.00	$ -	$ -	$ -	$ 29,000.00	65,808.51
19	Collin Curtis	0%	34%	66%	0%	0%	0%	0%		$ -	$ 11,300.00	$ 91,300.00	$ -	$ -	$ -	$ -	51,300.00
20	Truett Fisher	0%	0%	0%	0%	0%	57%	43%		$ -	$ -	$ -	$ -	$ -	$ 11,400.00	$ 99,900.00	51,273.63
21	Misael Atkinson	0%	0%	0%	27%	0%	73%	0%		$ -	$ -	$ -	$ 20,100.00	$ -	$ 93,900.00	$ -	66,225.00
22	Drew Pierce	0%	0%	39%	0%	0%	61%	0%		$ -	$ -	$ 80,700.00	$ -	$ -	$ 27,500.00	$ -	62,966.67
Sheet1

Cell Formulas
Range	Formula
R3:R22	R3	=SUMPRODUCT(--($C3:$I3<>0),$K3:$Q3,$K$1:$Q$1)/SUMIF($C3:$I3,"<>0",$K$1:$Q$1)

 

[DarkNight]

Like this?

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1										32%	200%	200%	60%	110%	100%	82%
2	Name	Apple	Banana	Pear	Blueberry	Apricot	Cherry	Grape		Apple	Banana	Pear	Blueberry	Apricot	Cherry	Grape	Weighted Average
3	Cillian Thornton	0%	36%	64%	0%	0%	0%	0%		$ -	$ -	$ 43,900.00	$ -	$ -	$ -	$ -	21,950.00
4	Luciano Atkinson	0%	0%	0%	41%	0%	59%	0%		$ -	$ -	$ -	$ 72,200.00	$ -	$ 49,100.00	$ -	57,762.50
5	Kiaan Brooks	0%	0%	0%	0%	85%	15%	0%		$ -	$ -	$ -	$ -	$ 85,000.00	$ -	$ -	44,523.81
6	Mack Ford	0%	0%	89%	0%	11%	0%	0%		$ -	$ -	$ -	$ -	$ -	$ -	$ -	-
7	Augustus Williams	0%	0%	0%	0%	0%	55%	45%		$ -	$ -	$ -	$ -	$ -	$ 86,400.00	$ 84,200.00	85,408.79
8	Dax Robinson	0%	17%	0%	0%	0%	83%	0%		$ -	$ 79,200.00	$ -	$ -	$ -	$ -	$ -	52,800.00
9	Elliot Page	46%	54%	0%	0%	0%	0%	0%		$ 70,100.00	$ -	$ -	$ -	$ -	$ -	$ -	9,668.97
10	Noel Peterson	0%	0%	0%	0%	0%	75%	25%		$ -	$ -	$ -	$ -	$ -	$ 81,000.00	$ 39,600.00	62,347.25
11	Clyde Valdez	0%	0%	15%	0%	0%	85%	0%		$ -	$ -	$ 90,700.00	$ -	$ -	$ 94,400.00	$ -	91,933.33
12	Leonidas Adkins	0%	26%	0%	74%	0%	0%	0%		$ -	$ 14,600.00	$ -	$ 69,100.00	$ -	$ -	$ -	27,176.92
13	Titus Silva	93%	0%	7%	0%	0%	0%	0%		$ 13,600.00	$ -	$ 14,500.00	$ -	$ -	$ -	$ -	14,375.86
14	Killian Barnett	86%	0%	0%	14%	0%	0%	0%		$ 77,300.00	$ -	$ -	$ 87,600.00	$ -	$ -	$ -	84,017.39
15	Andres Miles	77%	0%	23%	0%	0%	0%	0%		$ 52,600.00	$ -	$ 26,300.00	$ -	$ -	$ -	$ -	29,927.59
16	Cairo Moody	0%	0%	79%	0%	0%	21%	0%		$ -	$ -	$ -	$ -	$ -	$ 35,100.00	$ -	11,700.00
17	Yahir Frazier	0%	0%	0%	89%	0%	11%	0%		$ -	$ -	$ -	$ 27,200.00	$ -	$ 29,100.00	$ -	28,387.50
18	Sonny Nelson	0%	0%	29%	0%	0%	0%	71%		$ -	$ -	$ 80,900.00	$ -	$ -	$ -	$ 29,000.00	65,808.51
19	Collin Curtis	0%	34%	66%	0%	0%	0%	0%		$ -	$ 11,300.00	$ 91,300.00	$ -	$ -	$ -	$ -	51,300.00
20	Truett Fisher	0%	0%	0%	0%	0%	57%	43%		$ -	$ -	$ -	$ -	$ -	$ 11,400.00	$ 99,900.00	51,273.63
21	Misael Atkinson	0%	0%	0%	27%	0%	73%	0%		$ -	$ -	$ -	$ 20,100.00	$ -	$ 93,900.00	$ -	66,225.00
22	Drew Pierce	0%	0%	39%	0%	0%	61%	0%		$ -	$ -	$ 80,700.00	$ -	$ -	$ 27,500.00	$ -	62,966.67
Sheet1

Cell Formulas
Range	Formula
R3:R22	R3	=SUMPRODUCT(--($C3:$I3<>0),$K3:$Q3,$K$1:$Q$1)/SUMIF($C3:$I3,"<>0",$K$1:$Q$1)

I got this one working - thank you so much!

 

[AhoyNC]

You're welcome. Thanks for the feedback.