|
Profile Formulas and Functions
This command allows you to profile and prioritize each unique formula on a worksheet and to analyse the functions used. Use this to drill down on a problem worksheet or worksheet area to the formulas you need to change.
You can use this analysis to determine which formulas and functions are volatile and which formulas are single-threaded.
.png)
You can choose to profile one or both of:
Worksheet formulas: either all formulas or the formulas in a selected area.
Functions used: either in all formulas or the formulas in a selected area.
You can also request to time the individual functions, and set the warning tolerance level.
Worksheet Formulas Profile Table
This table contains one row for each unique formula, sorted in descending calculation time order.
.png)
Formula Address: Gives the addresses of the first few locations where this formula is used. If the formula is used in many different locations not all locations will be shown.
Flags: One or more of the following formula flags:
A - This is an Array formula
B - This formula contains a Built-in, native Excel function
X - This formula contains a registered XLL function
O - This formula contains a VBA, Automation, or XLM function
N - This formula is not Volatile
V - This formula is Volatile
? - Cannot tell if this formula is Volatile or not
M - This formula is Multi-threaded
S - This formula is probably Single-threaded
All the formulas in this example contain functions that are Built-in to Excel (B), are Non-volatile (N) and are Multi-threaded (M): so the flag is B-N-M.
VBA, Automation and XLM functions are always single-threaded but it is not currently possible to determine within the analysis if they are volatile or not, so they are flagged as?
The analysis does not currently detect Defined Names containing volatile functions or single-threaded functions.
Unique Formulas: Formulas that are copied and contain relative or absolute addresses are treated as being the same (they have the same R1C1 formula).
Formula Count: The total number of occurrences of this formula within the area analysed.
Microsecs per Formula: The average time to calculate (single-threaded) a single occurrence of this formula
Total Millisecs for this formula: Time in Milliseconds to calculate all the occurrences of this formula in this Sheet/Area using a single thread. =Time/Formula * Count
Sorted largest first.
Total Millisecs for this Area/Sheet: Total Millisecs for a single-threaded calculate of all the Formulas in this Sheet/Area.
Formula % of Sheet Time: Total single-threaded calculation time for this formula as a percentage of the time for a single-threaded calculation of all formulas.
Function Profile Table
This table shows an analysis of the functions used, with optionally some indicative calculation timing.
.png)
Local Function Name: The name of the function in the current User Interface Language
Function Flags: Flags that describe the attributes of the function.
B-This function is a native, Built-in Excel function
X- This function is registered as an XLL function
O- Other: This function is a VBA or Automation function
V- This function is Volatile
?- Cannot tell if this function is volatile or not
N- This function is Not volatile
S- This function is probably single-threaded
M- This function is multi-threaded
A - This function is an Asynchronous function
Function Count: The total number of occurrences of this function.
Average Millisecs per function: The Average Time to calculate this function In Milliseconds:
(Note that there may be other function calls etc. embedded inside this function, and the function may also be used in several different formulas)
Total Millisecs This Function: Time in Milliseconds to calculate all the occurrences of this function in this Sheet/Area using a single thread. =Time/Function * Count
Note: Timings for functions are indicative only and may not be accurate. Function timing is most accurate when the function returns only a single value and when each occurrence of the function is used in an identical formula.
|
