Speeding up calculation

[Chris.Ulrich]

Hi All,
hope someone will be able to help me with this.

I have made a table, (13 collums x 6000 rows) where all cells holds the
formula below:

{=SUM(IF(Data!$K$2:$K$45000=Reference!A2;IF(Data!$N$2:$N$45000<DATEVALUE("01-01-1997");IF(Data!$N$2:$N$45000>=DATEVALUE("01-01-1996");Data!$G$2:$G$45000;0);0);0))}

THe problem is that calculating it crashes excel completly. I have
tried to switch off autocalculation in an attempt to speed up the
calculation
is there anything I can do to speed up the calculation?

 

[Guest]

Array formulas are a pig for resources. Have you considered using a pivot
table. You should be able to get the same grouping without the overhead.
Since you have dates in the raw data you can group on the date.

Otherwise you can dup all of this into Access and it will not have much
difficulty with that for you...

HTH

 

[Tom Ogilvy]

The smaller you make 45000 the better your formulas will perform. If you
are not looking at 45000 rows, reduce the number closer to what you are
looking at.

 

[Chris.Ulrich]

Hi,
Thanks for replying.

I tried to reduce the 45000 down to 30000 (which is the absolute
minimum), but it doesn't help.
Do you know if a VBA code could replace the formulas?
Access is, unfortunately, not an options, since I dont have the
program....

Regards,
Chris

 

[Chris.Ulrich]

Jim,

as you wrote, I have dates in the spreadsheet. All of them will be in
the format: 12-12-2000. What's important to me, is not the specific
date, but the year.
Is there any way to construct a pivot, that will only take the year,
not the date, into account, when making a summery?

 

[Guest]

With the dates you can select group on. You can then group on the years,
quarters, months... It is realy cool in an accounting kinda way.