How to run a query in Excel 2007?

January 25, 2008 8:20 AM

How do I group values by a variable in Excel 2007? Essentially, I want to run a query.

I have a table with columns like city, state, population, average income, average house price. This table is constantly being updated and amended, with the new info tacked on at the end. So for example, I might have (all numbers completely pulled out of my ass):

Milwaukee, WI, 600000, 40000, 150000
Bozeman, MT, 30000, 25000, 100000
Chicago, IL, 2000000, 60000, 200000
Madison, WI, 100000, 50000, 125000
Springfield, IL, 100000, 40000, 100000

I need to group the cities by state and calculate stuff like total population, average income and house price by state. So I want to end up with

WI, 700000, 45000, 137500
IL, 2100000, 50000, 150000

I'm familiar with Access but I can't use that for this project because the end user only has access to Excel (2007). I understand SQL (mostly), but I don't know if it can be used in Excel and if it can, I don't know where to put it. I don't know any other code well enough. Please, if you have some magic line of code that will work, tell me exactly where to put it, because I won't know.

posted by desjardins to computers & internet (8 comments total)

can't you get summaries like that in a pivot table?
posted by birdherder at 8:24 AM on January 25

(Don't know if this is for Excel 2007 or earlier versions);
Data -> Filter -> AutoFilter.
Drop down arrows appear above the titles of columns.
Select the one for WI.

Will this work with the way your data is set up?
posted by salvia at 8:30 AM on January 25

Then to perform a particular operation (average this, add up that) on only the filtered rows, use one of the SUBTOTAL functions. Put it at the bottom of all the entries, but after you've set up the Autofilter. Details:

The SUBTOTAL Function in Excel is used to perform a specified function on a range of Auto filtered cells. When the Auto Filter has been applied the SUBTOTAL function will only use the visible cells, all hidden rows are ignored. The operation performed is solely dependent on the number (between 1 and 11) that we supply to it's first argument Function_num For example;

=SUBTOTAL(1,A1:A100)

will Average all visible cell in the range A1:A100 after an Auto Filter has been applied. If all rows in A1:A100 are visible it will simply Average them all and give the same result as

=AVERAGE(A1:A100)

The number for the first SUBTOTAL argument, Function_num, and it's corresponding function are as shown below

Function_Num

Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

The only annoying part is having to copy/paste-special-->values only the equation results into another table so they don't disappear when you filter for another variable.

But yeah, depending on exactly what you want to end up with, I'd probably try the pivot table first, like birdherder suggested.
posted by salvia at 8:39 AM on January 25

Thanks guys! Will a pivot table pick up new data that is entered or will I have to redo it?
posted by desjardins at 8:52 AM on January 25

A pivot table is for a specific range, and the bigger the range, the bigger the file size. But you could make a pivot table for entire columns by highlighting the column headings (A-E).

The pivot table won't refresh automatically when new data is added, but you can right click on it and choose refresh.
posted by Pants! at 9:01 AM on January 25

I would use VLOOKUP.

VLOOKUP finds a variable in a datasheet, reads the column you specify, and reads it into the cell where the formula is.

it's hard to explain but easy to do. let me know if you want more information.
posted by entropone at 9:43 AM on January 25

Yeah, you should be able to update a pivot table if you change values (I think you can right-click on the pivot table to see that option). I'm not sure what will happen if you add entire rows, though. When you create the pivot table it asks what the data range is (default being the whole thing). So, if it gets set to A1 : Z99 and then you add a new row at line 8, the data range might automatically switch to A1:Z7, A9:Z100.

The other issue with the pivot table is that to have multiple summary functions going on, you might end up having to create a dozen pivot tables.

Another option you might look into would be to do something with grouping and summary rows. "First sort the products by category, [ie state], and then select all the cells that contain data and open the Subtotal dialog box (Data menu, Subtotals command)..."
posted by salvia at 10:58 AM on January 25

For your groupings, the function you want is SUMIF.

For the example data you've given, presuming we're starting in A1, you'd have something like:

=SUMIF(B:B,"WI",C:C)

...which is saying sum the values on column C if column B matches "WI".

To do averages, you'd also use COUNTIF, so it'd be, say:

=SUMIF(B:B,"WI",D:D)/COUNTIF(B:B,"WI")
posted by pompomtom at 1:48 PM on January 25