A
Alan Graybosch
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
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