Slow Calculating and optimizing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kurt - 26 Sep 2007 11:48 GMT

Hi

I have been using the expression =SUMPRODUCT(--(file'!$P$1:$P
$65500="txt");'file'!$H$1:$H$65500)
to summarize all cells in column H where column P= "txt"

I have 1200 of theese expressions and it make the startup and
recalculating very slow (3-4 minutes)

The 65500 is choosed to be sure that I never ran out of Data and it
was not based on an analyze.

To optimize this cut the 65500 down to a relevant size ( ex. 10000)
and then the calculation time is reduced dramatically.

regards

Kurt

Reply to this Message

Roger Govier - 26 Sep 2007 12:05 GMT

Hi Kurt

Try creating dynamic ranges for your data
For example
Insert>name>define> Name colP Refers to =OFFSET($P$1,0,0,COUNTA($P:$P))

Repeat for colH

Then use
=SUMPRODUCT(--(colP=".txt");--(ColH))

To ensure that all of your ranges have the same length, use COUNTA($P:$P) in
all cases, or whichever column will not have any blank data within it

I usually prefer to have one named variable lrow where I would set it as
=COUNTA($P:$P)
Then all the other named ranges would be, for example
=OFFSET($P$1,0,0,lrow)

Signature

Regards
Roger Govier

> Hi
>
[quoted text clipped - 14 lines]
>
> Kurt

Reply to this Message

Tom Ogilvy - 26 Sep 2007 12:32 GMT

While Roger gives good advice for dynamic ranges, in this case, you don't
need to us sumproduct at all. Since you only have one condition, the much,
much faster

=Sumif('file'!P:P,"txt",'File'!H:H)

would work fast and doesn't restrict your range.

If 'file" will be closed, then Sumif does not work against a closed workbook
and you would have to go with Sumproduct and perhaps use Roger's suggestion.

Signature

Regards,
Tom Ogilvy

> Hi Kurt
>
[quoted text clipped - 33 lines]
> >
> > Kurt

Reply to this Message

Jim Thomlinson - 26 Sep 2007 17:00 GMT

There is a trade off to be had if you use the dynamic named range in that
offset is a volatile function. That means that all 1,200 functions become
volatile and while they will calculate faster they will also calculate more
often. This strategy could work for you or against you depending on the time
savings. If it does not work out then you might want to consider using some
code to redefine the range names when then overall number of rows of source
data increases or decreases.

Signature

HTH...

Jim Thomlinson

> Hi Kurt
>
[quoted text clipped - 33 lines]
> >
> > Kurt

Reply to this Message

Roger Govier - 26 Sep 2007 20:16 GMT

Hi

Whilst I agree entirely with Tom's advice to the OP about SUMIF, and that
this would speed up the calculations far more, and with your point about
volatility using OFFSET, I thought after posting, I should have offered the
OP INDEX instead as a non-volatile solution.
I went out immediately after posting and have only just returned.

Again define lrow as per my previous posting =COUNTA($P:$P)
Then use the following for the column name colP
=$P$1:INDEX($P:$P,lrow)

Signature

Regards
Roger Govier

> There is a trade off to be had if you use the dynamic named range in that
> offset is a volatile function. That means that all 1,200 functions become
[quoted text clipped - 48 lines]
>> >
>> > Kurt

Reply to this Message