|
Using FastExcel Calculation Profiling
To make the best of the Drill-down wizard it’s a good idea to read this background material on profiling Excel calculations.
Full Calculation, Recalculation and Volatility: What and Why
To get the best out of FastExcel you need to understand the difference between an Excel Recalculation, where Excel works out how to recalculate the smallest number of formulas possible, and an Excel Full Calculation, where Excel calculates every single formula regardless of whether it has already been calculated or not.
|
Volatile formulas get recalculated at every calculation.
|
Which of these types of calculation is more important for you depends on how much of the input data changes each time you want to calculate the workbook, and how many volatile formulas you have. FastExcel V4 Profiler will measure the volatility of each worksheet and can identify which built-in and XLL based functions are volatile.
|
For example, if you are doing a monthly budget variance analysis each month most of the input data will change and almost all the formulas will need to be recalculated.
But if you are doing a what-if analysis on a cash-flow model then you may change only a single input number that will cause only a small number of the formulas to be recalculated.
If possible, you should decide which of these two scenarios is the most frequent for your workbook, because it can significantly affect which part of your FastExcel Profiling analysis is most significant for you, and also the methods you use to optimize your workbook calculations.
Multi-Threaded Calculation: What and Why
In Excel 2007 the Excel calculation engine was rewritten to use all the available CPUs/cores in your PC. This method of multi-threaded calculation can have a dramatic effect on your Excel calculation times: on a 4-core PC a well-designed workbook can run up to 4 times faster than with single-threaded calculation.
But some worksheet functions (all VBA user-defined functions and many add-in library functions) are single-threaded and so will seriously slow down calculation.
FastExcel V4 Profiler will measure the multithreaded efficiency both of the individual worksheets and the overall workbook, and can identify any single-threaded functions being used.
|
