To speed up calculations on a Worksheet

[Bankebihari]

hey friends,

I have prepared a worksheet that contains data tables. However, when I perform calculations on different functions of data tables in worksheet, it takes a considerable amount of time. My question is- Is there any method to speedup these calculations in worksheet containing data tables ?

thnx

[Eric B]

There is very simple method to speedup calculations in worksheet containing data tables.

Steps -

1. Click the Microsoft Office Button , click Excel Options, and then click the Formulas category.
   
2. In the Calculation options section, under Calculate, click Automatic except for data tables.

Tip - Alternatively, on the Formulas tab, in the Calculation group, click the arrow on Calculation Options, and then click Automatic Except Data Tables.

Note - When you select this calculation option, data tables are skipped when the rest of the workbook is recalculated. To manually recalculate your data table, select its formulas and then press F9.

[chroma]

The key to improving faster excel and VBA calculations lies in understanding how Excel re-calculates. Whenever an event occurs that causes Excel to recalculate (for example a cell is changed, a VBA code calls for re calculation or anything else), the entire calculation process is carried out roughly as follows -

1. Load workbook in memory when the workbook is first opened.

2. Track all dependencies and build an initial “calculation chain”.

3. Calculate the cells that are marked in the chain one by one, starting from the top and proceeding along to the bottom of the chain.

[Macarenas]

To speed up calculation using the interpolant option of `dsolve/numeric`

In the setup in the worksheet, the timing measurements for dsolve are actually setting up and applying the numerical problem every time you request the solution.
Given that, it's actually quite fast

So basically at every requested point the worksheet calls the top-level dsolve/numeric which has to :
1) Process the input problem
2) Build the numerical evaluator
3) Compute the interpolant over 0..2
4) Evaluate the interpolant for the requested value

Ideally you want to set up the problem once for the values of beta,lambda,sigma,P,B (steps 1-3), then use this for multiple 'z' values (step 4).

This can be accomplished by replacing your definition for DLn2:

DLn2:=(z,beta,lambda,sigma,P,B)->
(1+z)*sqrt((1+sigma)^P+B)*rhs(op(2,rr(beta,lambda,sigma,P,B)))(z);

With the following:

DLn2:=proc(z,beta,lambda,sigma,P,B) global DLn2state;
if not assigned(DLn2state) or DLn2state[1]<>[beta,lambda,sigma,P,B] then
DLn2state := [[beta,lambda,sigma,P,B],
rhs(op(2,rr(beta,lambda,sigma,P,B)))];
end if;
(1+z)*sqrt((1+sigma)^P+B)*DLn2state[2](z);
end proc: