Weighted Averages by County

[Jo]

Hi,


I have a data sheet with multiple rows of data for 50 different U.S. counties. The number of rows for each county varies. For example, Broward County has 175 records, while Cook County has 230. Each record is weighted. I would like to find the weighted average by county for each county. Thanks to Chandoo, I know how to find the weighted average for a single county using sumproduct. But I don't see how to account for the multiple counties in the formula. Is it possible to do so? Many thanks -- Jo

 

[Luke M]

In the future, please post the formula you already have, as it can help give us a reference point. Rough outline:


=SUMPRODUCT((CountyNameRange=SpecificCountyName)*(WeightRange)*(ValueRange))/SUMPRODUCT((CountyNameRange=SpecificCountyName)*(WeightRange))

 

[SirJB7]

Hi, Jo!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords "weighted average" and press Search button. You'd retrieve many links from this website, like the following one(s), maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


http://chandoo.org/wp/2010/06/15/weighted-average-excel/

http://chandoo.org/forums/topic/weighted-average

http://chandoo.org/wp/tag/average/

http://chandoo.org/forums/topic/average-of-averages (take care of the considerations regarding average of averages, if applicable.


Regards!

 

[SirJB7]

@SirJB7

Hi, myself!

How many times should I say that I must remember to press "Send Post" button after writing?

Some guys never learn...

Regards!

PS: Comment wrote just after original post

 

[Jo]

Hi,


Thank you for the responses, and apologies for violating list etiquette. By way of introduction: I am an intermediate Excel user who generally uses the software to explore trends in public data sets available from government agencies or websites. At the moment, I am interested in some county-level sample data that require applying weights, which are provided with the data set, before comparing trends among counties.


I did search the list archive before posting my question -- which is how I found this formula:


=SUMPRODUCT(A1:A10,B1:B10)/SUM(B1:B10)


Sumproduct worked perfectly for a single county; however, my data set includes records for multiple counties compiled in a single sheet, and I had hoped to apply the function by county without manually entering the different data ranges. Luke's message looks like it might do that by using named ranges; I will see whether I can make that work.


In the meantime, rather than using sumproduct, I have applied the weights using math formulas and then a pivot table come up with the county-level figures.


Thanks again for the responses.


Jo

 

[SirJB7]

Hi, jo!

Glad you solved it. Thanks for your feedback and maybe you want to upload the solution so as it might help other people too. Welcome back whenever needed or wanted.

Regards!