Array Formulas
 

I'm having a major problem with an Excel 2007 workbook. I have over
104,000 rows of data, and it is taking about 20-30 minutes for Excel
to recalculate all of my formulas. To help solve this problem, I've
turned off automatic recalculations, so that I can write all I want
for awhile and wait only once.

Most recently, I've added the array formula:
{=SUM(IF(DATEDIF($A$3:$A5,$A5,"d")=0,$E$3:$E5))}

to sum all profits (in Column E) for all records with the same date
(in A).

Since I've added this formula, Excel has been unable to handle the
rest of my workbook, and after 40 minutes of waiting, I was told that
Excel didn't have enough resources. Is there any way to possibly write
this formula without using arrays? I'm thinking that by the time Excel
gets down to 60,000+ rows, it's trying to store too many values in
memory.

Also, I'm looking for any other ways to possibly trim down the
processing time. I think my major problem is that I have so many rows
of data, but I don't yet know how I'm going to get around that. If I
had it my way, I'd put in 5 times that amount of data.

My other formulas aren't *too* intensive, but look a little something
like this:

=SUM(INDEX($A$2:INDIRECT("E"&LastMarketData),MATCH (VLOOKUP(A6,'Trade
Data'!$A$2:INDIRECT("'Trade Data'!A" &LastTradeData),1),$A
$2:INDIRECT("A"&LastMarketData),1)+1,5):E6)

Basically, I'm cycling through 2-3 worksheets of data to grab relevent
values based on matching time stamps. But, since the time stamps don't
match exactly a lot of the time, I'm having to do a lot of modifying
of my conditions.

Any help would be great - I think I've reached the limit of my
workbook.

Array Formulas
 

Some general information on performance improvement can be found in this
white paper from Microsoft:
http://msdn2.microsoft.com/en-US/library/aa730921.aspx
I know it doesn't specifically address your issues, but it may give you some
thoughts on how to improve things or make future changes work a bit faster.

" wrote:

I'm having a major problem with an Excel 2007 workbook. I have over
104,000 rows of data, and it is taking about 20-30 minutes for Excel
to recalculate all of my formulas. To help solve this problem, I've
turned off automatic recalculations, so that I can write all I want
for awhile and wait only once.

Most recently, I've added the array formula:
{=SUM(IF(DATEDIF($A$3:$A5,$A5,"d")=0,$E$3:$E5))}

to sum all profits (in Column E) for all records with the same date
(in A).

Since I've added this formula, Excel has been unable to handle the
rest of my workbook, and after 40 minutes of waiting, I was told that
Excel didn't have enough resources. Is there any way to possibly write
this formula without using arrays? I'm thinking that by the time Excel
gets down to 60,000+ rows, it's trying to store too many values in
memory.

Also, I'm looking for any other ways to possibly trim down the
processing time. I think my major problem is that I have so many rows
of data, but I don't yet know how I'm going to get around that. If I
had it my way, I'd put in 5 times that amount of data.

My other formulas aren't *too* intensive, but look a little something
like this:

=SUM(INDEX($A$2:INDIRECT("E"&LastMarketData),MATCH (VLOOKUP(A6,'Trade
Data'!$A$2:INDIRECT("'Trade Data'!A" &LastTradeData),1),$A
$2:INDIRECT("A"&LastMarketData),1)+1,5):E6)

Basically, I'm cycling through 2-3 worksheets of data to grab relevent
values based on matching time stamps. But, since the time stamps don't
match exactly a lot of the time, I'm having to do a lot of modifying
of my conditions.

Any help would be great - I think I've reached the limit of my
workbook.