Life Without Pivot Tables
The most basic way to calculate summary information is to
SUMIF( ) and
) functions. For
example, to find the average annual purchases for an individual in
New York, you first calculate the total purchases made by all New
York customers. Here's the formula you need:
=SUMIF(C2:C11, "New York", E2:E11)
This formula scans the City column (C2 to C11) looking for the text. Every time it finds a match, it adds the corresponding purchase amount from the Annual Purchases column (E2 to E11).
Once you have the total purchases, you need to divide this figure by
the number of New York customers. To count the number of New York
customers, you use the
COUNTIF( ) function.
Here's the revised formula for finding the average annual purchase amount:
=SUMIF(C2:C11, "New York", E2:E11)/COUNTIF(C2:C11, "New York")
Finally, you can make this formula more generic so that you don't need to create a new version for each city. Instead, change the formula so that it retrieves the text it should match (the city's name) from a cell just to the left of where you're going to place the formula. (As shown in Figure 2, this group of "city" formulas resides in cells B15, B16, and B17.) Then, you can copy your formula into multiple rows. The formula won't change, but, in each row, the cell on the left will have a different value, instructing the formula to search for customers in a different city.
Figure 2. These three summary tables retrieve their data from the table shown in Figure 1 and calculate average purchases by grouping the customers into different categories. The purchase-by-city summary shows that there isn't a significant amount of difference based on location (although Seattle customers tend to spend a little less). The purchase-by-education summary demonstrates a dramatic difference, with less educated individuals making smaller purchases. Finally, the purchase-by-gender comparison turns up no variance at all.
Here's the perfected formula:
=SUMIF($C$2:$C$11, A15, $E$2:$E$11)/COUNTIF($C$2:$C$11, A15)
Note that in this formula, all the search ranges are fixed as absolute references using the dollar sign ($). The search text isn't fixed—that way, when you copy the formula to a new row, the formula uses the search text on that row.
Figure 2 shows several summary tables that use this technique for calculating averages.
In this example, building each table isn't terribly difficult, but the situation clearly becomes more tedious the more ways you want to compare the same data. It's not difficult to imagine a more realistic scenario where you might want to look at customer purchases based on age bracket, income level, and the answers to different customer survey questions. To get the full picture with this information, you would need to build each table from scratch.
In fact, in some of these more complex scenarios, you might need to group and then subgroup your information. Figure 3 shows a more advanced example of a table that calculates the variance in average annual purchases by city, and then shows the subdivided totals in each city by gender.
Figure 3. This summary table is a little more advanced. It groups and then subgroups data, which lets you find hidden trends. For example, in this case it identifies New York females as the best purchasers. Of course, there are far too few rows in this example for the results to be truly persuasive. In a table with thousands of rows, however, a grouped and subgrouped breakdown becomes much more meaningful.
The summary in Figure 3 performs
a two-dimensional comparison. In other words, it
compares two different groupings—one by city in different rows,
and one by gender in different columns. This is a step up from the
one-dimensional summary tables you saw earlier, but
it's also far more difficult to correctly calculate.
You could use the
SUMPRODUCT( ) function, or create the table
in two steps (first grouping the records by city, and then totaling them by gender).
In either case, life gets much more complicated, and
that's when pivot tables really shine.