calculation time problem

[Dave Breitenbach]

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

 

[Niek Otten]

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


|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

 

[Niek Otten]

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

|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

 

[Dave Breitenbach]

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