Speading up my Excel calculations.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Don - 11 Sep 2006 01:07 GMT

Hello,
In a nutshell: I am putting together a database. I created a search
"grid" that allows the user to choose a name from a data validation
list. After selecting a name, the grid (shows 4 locations across, and
the 12 months down) uses Sumproduct to count the number of units the
selected name has for each site, in each month.

The problem: I would like to use this database over a few years. Each
year I will be adding almost a thousand entries. I have been using a
set range, eg D7:D10000, for Sumproduct to search. I have an old
workstation at work, and after each entry it can literally take 2-3
full seconds for the workbook to re-calculate. It has been suggested
that I use a dynamic named range using
=OFFSET(Data!G7,,,CountA(Data!$B:$B),1), instead of the range I
indicated above. This would allow the formulas to only look in the
actual range, instead of a bunch of blank cells.

I haven't been able to put this to a try yet. So my question is---am I
on the right track? Is there a different technique I should be
considerin?

I hope I have explained this clearly enough. Please let me know if I
can supply any other need info.

Thanks,
Don

Reply to this Message

Bob Phillips - 11 Sep 2006 08:08 GMT

That could be one way to help. Others might be in having intermediate
calculations in helper cells, so that fewer cells are checked for recalc
each time.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Hello,
> In a nutshell: I am putting together a database. I created a search
[quoted text clipped - 22 lines]
> Thanks,
> Don

Reply to this Message

Don - 11 Sep 2006 11:50 GMT

Could you give me an example of this?
Thanks,
Don

> That could be one way to help. Others might be in having intermediate
> calculations in helper cells, so that fewer cells are checked for recalc
[quoted text clipped - 33 lines]
> > Thanks,
> > Don

Reply to this Message

Bob Phillips - 11 Sep 2006 12:09 GMT

As a very simple example, consider a formula in say D1 that checks if a
VLOOKUP operation is successful or not

=IF(ISNA(VLOOKUP("Bob",A1:D20,2,FALSE)),"",VLOOKUP("Bob",A1:D20,2,FALSE))

If you have the following the following in E1

=VLOOKUP("Bob",A1:D20,2,FALSE)

and then in D1 use

=IF(ISNA(D1),"",D1)

the VLOOKUP, which checks many cells is only invoked once, and then a much
shorter test is made in D1.

That is the basic technique, but clearly it can be adapted many ways.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Could you give me an example of this?
> Thanks,
[quoted text clipped - 36 lines]
> > > Thanks,
> > > Don

Reply to this Message

Epinn - 11 Sep 2006 17:04 GMT

Bob,

I also want to learn. I think I understand the concept, but as always I am having a hard time interpreting formulae. Before I pull any more hair, can you help me understand one thing first if you don't mind.

>>consider a formula in say D1 that checks if a

VLOOKUP operation is successful or not

I am kind of lost here because D1 is part of the table/array of A1:D20.

Am I missing something here?

Once I understand this, I'll try to figure out the rest like:

>>and then in D1 use

>>=IF(ISNA(D1),"",D1)

ISNA(D1) or ISNA(E1)

Yesterday I learned that for complex worksheets, primary calculations should be placed near the top of the worksheet and as far left as possible, and the calculations that are based on those primary calculations are placed later in the worksheet. I am not sure how to apply this "rule" to the location of a table (especially a huge one) used by VLOOKUP. Sometimes the table is in another workbook. Will things speed up if we include the table in the same worksheet? Is it even better if we place the table closer to the VLOOKUP formula to speed things up? But then the VLOOKUP formula may be a *primary* formula and I certainly don't want a huge table close to the top left of the worksheet.

Sorry Don, if you think I have gone off-track.

Bob, appreciate your wisdom.

Epinn

As a very simple example, consider a formula in say D1 that checks if a
VLOOKUP operation is successful or not

=IF(ISNA(VLOOKUP("Bob",A1:D20,2,FALSE)),"",VLOOKUP("Bob",A1:D20,2,FALSE))

If you have the following the following in E1

=VLOOKUP("Bob",A1:D20,2,FALSE)

and then in D1 use

=IF(ISNA(D1),"",D1)

the VLOOKUP, which checks many cells is only invoked once, and then a much
shorter test is made in D1.

That is the basic technique, but clearly it can be adapted many ways.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Could you give me an example of this?
> Thanks,
[quoted text clipped - 37 lines]
> > > Thanks,
> > > Don

Reply to this Message

Bob Phillips - 11 Sep 2006 18:55 GMT

Epinn,

I wasn't referring to Don's original question, but a totally fictitious
situation. So my D1 was not intended to be part of Don's array.

As for all of those 'rules' that you quote, I personally don't give them
much credence. IMO the first thing to do is to deign a good spreadsheet, by
laying out the data well, it will make it far easier to extract the
information, and you can reduce the amount of calculating that is going on.
If and when you have a problem with performance, starting to look at where
the bottlenecks are, and look to improve those areas. The solution might be
moving some data around, reducing array formulae, dynamic ranges, UDFs, all
manner of things. Unfortunately, there is no simple, easy answer, that is
why you pay Excel consultants so much money :-)

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

I also want to learn. I think I understand the concept, but as always I am
having a hard time interpreting formulae. Before I pull any more hair, can
you help me understand one thing first if you don't mind.

>>consider a formula in say D1 that checks if a

>>and then in D1 use

>>=IF(ISNA(D1),"",D1)

ISNA(D1) or ISNA(E1)

Yesterday I learned that for complex worksheets, primary calculations should
be placed near the top of the worksheet and as far left as possible, and the
calculations that are based on those primary calculations are placed later
in the worksheet. I am not sure how to apply this "rule" to the location of
a table (especially a huge one) used by VLOOKUP. Sometimes the table is in
another workbook. Will things speed up if we include the table in the same
worksheet? Is it even better if we place the table closer to the VLOOKUP
formula to speed things up? But then the VLOOKUP formula may be a *primary*
formula and I certainly don't want a huge table close to the top left of the
worksheet.

Sorry Don, if you think I have gone off-track.

Bob, appreciate your wisdom.

Epinn

As a very simple example, consider a formula in say D1 that checks if a
VLOOKUP operation is successful or not

=IF(ISNA(VLOOKUP("Bob",A1:D20,2,FALSE)),"",VLOOKUP("Bob",A1:D20,2,FALSE))

If you have the following the following in E1

=VLOOKUP("Bob",A1:D20,2,FALSE)

and then in D1 use

=IF(ISNA(D1),"",D1)

the VLOOKUP, which checks many cells is only invoked once, and then a much
shorter test is made in D1.

That is the basic technique, but clearly it can be adapted many ways.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Could you give me an example of this?
> Thanks,
[quoted text clipped - 38 lines]
> > > Thanks,
> > > Don

Reply to this Message

Biff - 12 Sep 2006 04:30 GMT

Just to add to Bob's reply:

If you use Vlookups on a "huge" table you can significantly speedup the
lookup by sorting the lookup table. That's not always possible but it can
really help.

Biff

> Epinn,
>
[quoted text clipped - 126 lines]
>> > > Thanks,
>> > > Don

Reply to this Message

Bob Phillips - 12 Sep 2006 08:44 GMT

Biff is right (as usual), sorting the data can be the biggest improvement
that you can make. I would certainly expect to see better from that than
grouping dependent cells.

Also, VLOOKUP can be a killer on large tables, so alternatives can often
speed things up.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Just to add to Bob's reply:
>
[quoted text clipped - 72 lines]
> > As a very simple example, consider a formula in say D1 that checks if a
> > VLOOKUP operation is successful or not

=IF(ISNA(VLOOKUP("Bob",A1:D20,2,FALSE)),"",VLOOKUP("Bob",A1:D20,2,FALSE))

> > If you have the following the following in E1
> >
[quoted text clipped - 55 lines]
> >> > > Thanks,
> >> > > Don

Reply to this Message

Don - 12 Sep 2006 17:32 GMT

Thank you all for your help. I apologise for the delay in responding.
I think I'm going to have to look at ALL manner of things to see if I
can hustle things up. Thanks again,
Don

> Biff is right (as usual), sorting the data can be the biggest improvement
> that you can make. I would certainly expect to see better from that than
[quoted text clipped - 174 lines]
> > >> > > Thanks,
> > >> > > Don

Reply to this Message

Biff - 12 Sep 2006 19:29 GMT

Probably the best resource of its kind. It's all about optimization:

http://www.decisionmodels.com/

Biff

> Thank you all for your help. I apologise for the delay in responding.
> I think I'm going to have to look at ALL manner of things to see if I
[quoted text clipped - 202 lines]
>> > >> > > Thanks,
>> > >> > > Don

Reply to this Message