About the Speed of Spreadsheets #2

This is the second post on Excel speed. This has serious implications in creating usable Excel animations. The first part benchmarked the calculation speed of Excel 2003 versus Excel 2007 for various size arrays of formulas.

This part analyzes the effect of formula complexity on the spreadsheet speed.

About the speed of animated models in MS Excel – part #2

– All the series of tests were run on both 2003 and 2007 versions of Excel, the results are presented in the chart to the left.

– As seen in the chart, it looks like for relatively small worksheets (with less than ten thousand single formula calculations) Excel 2003 is 5-10 times faster that Excel 2007 when the window is kept very small (2-3 inches).

– The speed in 2003 for small worksheets drops significantly when the window is increased at full screen size.

– Even within the 2007 version the speed can be increased dramatically (2-3 times) just by decreasing the window size during the calculation for small to medium size worksheets (below 1 million formulas).

– It must be noted the formulas were all “=rand()” and there was no charting within these worksheets.

Large worksheets Medium worksheets 1 Million Formulas “= rand( )” Hardware: Gateway SX2840-01
Monitor resolution: 1280 x 1024

Benchmarking Excel calculation speed for complex formulas

– Up to this point we filled ranges of various sizes with the same formula (“=rand()”) and measured the calculation speed.

– Let’s now keep the array of formulas at the unique size of one million (200 columns x 5000 rows) and do a series of average speed measurements while progressively increasing the complexity of the formulas in each cell.

– It would be a good idea to use a product of the same function (“rand()”) and just use a product of them with an increasing number of factors (from 1 to 10).

– What we expect to see is a calculation time which is proportional to the total number of “rand()” functions contained in the worksheet (our assumption will prove to be right).

– We upgraded the “speed_test_1()” macro so it can be started and stopped from the same button. We called this new macro “speed_test_2()”.

– We will also upgrade the macros which fill the worksheet with the formulas. For this we first copied the two previous worksheets and used a rename scheme as seen below.

Dim N As Boolean
Dim p As Double
Dim Measurement_Start As Double

Sub speed_test_2()
Sheets(“Test2”).Select
N = Not N
p = 0
Measurement_Start = Timer()
Do Until N = False Or (Timer() – Measurement_Start) > 15
[B4] = p / (Timer – Measurement_Start + 0.001)
DoEvents
p = p + 1
Loop
End Sub

The formula generator macros

– These macros are used one by one to alter the formulas in an array of 200 x 5000 cells so that they are a product of “rand()” functions with different number of factors, starting with one factors and ending with ten factors.

-We run one macro at a time and after each macro we run the speed test on sheet “Test2” and when the speed test is over we write the iteration frequency results in a table in worksheet

“Test2_results”

-We then repeat the same process for the rest of the macros one by one.
-The charted result of this procedure is shown in the next page.

Sub generate_array_1M_10rands()
Sheets(“Test2”).Select
[A10:GR65536].Clear
[A11:GR5010].Formula = “=rand()*rand()*rand()*rand()*rand()*rand()*rand()*rand()*rand()*rand()”
End Sub

Sub generate_array_1M_9rands()
Sheets(“Test2”).Select
[A10:GR65536].Clear
[A11:GR5010].Formula = “=rand()*rand()*rand()*rand()*rand()*rand()*rand()*rand()*rand()”
End Sub

Sub generate_array_1M_8rands()
Sheets(“Test2”).Select
[A10:GR65536].Clear
[A11:GR5010].Formula = “=rand()*rand()*rand()*rand()*rand()*rand()*rand()*rand()”
End Sub

Sub generate_array_1M_7rands()
Sheets(“Test2”).Select
[A10:GR65536].Clear
[A11:GR5010].Formula = “=rand()*rand()*rand()*rand()*rand()*rand()*rand()”
End Sub

Sub generate_array_1M_6rands()
Sheets(“Test2”).Select
[A10:GR65536].Clear
[A11:GR5010].Formula = “=rand()*rand()*rand()*rand()*rand()*rand()”
End Sub

Sub generate_array_1M_5rands()
Sheets(“Test2”).Select
[A10:GR65536].Clear
[A11:GR5010].Formula = “=rand()*rand()*rand()*rand()*rand()”
End Sub

Sub generate_array_1M_4rands()
Sheets(“Test2”).Select
[A10:GR65536].Clear
[A11:GR5010].Formula = “=rand()*rand()*rand()*rand()”
End Sub

Sub generate_array_1M_3rands()
Sheets(“Test2”).Select
[A10:GR65536].Clear
[A11:GR5010].Formula = “=rand()*rand()*rand()”
End Sub

Sub generate_array_1M_2rands()
Sheets(“Test2”).Select
[A10:GR65536].Clear
[A11:GR5010].Formula = “=rand()*rand()”
End Sub

Sub generate_array_1M_1rand()
Sheets(“Test2”).Select
[A10:GR65536].Clear
[A11:GR5010].Formula = “=rand()”
End Sub

Charted results:

Below is the average worksheet calculation time in seconds charted against the formula complexity (n). As expected the calculation (update) time is proportional to the formula complexity (or in other words to the total number of “rand()” functions used in the worksheet).

-There are also some products to evaluate within these functions but it seems from the chart below (and the one next page) that this multiplication is not taking significant resources.

-Below is the average calculation time per function used (in nanoseconds). If we use let’s say a product of 10 “rand()” functions in 1 million cells we have a total of 10 millions functions. If we divide the worksheet calculation time with the total number of functions we get a point on the chart below. As expected this is a fairly constant number and of course dependent on the Excel version and the machine used.

Benchmark a few mathematical functions against each other

-A new speed benchmarking worksheet (Test_3) was run for eight different functions (using eight different macros to fill the worksheet with one million formulas).

-The results are shown below: