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