Weighted Average

[colemenwilson]

I am trying to calculate weighted averages where the weight is determined by volume. I want to calculate these weighted averages for various criteria such as Industry, year, etc...)
Here is a sample of what my data looks like:

Cabinetry

Manufacturing

Cabinetry

Cabinetry

Marketing

Oil and Gas

Cabinetry

<tbody>
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]

[TD="align: right"]1[/TD]
[TD="align: center"]Volume[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"]Industry[/TD]

[TD="align: right"]2[/TD]
[TD="align: center"]4.5[/TD]
[TD="align: right"]$93.50[/TD]

[TD="align: right"]3[/TD]
[TD="align: center"]6[/TD]
[TD="align: right"]$73.48[/TD]

[TD="align: right"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]$71.13[/TD]

[TD="align: right"]5[/TD]
[TD="align: center"]3.5[/TD]
[TD="align: right"]$70.56[/TD]

[TD="align: right"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"]$91.09[/TD]

[TD="align: right"]7[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]$79.82[/TD]

[TD="align: right"]8[/TD]
[TD="align: center"]4[/TD]
[TD="align: right"]$69.16[/TD]

</tbody>

If I want to calculate a weighted average for the Cabinetry industry, how would I go about doing that? I have hundreds of lines of data.

 

[dcardno]

Put in a list of the industries that you want to calculate the W.Ave for - in you example I extracted the unique industries and listed them in C12:C15.
In B12 enter the formula

=SUMPRODUCT(--($C$2:$C$8 = C12), $B$2:$B$8, $A$2:$A$8) / SUMIF($C$2:$C$8, C12, $A$2:$A$8)

(then copy the formula down for as many rows of industry names that you have - expand for the number of rows as required)
This calculates the total of all products of Volume and Price where the industry is named in Cell C12, and divides it by the total of all the weights (where the industry is listed in C12).
If you have other criteria (years, regions, markets, etc), you have to add more 'validation' tests to the SUMPRODUCT function - that's the --($C$2:$C$8 = C12) part.
In that case, I would add a helper column all valued at "1" and then rather than the SUMIF function, I would use another SUMPRODUCT to get the total of weights by including the column(s) to validate, and then the columns of weights and the column of "1"s - the product of the weights and the ones will just be the values of the weighting factors, and SUMPRODUCT will sum them up for you

 

[colemenwilson]

YES! That worked perfect!! I'm having trouble understanding the additional criteria part. I do have 1 other criteria I need to add, I need to find WA for all rows that are Cabinetry and Enterprise level. Would I just add a list of the different levels (standard, professional, enterprise). If I added a list of those criteria to C17:C19 what would my new formula look like? Also Thank you so much for your help with the first part! You are an excel wizard!

 

[dcardno]

Sorry for being so long to pickup this thread - I hope you haven't had to do tedious manual work; and if you have had to, I hope you won't have to do it again. I thought over my last answer, and I was the victim of some confused thinking - you don't need a helper column in this case, although you might need one in other situations. I added one column (Column D) to contain "location" - the values alternate between "OR" and "WA" with "OR" in the first row

In Cells C17 and C18 I have entered "Cabinetry" and in Cells D17 and D18 I have entered "OR" and "WA," respectively. In Cell B17 I entered the formula:

=SUMPRODUCT(--($C$2:$C$8 = C17), --($D$2:$D$8 = D17), $A$2:$A$8, $B$2:$B$8) / SUMPRODUCT(--($C$2:$C$8 = C17), --($D$2:$D$8 = D17), $A$2:$A$8)

I can copy that down to B18, as well.

SUMPRODUCT ordinarily multiplies (and adds) the values in each range provided as an argument. When instead of a range it is given a logical test, it creates a string of {true, false, true, true...} values. The double negation coerces these values into 1s and 0s (True = 1, False = 0), and then performs the multiplication. In this case, the numerator multiplies the weights of each row by the value in the row, and by the truth-value of meeting both of the criteria: the denominator is the weight only times the same truth values - the result is the sum of the weighted values divided by the sum of the weights, or the weighted average.