Timing Excel Formula Calculations

I have written a couple of posts previously about the methodology of timing Excel calculations:

A Quantum of Time – measuring time accurately

The XIPS Challenge – how fast does Excel calculate?

But rather than focus exclusively on how to time Excel calculations, this also post looks at why you might want to and what should you measure.

Why time Excel calculations anyway?

There are only a few reasons I can think of for why you might want to time calculations:

• a) To find out if it is the calculation that is slowing things down or something else.
  • Could be VBA, Screen Refresh, Data Refresh, not enough RAM, Addins …
• b) To prioritize the formulas that are responsible for slow calculation.
• c) To compare 2 different ways of calculating the same result to see which is faster.
• d) Because its interesting if you are a geek like me.

Most people focus on c) followed by b).

Exactly what should you be measuring?

Elapsed time vs CPU Time

Usually you measure elapsed time: how much time passes from starting something (a macro, a calculation, …) to finishing it.
Sometimes it would be nice to measure CPU time (the amount of time your CPU cores are being used), so that you can see if you have enough RAM available or if you are using all the cores you have available efficiently.
Of course measuring elapsed time is a lot easier, so that is what we do most of the time.

And anyway as Excel users it’s elapsed time that we want to minimize.

Overhead time vs Calculation time vs Screen Repaint time

If you think about what Excel has to do to calculate formulas you can break it down into a series of steps:

Overhead Time

• Work out which formulas need to be calculated, depending on what has changed since the last calculation and what is volatile.
• Separate the formulas into chains of interdependent formulas and allocate them to the available calculation threads.
• Process each formula in the calculation chains in turn

Calculation Time

• Parse the formula into a sequence of executable sub-expressions and functions.
• Get the data needed by the sub-expressions and functions and pass the data to them.
• Evaluate the sequence of sub-expressions and functions and return the results to the next sub-expression.
• Return the result to the Excel value layer.

Screen Repaint Time

• If the cell containing the formula is in the visible part of the screen then Excel will format the result using the formatting and conditional formatting rules for that cell. This can be slow!

 Multi-threaded or Single-threaded calculation

Given that most modern PCs have multiple cores Excel’s calculation time is heavily dependent on making good use of the available cores.
Some functions (INDIRECT, GETPIVOTDATA and most UDFs) are single-threaded.

Don’t use a single-threaded calculation method such as Range.Calculate to compare a single-threaded function to a multi-threaded function.

Comparing Formulas

If you want to compare formula efficiency it does not much matter if the time measured includes overhead and screen repaint time as well as calculation time.

What matters is to compare like with like: each measurement should include the same amount of overhead and screen repaint etc.

Recalculation vs Full Calculation

Most of the time when Excel calculates it’s smart recalculation engine only recalculates the stuff that has changed since the last recalculation (and any volatile formulas).
So usually you want to measure and optimize recalculation time.
Of course if you change ALL the data in a workbook then ALL the formulas need to be recalculated.

Repeatable Timings

There are many reasons why you can get quite significant differences between timings:

• Windows multi-tasking means other processes can be stealing time
• Modern CPUs have Turbo modes that are workload-dependent
• Modern CPUs have high-speed caches that may or may not contain the required data or program code
• Windows may be pageing stuff in or out of RAM
• Excel progressively optimizes both multi-threading and the calculation chain in successive calculations
• Excel’s smart recalculation engine tries to minimize the amount that needs to be calculated but this depends on the previous calculation
• Excel’s memory management algorithms may need to reorganize memory
• …

To minimize these problems you should repeat the timing of the calculation several times and pick the most consistent timing.

Calculation Methods

Excel VBA gives you a variety of callable calculation methods. Which one you use for timing purposes can make a large difference to the comparison.

Range.Calculate

Most people writing a VBA calculation timing routine use Range.Calculate in Manual Calculation mode,.

Range.Calculate is usually the best method to use when comparing formulas.

• If you switch off screen updating whilst timing the range calculate then you eliminate screen repaint time.
• If you turn on Automatic calculation after the Range.Calculate then the formula(s) you calculated and all their dependents and all volatile formulas will be recalculated.
• Range.Calculate is always single-threaded so does not include the multi-threaded calculation overhead.
• Range.Calculate always calculates all the formulas in the selected range.
• With a high-resolution timer you can compare even single-cell formulas reasonably accurately.

Don’t use Range.Calculate to compare a single threaded formula with a multi-threaded formula.

Range.CalculateRowMajorOrder

CalculateRowMajorOrder ignores dependencies within the selected range and so may be faster but less realistic than Range.Calculate.

Worksheet.Calculate

Worksheet.Calculate does a multi-threaded smart recalculate of a worksheet. To do a Full calculation of the worksheet (all the formulas on the worksheet) you need to toggle Worksheet.EnableCalculation to False and then back to True before Worksheet.Calculate.

Application.Calculate

Recalculates all the open workbooks using the multi-threaded smart recalc engine.

Application.CalculateFull

Calculates ALL the formulas in all the open workbooks using multi-threading.

 Application.CalculateFullRebuild

Rebuilds the dependency trees and calculation chain (this is slow) and then does a multi-threaded full calculation.

FastExcel Calculation Profiling

My FastExcel V3 Profiler product has a variety of optimized calculation timing and profiling tools that are designed to simplify drilling down to the calculation bottlenecks and timing formulas. FastExcel Profiler can save you a lot of time and effort.

http://www.decisionmodels.com/FastExcelV3Profiler.htm

You can:

• Time workbooks, worksheets and formulas using each of the available calculation methods
• Profile a Workbook to prioritize the slow worksheets and measure volatility and multi-threading efficiency
• Profile Worksheets to find out which are the slow areas of formulas
• Profile Formulas: Time and prioritize each of the unique formulas on a worksheet and discover which formulas are volatile or single-threaded.

Recommendations

• Work out what you are trying to achieve.
• Design your timing test to be as close as possible to the real-life problem.
• Repeat the timing several times until you get some timing consistency.
• Choose your calculation method according to what you want to optimize.
• Don’t compare single-threaded calculation times to multi-threaded calculation times.
• Be careful that screen rendering/refresh times do not distort your comparisons.
• Use the Windows high-resolution timer API to measure elapsed time.