Analyzing Data: Functions or PivotTables

re: Analyzing Data: Functions or PivotTables

Stuart — Tue, 24 Mar 2009 03:33:22 GMT

Another big advantage of the pivot table is being able to drill down on the information to quickly see the items that meet your criteria.

re: Analyzing Data: Functions or PivotTables

Jeff L — Tue, 24 Mar 2009 14:07:44 GMT

To me this question is especially valid, but from a different perspective: Which is more efficient/performant when analyzing large data sets?

Your example is ironically perfect for me: I am in the real estate industry building analytical models based on transactional data of thousands of rows. Before learning about SumIfs I built the models to use pivot tables to aggregate, then used indexes to find my value in the PT. Can Sumifs, AverageIfs, etc offer the same performance as the Pivot Table, or will a large reporting toolset bog down?

I'd love to hear if anyone has insight to that.

re: Analyzing Data: Functions or PivotTables

Jeff L — Tue, 24 Mar 2009 14:07:58 GMT

To me this question is especially valid, but from a different perspective: Which is more efficient/performant when analyzing large data sets?

I'd love to hear if anyone has insight to that.

re: Analyzing Data: Functions or PivotTables

Charles Williams — Tue, 24 Mar 2009 20:05:17 GMT

Both SUMIFS/COUNTIFS and Pivot tables are very efficient compared to SUMPRODUCT/array formulae.

For large datasets one major factor in the choice is the useage scenario:

if you are looking for what-ifs calculations by changing the input data/calculations SUMIFS/COUNTIFS would be a good choice.

If your input data is relatively static but you want to do whatif analysis with drilldown then Pivot Tables are a good choice.

If you just want to aggregate relatively static data and then do a relatively standardised report either will do, but a third alternative of an SQL aggregation query feeding either a summary sheet or a pivot table might be best.

re: Analyzing Data: Functions or PivotTables

sam — Wed, 25 Mar 2009 09:07:51 GMT

Unfortunately the limited aggregate functions available in a pivot table and the limited functions supported in a calculated fields and items make use of formulas unavoidable (either inside the SQL Statements - for external Data or in adjacent columns)

re: Analyzing Data: Functions or PivotTables

Jeff L — Wed, 25 Mar 2009 20:22:08 GMT

@ Charles, Interesting that you mention using SQL...in some of the things I've done that seemed like it would be a more efficient way, but the methods I tried either failed miserably (programmatically) or were ridiculously slow.

But that said I was probably not being very efficient. How would you apply the SQL to an in-sheet data set--create an array and use the array as the "from"?

re: Analyzing Data: Functions or PivotTables

sebastien mistouflet — Thu, 26 Mar 2009 05:59:13 GMT

I try to use Pivots as much as possible, but:

- Pivots don't handle complex formulas (eg: rule-based accounting...)

- and do not deal with different nested groups, eg in column: Sales by Product Line, followed by another metric by another grouping, say employee salaries by {manager, Non-manager}.

It is sometimes possible to precompute in the data table directly and use the new precomputed fields in the pivot, but not always. In that case the Formula-type Report works better.

Now I try to separate data entry and reporting so I use SQL most of the time to pull to the reporting book.

Jeff L, you can use the Excel odbc driver to query a *closed* XL file like a database. You can try on an *open* XL file too, but there is still (i believe) a memory leak in the driver in that case.

re: Analyzing Data: Functions or PivotTables

DaveP — Thu, 26 Mar 2009 20:27:06 GMT

Consider a simple Data List (2003) and Subtotal for average, number, max, min, and/or sum.

Use AutoFilter and secure required numbers with =SUBTOTAL(F1,$G$9:$G$1000)

re: Analyzing Data: Functions or PivotTables

Han — Sun, 29 Mar 2009 05:27:05 GMT

Would you mind to offer a download link for each table you have in your blog post? So that readers can practice with them.

Thanks.

re: Analyzing Data: Functions or PivotTables

findsarfaraz — Thu, 02 Apr 2009 13:19:15 GMT

If I understood correctly.

Averageif = Sumif/countif

Also, can we have downloadable tables so the beginner can practice.

re: Analyzing Data: Functions or PivotTables

Spence Lloyd — Thu, 02 Apr 2009 15:10:54 GMT

Question: Can a SUMIFS criteria include a cell reference? I can't seem to figure out how to make that work.

re: Analyzing Data: Functions or PivotTables

Monica Poinescu — Fri, 03 Apr 2009 20:59:01 GMT

You can now find the files used for the examples I used in my blog above- see 'Attachment(s): examples.zip'.

I also included some examples of criteria passed as a cell reference and an example using criteria with wildcards.

I hope that helps - and thanks for your comments.

CCNA_Karox

karrroxtraining@gmail.com — Thu, 09 Apr 2009 08:49:06 GMT

Thanks for the information. I had been looking for such a simple example which help describe things in such a simple but informative fashion. Thanks.

re: Analyzing Data: Functions or PivotTables

EVaino — Tue, 14 Apr 2009 23:32:42 GMT

I was just having this discussion with a colleague last week. I've always been a formula person but he convinced me to give Pivot tables a try. I'm still skeptical though. I do acknowledge that functions can bog things down, especially array functions on large workbooks. My biggest reason for not liking pivot tables is that often I want to take many different "slices" of a table. If I'm not mistaken, using pivot tables would require me to create a separate one for each analysis? They're super easy to create, but to me that results in a cluttered workbook if there are dozens of little pivot tables everywhere. I'd rather use formulas so my results can be nice and tidy - and if anyone wants to see where the data came from they can easily check the formula. Just seems cleaner. Especially if they all need to be refreshed too...(although presumably there is a refresh all option?).

good discussion.

re: Analyzing Data: Functions or PivotTables

david — Wed, 22 Apr 2009 11:30:10 GMT

How to add CalculatedField to pivottable using c# ?

Thks.