In our last episode keepITcool wrote:

40 Megs per workbook is an awful lot and CAN/MUST be streamlined.
does it need to be recalculating formulas? or is it just to calculate
invoice lines, which when calculated can be converted to Values?

How many different functions have you defined?
What does your workbook look like
# of sheets
# of rows


I appreciate that it's big (hence my attempt to reduce the number of
formulas in the sheets by using functions) but that's a function of the
volume of data more than anything - FYI we are calculating costs and
comparing them with the invoiced charge. Average number of records per
sheet varies per cost type, but it's safe to say that the bigger sheets
(not books) would be around 40K records each (more is not uncommon).

the object of the excercise is to confirm the accuracy of the charges and
to provide some visibility of the calculation process

not sure what you mean by recalculating formulas - the formulas are
consistent within the context they are called - and the expected outcome
will always be a value

there are at present 3 sheets referencing functions (note that there are a
number of conditional formula's to satisfied before the functions are
called, so while there are 10 opportunities to call the cost function per
row in sheet 3, only 1 or 2 will pass the conditional statements and call
the function) -

1st sheet does 1 function call per row - calls the cost function (see
earlier post for details) - there are other calculations, but they are
done by formula's at the moment

2nd sheet does 1 function call per row - calls the cost function for 1 of 2
opportunities

3rd sheet does a maximum of 3 function calls per row - calls a function
similar to cost (but much simpler - for any given record there will be up
to 2 results from 5 opportunities) and another once (more complex -
calculates the variance between the calculated and invoiced amounts -
calculated amount would be the output from the aforementioned cost function)

at present there are 3 'main' functions - these replaced the use of the
equivalent formulas in each cell to be calculated, ie 11 times per row for
sheet 3 by XXXX rows...

hope this helps (it would probably be a lot easier to see the spreadsheet
than to try and work it out from my descriptions)

once again, many thanks for you're assistance (still working thru the
previous posts...)

S