calculate weighted average

[heather]

hi, I have a very large dataset similar to this:
a(area code, not unique); b(lotplan - unique, only one area code); c)water
measurement.

i have about 1000 area codes, each one of which is made up of 1-300 lotplans
with water measurement. i want to calculate a weighted mean of all water
measurements per area code.

I can do a straight average easily enough with subtotals, but am stuck doing
a weighted mean for the same thing.

any help much appreciated

 

[Shane Devenshire]

Hi,

How is it weighted? Something is missing. All you seem to have is water
measurements. Are you really looking for the average per area code? that is
not a weighted average, but an average if. You also haven't given us any
idea of the layout of your data, so here is the general idea:

2003: =AVERAGE(IF(A1:A1000=AreaCode1,C1:C1000)
2007: =AVERAGEIF(A1:A1000,AreaCode1,C1:C1000)

 

[heather]

Sorry, I should have given more detail; I have a table as follows:

Area Code; LotPlan; Average2006; Av2007; Av2008; etc
302020 4SL55 666 555 555
302020 5SL55 555 333 444
302021 etc..

each LotPlan is unique, and linked to one Area Code (which is not unique)
eg, I have about 35000 rows of lotplans, each having an Average 2006 etc

and one Area Code might have from 1 - 450 lotplans in it (that is why I want
to weight the data).

What I want to do is calculate a weighted average per Area Code (for all the
LotPlans for each Year)

 

[Bernard Liengme]

Heather, I believe you are misusing the expression 'weighted average'
Suppose you have 3 bags weighing 5 lbs and 6 bags weighing 10 lbs. The
weighted average is found with
={ (3 × 5) + (6 × 10) } / (3 + 6)

You want the average for each area code.
You could do a weighted average if, for example, you knew the area of each
Lot (not sure that result would mean much!)

Let us say your area codes are in A2:A451 and the 3006 data is in C2:C451
Suppose G1 contains area code 302020
In H1 enter = SUMPRODUCT(--(A2:A451=G1), C2:C451) / COUNTIF(A2:A451,G1)
You could make a copy of column A and then filter it to get a unique list
Then you could copy H1's formula down the column after making this
modification
= SUMPRODUCT(--($A$2:$A$451=G1), $C$2:$C$451) / COUNTIF($A$2:$A$451,G1)

Another way would be to use a pivot table. It might take you a few tries to
get the hang of these but if you do much work like this, it will be time
well spent. Here are some resources.
Pivot table

http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/
best wishes

 

[mazorj]

Heather, I believe you are misusing the expression 'weighted
average'
Suppose you have 3 bags weighing 5 lbs and 6 bags weighing 10 lbs.
The weighted average is found with
={ (3 × 5) + (6 × 10) } / (3 + 6)

Which equals 8.333

Maybe I'm missing something, but how is that different from a simple
"mean" (average) value?

Mean = (5 +5 + 5 +10 +10 +10 +10 +10 + 10) / 9 = 8.333

I can see where you might have problems if the rows or columns over
which you did an AVG function had different numbers of blank cells and
the AVG function is ignoring blank cells. But your equation as
written yields the same as the mean. There must be something missing.

 

[heather]

Thanks for all your help, I think I will just use the straight average
(someone else told me to use a weighted average, hence my confusion...)
Incidentally, I originally wanted to use a pivot table, but the dataset was
too large!

 

[Roger Govier]

Hi Heather

Incidentally, I originally wanted to use a pivot table, but the dataset
was
too large!

Why so?
In your original post, you said you had around 35000 rows. A Pivot Table
would handle that data easily.