*Todayâ€™s author, Monica Poinescu, a Software Developer in Test on the Excel team, discusses two different approaches to analyzing data in Excel.*

**Edit:** I’ve attached a file at the bottom of this blog that contains spreadsheets of the examples discussed in this post.

My earlier blog on the new Excel 2007 function SUMIFS spawned a very interesting discussion (thanks to everyone who posted comments there): when trying to analyze/aggregate data in a table, how do we decide whether to use functions versus PivotTables?

This blog outlines reasons to use one option or another. To better illustrate the two alternatives Iâ€™ll consider a real estate inspired example: letâ€™s say I have list of homes for sale and their corresponding characteristics:

and Iâ€™m trying to find the average price for those homes which have at least 3 bedrooms, a garage and are between 5 and 10 years old. Just by looking at the table, we see that only house3 and house4 satisfy all conditions and the average of their prices is 312,500.

Here is a functions based solution:

=AVERAGEIFS(G2:G6,C2:C6,”>2″,E2:E6,”yes”,F2:F6,”>1999″,F2:F6,”<2004″)

which returns $ 312,500.

To build a corresponding PivotTable, one can use several filters:

Advantages of PivotTables:

- While AVERAGEIFS is limited to using at most 127 conditions, PivotTables can handle more than 127 conditions.
- The elements of the set that fulfill all conditions are listed in the resulting PivotTable.
- PivotTables have a lot of flexibility: the ease of use offered by the new UI allows for a very quick detailed analysis of different available options. Nested layers in a PivotTable offer added results visualization.
- Several different approaches are possible: one can construct different PivotTables that answer the same question above.
- Particularly useful for large data sources: when relying on an external data source, you donâ€™t need to bring all the data in Excel and one could, for example, use OLAP databases.

Advantages of using functions:

- Itâ€™s easier to see in one glance all the conditions being used either by looking in the formula bar, or by listing all criteria in separate ranges.
- The result updates immediately when adding rows to the source table, while PivotTables need to be refreshed.
- The criteria, when referenced in a cell, could be as well the result of another formula, while value filters in PivotTables can only use constants.

For example, in the formula above, one could replace

=AVERAGEIFS(G2:G6,C2:C6,”>2″,â€¦

with

=AVERAGEIFS(G2:G6,C2:C6,I4,â€¦

where I4 contains another calculation ( e.g. =”>”&FIXED(SUM(1,1),0)).

The image below shows a corresponding PivotTable filter:

The last field will not accept formulas, only numbers. - Formulas take little space and are easy to move around in a sheet.

Note that in both cases you can use the wildcard characters to define criteria. Also both solutions deal in a similar manner with missing data or errors in the range.

I don’t know if I can be fully objective on this question because I’m more of a formula person myself. If I’ve missed a reason you should pick one approach over another, feel free to let me know the reason you use formulas or PivotTables for summarizing data by leaving a comment.