This is the second post on Excel speed. 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.

Small 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:

Sorry, set also:

Application.ScreenUpdating = True

P.S.:

Solution n°2 is very slower

Hello,

I am French and i not speak english usualy.

I very like your site.

I would to say to Work with Table in Excel because it is very very very fast:

Compare :

Sub remplissageTableau()

Application.ScreenUpdating = False

Dim a(1 To 800000, 1 To 1)

t = Timer

For i = 1 To 800000

a(i, 1) = Cos(i)

Next i

Range(“A1:A800000”).Value = a

MsgBox Timer – t

Application.ScreenUpdating = True

End Sub

Sub remplissageCellules()

Application.ScreenUpdating = False

t = Timer

For i = 1 To 30000

Cells(i, 1) = Cos(i)

Next i

MsgBox Timer – t

Application.ScreenUpdating = True

End Sub

Solution n°1 with 800 000 cellules prennent 0,9 secondes

Solution n°2 with 30 000 cellules prennent 0,9 secondes too

Solution n°2 is very faster. bye and excuse me for my english.

Hi Jagmohan,

My house burned down and I am in a soul searching phase. I will eventually get back to work for the blog. Right now I am learning about architecture and how to use Google Sketchup since we are going to rebuild. Please give me some G+’s or FB Likes. It helps my traffic. Thanks!

Cheers, George

Hi George,

How are you? Why have you stopped putting anything new on your site? Certainly I am missing it.

regards,

Jagmohan

Russel, Sorry for the delay, I just returned from vacation (Fla). I did this stuff when I was a student many years ago. I need to read some. How urgent is this?

Russel, I am a bit confused, what is PID? I do this for fun and I can read some and try to help you but you need to be more explicit.