Measuring Excel floating point operations

[Celly]

I understand Excel employs standard IEEE 754-1985 floating point math. I believe it uses 64-bit numbers on all platforms, but I'm not sure about that (comments?)

My question: is there any way to count the total number of floating point operations performed during the calculation of a workbook? I can exclude VBA from the equation and focus on cell formulas for now.

I have a large, complex simulation project employing iterative calculations across approximately 95.000 formula cells. It may be a useful metric for optimization, but mainly I'm just curious. I can't even ballpark the number of operations required by the simulation.

Oddly, I can't seem to find any threads on the subject.

 

[Celly]

The term for what I desire is a FLOP count. I believe this is possible at the processor level, but requires CPU emulation and is quite complicated.

Calculating “FLOP” using Intel® Software Development Emulator (Intel®...

My hope was Excel had this type of performance counter built-in, but it doesn't appear likely.

 

[joeu2004]

The short answer is "no". And certainly not to the degree of accuracy described in the Intel document.

But FLOPs are not a relative measure, anyway. When Excel performs calcuations, there is a lot more going on than just floating-point operations.

Instead, we can measure the time that it takes to complete a calculation -- actually, a recalculation cycle.

On PCs, we can use Timer to measure time with at least millisecond precision, but with a typical resolution or accuracy of 15.625 milliseconds (1/64 second).

(On Macs, the resolution is only 1 second, if we can believe the VBA help page. TBD.)

Or we can use QueryPerformanceCounter to measure time with much better precision, but with a resolution that varies from CPU to CPU. For example, with my Intel CPU, the QPC resolution is 1/2648583 second (about 0.377560378511831 microseconds).

(The ability to use QPC depends on the "hardware platform". That is, it varies from computer to computer.)

If you are doing simulations that involve 100,000 formulas or more, I suspect that Timer is sufficient for your purposes, even on a Mac.

However, there are many factors that complicate such measurements. These are overlooked in most documents and user examples that describe their use.

For a very basic paradigm, consider the following.

Sub doit()
Dim st As Double, dt As Double
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
st = Timer
Range("a1").Dirty
dt = Timer - st
Application.ScreenUpdating = True
MsgBox dt & " seconds"
End Sub

That code assumes that A1 contains a formula that triggers the simulation recalculation -- typically, formula that uses RAND or RANDBETWEEN.

Where or not to disable ScreenUpdating is one of many "complications" to consider. It depends on what you truly want to measure.

 

[joeu2004]

Too late to edit....

On PCs, we can use Timer to measure time with at least millisecond precision, but with a typical resolution or accuracy of 15.625 milliseconds (1/64 second).

Re: ``typical resolution``. For example, when Firefox is running on my computer, the Timer resolution is 9.765625 milliseconds (5/512 seconds).

 

[Celly]

Thanks, yeah I've got a basic performance calculation working based on Timer. A full solve of the workbook takes about 10 seconds, so there's enough accuracy there. I was interested in the FLOP count mainly as an academic exercise.

 

[joeu2004]

Again, too late to edit (sigh; I did not realize I posted the reply)....

Re: ``typical resolution``. For example, when Firefox is running on my computer, the Timer resolution is 9.765625 milliseconds (5/512 seconds).

I should have written "seems to be" 9.765625 milliseconds.

My guess: it is really 1/100 seconds. The apparent resolution is affected by the limitations of type Single precision. It appears to be 9.765625 msec starting at 1:08:16 AM, then 11.71875 msec starting at 9:06:08 AM, then 7.8125 msec at and after 6:12:16 PM.

 

[joeu2004]

I was interested in the FLOP count mainly as an academic exercise.

Okay. As described in the Intel document, FLOPs can be measured only at the hardware level, and only in CPUs with instruction counters (all Intel CPUs), and only for loops that have only floating-point operations.

Obviously, at the application level (Excel, VBA), there oh-so-many more things going on besides the floating-point calculations. At the very least, there are interrupts every 15.625 msec (typically) to update the system clock. And the O/S process management will look for other processes to schedule every 100 msec (typically, if Win is anything like the NT O/S that it was based on). And there are the frequent internet interrupts (if you are connected during measurements; you shouldn't be), which may or may not impact the Excel thread. Not to mention the many thousands (millions?) of instruction cycles that Excel excecutes to evaluate each formula. And that's just the tip of the iceberg.

If you are interested in optimizing formula (re)calculation time, it is better to focus on spreadsheet and formula design, not "FLOPs" (instruction cycles). There are some good articles about things to look for. I cannot think of them off-hand. I'm sure others have a list that they can share.

Good luck!