Array formulas vs Getpivotdata - comments invited

[Laurence Lombard]

I think that the GETPIVOTDATA function should be promoted above the use of
array formulas. I would appreciate comments from the experts.

My major problem with array formulas (and the much more user friendly
SUMPRODUCT function which I used later) is that they slow down a workbook a
lot as they are computation demanding and are recalculated every time a cell
is changed anywhere in the workbook. I am probably the only person still
using Windows 98 and a PII with office 2000, so maybe it is not an issue for
others!

I have managed to get the same result using a pivottable and the
GETPIVOTDATA function with a dramatic increase in speed. In my opinion array
formulas are mostly used in a report situation, therefore the "refresh when
needed" facility of the associated pivot table is more than adequate.

It takes a bit of time to figure out the syntax of GETPIVOTDATA as it is not
that well explained. I thought I would add an example:

=GETPIVOTDATA(Table1,"SALESPERSON['"&A1&"'] QUARTER['" & A2 & "'] AMOUNT")

Here Table1 is the name of a cell in the pivot table. The formula returns
the total AMOUNT for SALESPERSON stored in A1 and QUARTER stored in A2.

Regards
Laurence

 

[JE McGimpsey]

First, I agree with you - using PTs and GETPIVOTDATA is in many cases
much more efficient than using array formulae.

The source of GETPIVOTDATA's efficiency is that the array calculations
are done when the PT is constructed or refreshed. This gives it a
significant advantage over a volatile array formula, since it's just a
lookup function.

But that's also a real disadvantage. Array formulae recalculate when
their source data changes. PT's don't, they have to be manually
refreshed. If you want automatic calculation, GETPIVOTDATA doesn't cut
it.

A second disadvantage is having to construct a PT in the first place.
While I like PTs and use them frequently, they have a significant
learning curve, and a very significant resistance among most users. One
could argue that this *shouldn't* be true, but it is.

A third disadvantage is actually the PT's strength. PT's make it easy to
manipulate data; add, subtract, or rearrange fields; etc. Doing that
usually fubars the GETPIVOTDATA function.

 

[Laurence Lombard]

Thank you for your reply.

It would be useful if one had an option to make array functions "refresh
when required" in cases where it is not necessary to have them live. My
workbook is much faster since I worked around the array functions -
including refreshing PTs.

Regarding your 3rd point. I have dedicated PT's as the source of the
GETPIVOTDATA function so that they there is no temptation to change them.
Having multiple PTs of the same source data does not increase workbook size
and they are all refresshed at the same time.

PT's need to be promoted - once you know of them you cannot do without them!

Laurence

First, I agree with you - using PTs and GETPIVOTDATA is in many cases
much more efficient than using array formulae.

The source of GETPIVOTDATA's efficiency is that the array calculations
are done when the PT is constructed or refreshed. This gives it a
significant advantage over a volatile array formula, since it's just a
lookup function.

But that's also a real disadvantage. Array formulae recalculate when
their source data changes. PT's don't, they have to be manually
refreshed. If you want automatic calculation, GETPIVOTDATA doesn't cut
it.

A second disadvantage is having to construct a PT in the first place.
While I like PTs and use them frequently, they have a significant
learning curve, and a very significant resistance among most users. One
could argue that this *shouldn't* be true, but it is.

A third disadvantage is actually the PT's strength. PT's make it easy to
manipulate data; add, subtract, or rearrange fields; etc. Doing that
usually fubars the GETPIVOTDATA function.

I think that the GETPIVOTDATA function should be promoted above the use of
array formulas. I would appreciate comments from the experts.

My major problem with array formulas (and the much more user friendly
SUMPRODUCT function which I used later) is that they slow down a workbook a
lot as they are computation demanding and are recalculated every time a cell
is changed anywhere in the workbook. I am probably the only person still
using Windows 98 and a PII with office 2000, so maybe it is not an issue for
others!

I have managed to get the same result using a pivottable and the
GETPIVOTDATA function with a dramatic increase in speed. In my opinion array
formulas are mostly used in a report situation, therefore the "refresh when
needed" facility of the associated pivot table is more than adequate.

It takes a bit of time to figure out the syntax of GETPIVOTDATA as it is not
that well explained. I thought I would add an example:

=GETPIVOTDATA(Table1,"SALESPERSON['"&A1&"'] QUARTER['" & A2 & "'] AMOUNT")

Here Table1 is the name of a cell in the pivot table. The formula returns
the total AMOUNT for SALESPERSON stored in A1 and QUARTER stored in A2.