Select a varying range of rows for a formula

Question

I have an Excel spreadsheet with 73300 rows (representing firms) from 144 countries and dozens of columns (with entries for each firm, e.g. sales, # of employees, private or state-owned, market value, etc). This panel is unbalanced, i.e. the number of firms in each country is different. How do I get an average of column values (sales, # of employees, etc.) for each COUNTRY without having to manually go through all 144 countries?

I know how to do the "calculate the average of for the COUNTRY column a cell equals the previous cell and put NA otherwise" but I don't know how to tell the program to select the varying quantity of rows. Say, I have 533 firms in country 1, 274 firms in country 2, 797 firms in country 3, etc. How do I write the "find the average sales figure over all firms in a given country" without copying and pasting the average formula 144 times? THANK YOU!

You can use a pivot table and summarise the values by average. — Jerry
I guess by "pivot" you mean transpose? But I am not sure what the rest means or how to do it. — user2777766
Not "pivot", but "pivot table" xP See some documentation here and there are lots of resources on the web. — Jerry

Ross Larson Ross Larson · Accepted Answer · 2013-09-13T20:32:15

use remove duplicates (Data tab) on a copy of the country column to get a unique list. put this on its own worksheet. I recommend sorting it afterwards (not required).

Then, add the developer tab (file->options->customize ribbon) and insert a combo box control (don't use the activeX controls though, they are slightly more tricky) that gets data from that list (this is why i recommend alphabetizing) remember to link its output to a cell (I recommend naming that cell and then placing the dropdown over that cell).

Right click on the combobox after you place it and select format control. Use input range for the unique country list and cell link for the output.

Finally, insert the following formula.

=averageif(CountryColumn, index(UniqueCountryList, valueFromDropdown), SalesColumn)

Averageif documentation

http://office.microsoft.com/en-001/excel-help/averageif-function-HA010047433.aspx

index documentation

http://office.microsoft.com/en-us/excel-help/index-function-HP010342608.aspx

OR use a pivot table as JErry suggested, but this will be the start of a nice dashboard (especially if you keep data on its own sheet, the unique country list on a separate sheet and the dropdown and formula on a dashboard sheet)

Select a varying range of rows for a formula

1 Answers