Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
when to use array formulas | Excel Discussion (Misc queries) | |||
Array Formulas | Excel Worksheet Functions | |||
array formulas | Excel Worksheet Functions | |||
array formulas | Excel Worksheet Functions | |||
Array formulas | Excel Worksheet Functions |