Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculation time problem
I have raw data that runs about 55,000 rows in excel (without going into why,
we'd like to keep our formulas in excel as opposed to Access) with maybe 20-30 columns. We then have a summary sheet which uses many sumproducts to lookup on the data and return results base on criteria which we have successfully done. The problem is the spreadsheet crashes excel because the many sumproducts combined with so many rows needs so much power (at least that's is what appears to be the cause). I thought about separating the summary spreadsheet into pieces but I'd like to avoid making it unmanageable. I was told that DSUM is as much as 50% faster than sumproduct, but I hate DSUM, and, in our case, that would still need 6 hours of computation time. Any magic bullets out there for spreadsheet design (workspaces, other lookup with category functions, or other methodology with existing functions) that can substantially reduce calculation time? tia, Dave |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculation time problem
Hi Dave,
How could we possibly say without knowing your formulas? In general, large files/numbers of computations shouldn't make Excel crash (whatever you mean with "crash"). Post again with examples of your formulas, the version of Excel, memory and processor specs and what you see when Excel crashes. In the meantime, check www.decisionmodels.com for many tips on speed and capacity problems. -- Kind regards, Niek Otten Microsoft MVP - Excel "Dave Breitenbach" wrote in message ... |I have raw data that runs about 55,000 rows in excel (without going into why, | we'd like to keep our formulas in excel as opposed to Access) with maybe | 20-30 columns. We then have a summary sheet which uses many sumproducts to | lookup on the data and return results base on criteria which we have | successfully done. | | The problem is the spreadsheet crashes excel because the many sumproducts | combined with so many rows needs so much power (at least that's is what | appears to be the cause). | | I thought about separating the summary spreadsheet into pieces but I'd like | to avoid making it unmanageable. I was told that DSUM is as much as 50% | faster than sumproduct, but I hate DSUM, and, in our case, that would still | need 6 hours of computation time. | | Any magic bullets out there for spreadsheet design (workspaces, other lookup | with category functions, or other methodology with existing functions) that | can substantially reduce calculation time? | | tia, | Dave |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculation time problem
I forgot to say: if you have complex SUMPRODUCT() formulas, please specify what you expect them to do.
-- Kind regards, Niek Otten Microsoft MVP - Excel "Dave Breitenbach" wrote in message ... |I have raw data that runs about 55,000 rows in excel (without going into why, | we'd like to keep our formulas in excel as opposed to Access) with maybe | 20-30 columns. We then have a summary sheet which uses many sumproducts to | lookup on the data and return results base on criteria which we have | successfully done. | | The problem is the spreadsheet crashes excel because the many sumproducts | combined with so many rows needs so much power (at least that's is what | appears to be the cause). | | I thought about separating the summary spreadsheet into pieces but I'd like | to avoid making it unmanageable. I was told that DSUM is as much as 50% | faster than sumproduct, but I hate DSUM, and, in our case, that would still | need 6 hours of computation time. | | Any magic bullets out there for spreadsheet design (workspaces, other lookup | with category functions, or other methodology with existing functions) that | can substantially reduce calculation time? | | tia, | Dave |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculation time problem
Niek,
Thanks for your reply. I didn't pose specifics because I don't have a problem getting the formulas to work on a small scale. It's more of a general question, but I see your point and I'll give more specs for you. Excel version: Excel 2002 SP3 There are many examples of sumproducts, but in general they refer to at least one condition =SUMPRODUCT(--($A$1:$A$56976=$I$6),--($C$1:$C$56976)) but can have 2 or 3 conditions =SUMPRODUCT(--($A$1:$A$56976=$I$6),--($B$1:$B$56976=$F$6),--($d$1:$d$56976)) The summary sheet will have 1200 "pool names" (one pool per row) in the first column. Those pool numbers will be in column A of the raw data tab, for example, so the first formula matches all column C (on summary tab) quantities that match the name in column A (on raw data tab). The summary tab will have maybe 20 more columns of sumproducts which all extract different pieces of info from the raw data tab. I suppose "crash" was the wrong word to use. We've tested the calculation time for smaller samples of data and extrapolated how long it would take and haven't bothered to wait for it to finish as 16 hrs (for example) is too long a solution for us. We're working on a server that has something like 15 or 20 gigs of ram but shares it with 15 other users. I'm not sure of the processor, but I believe there are 6 of them on the one machine Let me know if there is more info you need. thanks, Dave "Niek Otten" wrote: I forgot to say: if you have complex SUMPRODUCT() formulas, please specify what you expect them to do. -- Kind regards, Niek Otten Microsoft MVP - Excel "Dave Breitenbach" wrote in message ... |I have raw data that runs about 55,000 rows in excel (without going into why, | we'd like to keep our formulas in excel as opposed to Access) with maybe | 20-30 columns. We then have a summary sheet which uses many sumproducts to | lookup on the data and return results base on criteria which we have | successfully done. | | The problem is the spreadsheet crashes excel because the many sumproducts | combined with so many rows needs so much power (at least that's is what | appears to be the cause). | | I thought about separating the summary spreadsheet into pieces but I'd like | to avoid making it unmanageable. I was told that DSUM is as much as 50% | faster than sumproduct, but I hate DSUM, and, in our case, that would still | need 6 hours of computation time. | | Any magic bullets out there for spreadsheet design (workspaces, other lookup | with category functions, or other methodology with existing functions) that | can substantially reduce calculation time? | | tia, | Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time calculation (Subraction of Idle Time) | Excel Discussion (Misc queries) | |||
Time calculation problem (URGENTProject due) | Excel Discussion (Misc queries) | |||
Ignoring Time in a Date Time Calculation | Excel Worksheet Functions | |||
time calculation with military time | Excel Worksheet Functions | |||
Time problem, Auto calculation??? | Excel Discussion (Misc queries) |