Timing LAMBDAs with LAMBDA

I recently created a 5g function. It worked great but two friends suggested faster ways to accomplish the same thing. Each friend's suggestion was different. Which one should I use? To find out I used a simple timing routine that is based almost completely on the work of Sergei Baklan. His work can be found here:

https://www.linkedin.com/pulse/excel-lambdas-thunks-calculation-time-sergei-baklan/

Here is my version of Sergei's function:

How it works

We will measure our function's performance in milliseconds. The first LET() step is ms. Multiplying a date and timestamp, such as produced by Excel's NOW() function, by 24 hours per date, 60 minutes per hour, 60 seconds per minute and 1000 milliseconds per second converts the date and timestamp to milliseconds.

The second LET() step is Start where we capture the current date and timestamp from Excel's NOW() function. NOW() isn't very accurate. Its accuracy is about 15 milliseconds. That is fast enough for comparisons of noticeably slow functions. Most functions are not noticeably slow with small data sets and will not register using this timer. To get a function to register we almost always need to run our function over a large data set. In our example we will use 100,000 rows to get results that we can meaningfully compare.

Immediately after Start captures the date and time, we run our function in LET() step Test. Getting Excel to execute our function only between Start and Timing requires that we keep the function separate from at least one of its arguments until LET() step Test.

The fourth LET() step, Timing, captures how much time elapsed by subtracting Start from the now current date and timestamp.

The last LET() step, Result, converts the elapsed time to milliseconds and uses ROUND() to drop decimals that are well outside NOW()'s accuracy.

Example Use

To illustrate, here are two ways to create a 100,000 by 10 sequence of numbers.

= LAMBDA( Argument, SEQUENCE( Argument, 10))( 100000)

= LAMBDA( Argument, MAKEARRAY( Argument, 10, LAMBDA( R, C, (R -1 ) * 10 + C)))( 100000)

You can probably guess which is faster, but by how much? To find out we type these formulas into 2 different cells:

=Timer(100000, LAMBDA( Argument, SEQUENCE( Argument, 10)))

=Timer(100000, LAMBDA( Argument, MAKEARRAY( Argument, 10, LAMBDA( R, C, ( R - 1) * 10 + C))))

Hit F9 to force these functions to recalculate several times because the timing will fluctuate a bit. But after about a half dozen recalculations, we will know how they compare.

Summary

Both of my friends had great suggestions but one was more than 10 times faster than the other. Incorporating that into my function transformed it from sluggish to scorcher!