Calculation time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BKO - 12 Jun 2007 07:20 GMT

Hello,
I have a large data file (10.000 rows / 10 columns) from wich I have to
process data. I use a lot of index match formula's, lookup tables and data
validations, in multiple sheets

Now the calculation of the sheet takes about 25 seconds after each input.

How can I speed up the calculation time, or calculate only the cells that
are actualy changed after input.

Any help or information about this matter would be appreciated very much,

Thanks in advance

Signature

There are only 10 types of people in the world:
Those who understand binary and those who don''t.

Reply to this Message

Bob Phillips - 12 Jun 2007 08:46 GMT

Excel only calculates a certain number of cells, any that change, or are
dependent upon cells that change.

To reduce the calculation times, you need to be smart, reduce the number of
cells dependent upon a change.

There are some hints and tips at
http://www.decisionmodels.com/calcsecrets.htm

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hello,
> I have a large data file (10.000 rows / 10 columns) from wich I have to
[quoted text clipped - 10 lines]
>
> Thanks in advance

Reply to this Message

bj - 12 Jun 2007 14:12 GMT

When there are too many dependencies, every cell gets recalculated whether it
is in a dependency list or not.

Macro functions can be used to reduce the effective number of dependencies.
(they can also increase the dependencies when done wrong)

> Excel only calculates a certain number of cells, any that change, or are
> dependent upon cells that change.
[quoted text clipped - 19 lines]
> >
> > Thanks in advance

Reply to this Message

BKO - 12 Jun 2007 15:19 GMT

I don't know if I understand everyting right,

So to decrease the calculation time it is better to split the formula's up
in more cells, in stead of using a complex formula (and get a direct result),

I already use dynamic ranges, but it seems to make no difference.

It is not possible for me to use sorted ranges,

Are there any other tips to decrease the calculation time

Thanks for the info so far

Signature

There are only 10 types of people in the world:
Those who understand binary and those who don''''t.

> When there are too many dependencies, every cell gets recalculated whether it
> is in a dependency list or not.
[quoted text clipped - 25 lines]
> > >
> > > Thanks in advance

Reply to this Message

bj - 12 Jun 2007 15:55 GMT

Do your sheets work back and forth?
if each sheet is independant, you could have a macro which would make only
formulas in that sheet calculate when you change something in that sheet.
if you have to make several changes at a time to the master data sheet you
could turn off calculations when that sheet is active.

> I don't know if I understand everyting right,
>
[quoted text clipped - 38 lines]
> > > >
> > > > Thanks in advance

Reply to this Message

BKO - 13 Jun 2007 07:10 GMT

That looks interesting,
I have one large sheat with data (import, 10.000 rows),
one sheet with extrexted information (Data, 5000 rows),
and a sheet where the extrexted data is combined with manual input(input
(5000 rows)
I only need calculation after an import of the txt file, and after the
manual input is completed
At the end the information is combined in 4 or 5 sheets neccesary to
generate a report.

Can You give me some help by achiving this.

Thank a lot for your input,

Signature

There are only 10 types of people in the world:
Those who understand binary and those who don''''t.

> Do your sheets work back and forth?
> if each sheet is independant, you could have a macro which would make only
[quoted text clipped - 44 lines]
> > > > >
> > > > > Thanks in advance

Reply to this Message

Bob Phillips - 12 Jun 2007 16:24 GMT

Yes, in a manner such that a partial formula is only effected once by a
calculation, and if the result doesn't change, all its dependent cells
aren't recalculated.

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> I don't know if I understand everyting right,
>
[quoted text clipped - 48 lines]
>> > >
>> > > Thanks in advance

Reply to this Message