Named Ranges - Slow Workbooks!

[Karl Burrows]

I posted this earlier, but it went in a different direction...

I have several workbooks where I replaced array formulas with named ranges
to pull data from a tab that imports data from an Access database. There
were several thousand of these formulas. An example would be:

=E$9)*(Date<F$9),(Count),0)),""),"")

I created the names since I wanted to have a dynamic range of values to
search since the database would return varying rows of data depending on
what was input in the database:

=OFFSET(Database!$E$3,0,0,COUNTA(Database!$E:$E),1)

When I replaced the original formulas:

=IF($D$97<=E$11,IF($B$5>=E$12,SUM(IF((Database!$C$3:$C$2500=$B$3)*(Database!
$D$3:$D$2500=$B28)*(Database!$E$3:$E$2500>=E$9)*(Database!$E$3:$E$2500<=F$9)
,(Database!$E$3:$E$2500),0)),""),""))

with the named ranges, the workbooks have ground to a halt. It now takes
forever for them to update (they weren't speed demons in the first place
with so many arrays) and Macros run incredibly slow (5 minutes or more to
run!).

Did I do something wrong? Are there limitations to named ranges? Is there
something with a dynamically named range that would create this problem? I
thought I was trying to make the workbooks more efficient and allow for an
expanding range of data without having to 'anticipate' how many rows to
include in the array making less monitoring for me.

Help! Thanks!

 

[Charles Williams]

OFFSET is volatile. This means that every formula that contains OFFSET or is
dependent on a formula that contains OFFSET will always calculate at every
recalculation.
COUNTA is quite slow because it scans every cell in the used range of the
column you are counting.
Array formula are quite slow because each formula requires the creation and
calculation of virtual columns/rows, and all of the formulae in these
virtual columns/rows have to be calculated each time the array formulae is
calculated.

The solution is one or more of
- use a completely different approach (ie pivot tables or do your filtering
in ACCESS or ...)
- minimise the amount of repeated calculation by using helper cells/columns
(only do your COUNTA once, only do Database!$C$3:$C$2500=$B$3 once etc)
- sort your data and do subset calculations
- use more efficient formulae (DSUM, SUMPRODUCT, ...)

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[onedaywhen]

Go pay homage to the site which is Charles Williams's Calculation Secrets:

http://www.decisionmodels.com/calcsecrets.htm

There's lots of details info there, including tips for better dynamic ranges.

--

 

[Karl Burrows]

Our group couldn't handle Pivot Tables (as much as I have tried!), plus we
need to print these for 15 monthly status reports (linked to Word).

So how would I create helper cells for arrays and the dynamic named ranges?
I counted and I think there are about 4500 array formulas (each month for a
5 year range for 5 or 6 builders on each worksheet x 15 worksheets) that are
almost identical (the only thing that changes is the current month date
range search). The array searches by Subdivision, then builder, then start
and end of month range. There are 15 sheets that are duplicates of each
other separating each subdivision. Each array includes the dynamic named
ranges for subd, builder, start and end date.

If you have any suggestions, it would be greatly appreciated! I have been
working to improve this for several years now!

Does your FastExcel program work on other users computers if I run it to
improve performance and then it is opened by another computer? In other
words, can I generate improved workbooks and then share them or do they have
to have the add-in to run some of the features?

Thanks!

 

[Charles Williams]

Hi Karl,

Since I don't know the layout of your sheets you may need to adjust this a
little, but here is my suggestion:

- Sort the data by month within builder within subdivision so that all the
data is in a contiguous block of rows for each "array formula"
- calculate the start and end row number for each block of rows in helper
rows/columns somewhere (this is the only difficult bit, you can use
functions like MATCH and COUNTIF etc if there are varying numbers of
builders within each subdivision).
- use OFFSET and the start end end row numbers to return a range which is
the contiguous block of rows
- this process should have reduced the number of conditions to either zero
(so use SUM) or 1 (use SUMIF) so that you dont need to use an array formula
- If you still have multiple conditions within the contiguous block of rows
then you have to use an array formula, but it will only cover a very small
number of rows and fewer conditions and so will calculate much faster.

There is an example of this technique described at
http://www.DecisionModels.com/optspeedk.htm
Also there is another example of using this technique in the FastExcel
sample problem which you will find at
http://www.DecisionModels.com/Downloads.htm

You can use FastExcel to analyse and improve workbooks and you will only
need it installed on the other users computers if you choose to use the
additional FastExcel functions or the additional FastExcel Excel calculation
modes.


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm