Ugly Problem

Here's what I've got...

Need to summarize data by person (count, median, 95th percentile) by month for the last 12 months. I've got a query that returns ~6000 rows x 10 columns and I did a pivot table to get all the unique person names (~40). I then added columns for the months and entered array formulas (for the count, median, 95th percentile) comparing the person's name and month - pretty cool and it works to a degree...

However, in order to summarize variations of the same data I end up with multiple array formula tables - each 40 rows by 12 cols - and performance is terrible after the 4th table. It's been recalculating for over 90 min. I recently implemented dynamic named ranges and that seemed to help a bit.

In researching I understand that one of the drawbacks to array formulas can be slow performance and the recommended solution is to use database formulas. Problem is how do I best organize the criteria when I have 40 names to summarize over 12 months - wouldn't I need 480 criteria combinations? Or is there a simpler way to handle those criteria combinations. Regardless, as a new person is added, I lose the dynamic nature of the array formulas. Not to mention there is no DPERCENTILE function.

Alan

Hi,

As you have started with a pivot table, you may continue using it for the
calculations. There are some that are standard (like count), and others that
may be done either with the Field options or calculated fields.
For the ones that couldn't be made that way, you may use database functions
directly with the pivot table, as the underlying data is there, or use the
GETPIVOTDATA and craft the formulas based on that.

Hope this helps,
Miguel.

"Alan Graybosch" wrote:

>
> Here's what I've got...
>
> Need to summarize data by person (count, median, 95th percentile) by
> month for the last 12 months. I've got a query that returns ~6000 rows
> x 10 columns and I did a pivot table to get all the unique person names
> (~40). I then added columns for the months and entered array formulas
> (for the count, median, 95th percentile) comparing the person's name
> and month - pretty cool and it works to a degree...
>
> However, in order to summarize variations of the same data I end up
> with multiple array formula tables - each 40 rows by 12 cols - and
> performance is terrible after the 4th table. It's been recalculating
> for over 90 min. I recently implemented dynamic named ranges and that
> seemed to help a bit.
>
> In researching I understand that one of the drawbacks to array formulas
> can be slow performance and the recommended solution is to use database
> formulas. Problem is how do I best organize the criteria when I have 40
> names to summarize over 12 months - *wouldn't I need 480 criteria
> combinations*? Or is there a simpler way to handle those criteria
> combinations. Regardless, as a new person is added, I lose the dynamic
> nature of the array formulas. Not to mention there is no DPERCENTILE
> function.
>
> Alan
>
>
> --
> Alan Graybosch
> ------------------------------------------------------------------------
> Alan Graybosch's Profile: http://www.excelforum.com/member.php...o&userid=34342
> View this thread: http://www.excelforum.com/showthread...hreadid=541110
>
>