How to find the weighted average of a category?

[agerrard]

Hi All,

How would i find the weighted average of the column titled "ALCOHOL % SEGMENTS". At the moment if i just take the average of all the values in that column i end up with 5.9. But i want to make sure that i get the weighted average of that column based on the size of each of the segments. So for example the 3.5% segment (that is the top segment with litres sales of 15,183,848) should receive a higher weighting that say the 4.7% segment that has sales of 780,111. But how do i do that in the formula in cell A80??

ABV Average - Weighted.xlsx
	A	B	C
6	ALCOHOL % SEGMENTS	LITRES SALES	SHARE OF CATEGORY
7	3.5	15,183,848	25%
8	4.5	10,101,934	17%
9	4.2	8,026,290	13%
10	4.6	7,042,038	12%
11	4.9	4,721,966	8%
12	4.4	3,975,682	7%
13	5	3,165,422	5%
14	4	1,796,876	3%
15	3	1,059,032	2%
16	4.7	780,111	1%
17	4.8	682,475	1%
18	5.8	596,341	1%
19	5.1	580,748	1%
20	2.7	448,094	1%
21	5.2	391,384	1%
22	3.3	318,865	1%
23	2.4	241,615	0%
24	0	232,207	0%
25	3.2	172,601	0%
26	6.3	164,971	0%
27	4.3	152,233	0%
28	2.9	134,999	0%
29	6	87,004	0%
30	8	64,311	0%
31	3.8	64,277	0%
32	6.5	35,163	0%
33	5.5	33,862	0%
34	2	33,301	0%
35	5.4	33,272	0%
36	6.2	20,020	0%
37	6.8	16,167	0%
38	2.3	14,139	0%
39	7.5	11,617	0%
40	5.7	10,810	0%
41	7	10,495	0%
42	7.2	9,655	0%
43	5.9	8,839	0%
44	5.3	8,705	0%
45	0.5	8,057	0%
46	5.6	7,720	0%
47	6.4	6,156	0%
48	7.4	5,748	0%
49	4.1	4,967	0%
50	6.1	4,486	0%
51	7.9	3,903	0%
52	8.8	3,572	0%
53	6.7	3,458	0%
54	3.4	3,388	0%
55	6.6	2,822	0%
56	7.8	2,487	0%
57	6.9	2,433	0%
58	0.4	1,234	0%
59	9.1	1,044	0%
60	8.2	908	0%
61	8.5	597	0%
62	7.1	589	0%
63	10	584	0%
64	2.8	404	0%
65	3.9	382	0%
66	10.5	336	0%
67	11.7	322	0%
68	9	280	0%
69	8.7	279	0%
70	9.5	269	0%
71	0.9	207	0%
72	3.6	198	0%
73	7.3	96	0%
74	11	33	0%
75	12	16	0%
76	14.7	8	0%
77	7.7	5	0%
78	9.2	2	0%
79	7.6	1	0%
80	5.9
SUMIF (2)

Cell Formulas
Range	Formula
A80	A80	=AVERAGE(A7:A79)

 

[bebo021999]

As per my calculation, Weighted Average should be:
=SUMPRODUCT($A$7:$A$79*$C$7:$C$79)
=4.22