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:

=IF($D$97<=E$11,IF($B$5=E$12,SUM(IF((Subdivision= $B$3)*(Builder=$B28)*(Date
=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)*(D atabase!$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!

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

"Karl Burrows" wrote in message
...
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:


=IF($D$97<=E$11,IF($B$5=E$12,SUM(IF((Subdivision= $B$3)*(Builder=$B28)*(Date
=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)*(D atabase!$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!

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" wrote in message
...
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

"Karl Burrows" wrote in message
...
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:



=IF($D$97<=E$11,IF($B$5=E$12,SUM(IF((Subdivision= $B$3)*(Builder=$B28)*(Date
=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)*(D atabase!$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!

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

"Karl Burrows" wrote in message
...
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" wrote in message
...
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

"Karl Burrows" wrote in message
...
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:




=IF($D$97<=E$11,IF($B$5=E$12,SUM(IF((Subdivision= $B$3)*(Builder=$B28)*(Date
=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)*(D atabase!$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!

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" wrote in message ...
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:

=IF($D$97<=E$11,IF($B$5=E$12,SUM(IF((Subdivision= $B$3)*(Builder=$B28)*(Date
=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)*(D atabase!$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!