Speeding up calculation by switching from arrays to db functions ??

[scott23]

Hi,
I have tons of array formulas in my 5 worksheets ,and the more entries
i create the slower the calculation of the workbook, which at this
point is beyond the 64k (?) limit and calculates EVERY formula upon
new data entry.

So, other than going with manual calculation which is not safe for me
because i rely on my real time analysis within the worksheets for
trading, how can i speed things up. I read that using db function
rather than arrays will help substantially but excel help doesnt
explain how i setup criteria that are different for every formula. I
may have 15 categories of criteria with 100 different scenarios of how
the criteria align within my formulas. Do i have to create a new
criteria heading for each calculation ? If so, can i create a
separate workbook to store these criteria headings ? Excel help only
shows a database with 1 criteria heading above it, but what do you do
if you have multiple formulas that need unique criteria settings ?

Many of my formulas have multiple criteria with nested functions. How
can i accomplish this feat ? Ive considered transferring my database
to ACCESS, but i dont know how i would use my formulas within excel to
reference the access data, AND if that would even speed things up for
me ?

Thanks if you have any clues.

scott

 

[Harlan Grove]

So, other than going with manual calculation which is not safe for me
because i rely on my real time analysis within the worksheets for
trading, how can i speed things up. I read that using db function
rather than arrays will help substantially but excel help doesnt
explain how i setup criteria that are different for every formula. I
may have 15 categories of criteria with 100 different scenarios of how
the criteria align within my formulas.

Do i have to create a new criteria heading for each calculation?

They're criteria ranges, and yes you need a different one for each different
set of criteria. However, you could use D-functions in combinatio with data
tables to vary criteria values particular criteria fields.

If so, can i create a separate workbook to store these criteria headings?

Workbook? Not worksheet? Either way, not a good idea.

Excel help only shows a database with 1 criteria heading above it, but
what do you do if you have multiple formulas that need unique criteria
settings?

....

So what's an example of your complex criteria?


Then again . . .

If you have lots of data, then you're using the wrong tool. A database would
be much better, specifically because databases provide for indexed access to
certain fields. Complicated criteria may also be more easily expressed in
database terms than spreadsheet formulas (and ESPECIALLY as D-function
criteria in Excel).

In Excel, caching previous results and applying formulas to filtered ranges
may improve recalc performance significantly.

But in my own biased opinion, Excel isn't particularly good at more
database-like calculations. Lotus 123 does a better job of this. 123
@D-function syntax is much better than Excel's, and external database tables
and views can be used directly as arguments to @D-functions and @VLOOKUP and
various other functions. Maybe Excel 2003 provides that functionality (I
dunno because I don't have it).

 

[onedaywhen]

Sounds like Excel's database functions are not for you. I once used
them for a project just to try them out - my colleague still laughs
about what a maintenance nightmare I created for myself. And the
criteria were fairly easy to write and were relatively stable.

One way a RDBMS could help you is that you could fetch only the rows
you need to apply your Excel functions to i.e. an initial 'filter'.
Even something as primitive as MS Access - or another (closed)
workbook - is surprisingly quick. I've done similar in the past where
I only persist one row of Excel formulas, fetch the data, copy the
functions down to fit and remove the formulas when I'm done (either
PasteSpecial to replace formulas with values or remove the rows
entirely - remembering to keep the one row of formulas of course!)