TIMER

=TIMER(n_iterations, LAMBDA(i, expression), [ret_values], [j], [k])

Download the Template

In the template file, navigate to the General worksheet to see the TIMER function in action.

Description

TIMER is a utility function designed to let you time how long a function takes to run. There are usually many different ways to code a function, so this utility may help compare calculation times for different algorithms.

To test the calculation speed of a formula in Excel, you may need some way to repeatedly run the function, because many functions run too quickly to be measured in milliseconds. So, TIMER is designed to let you specify a number of iterations (which can be 1 if you want).

Procedure Used by TIMER

1. Record the Start time with NOW()
2. Repeat the Function n times in sequence using BYROW
3. Record the Final time with NOW()
4. Return Final-Start converted to milliseconds

To demonstrate how to use TIMER with any formula or function, we will test using the RAND() function. We'll start with repeating the function 10 times:

=TIMER(10,LAMBDA(i, RAND() ))
Result: Likely 0 because this runs faster than 10ms

Next, to make sure it's actually running the function multiple times, we'll set [ret_values] to TRUE and check the results.

=TIMER(10,LAMBDA(i, RAND() ), TRUE)
Result: {0.608;0.342;0.375;0.701;0.775;0.963;0.3;0.133;0.384;0.443}

Next, we'll set the iterations to 100000 to see if we can get a time >10ms.

=TIMER(100000,LAMBDA(i, RAND() ))
Result: About 100ms on my machine

IMPORTANT: The function that is passed to TIMER is LAMBDA(i,expression), not just expression. Here is an example of using the iteration number within the test expression:

=TIMER(10,LAMBDA(i, i+RAND() ), TRUE)
Result: {1.814;2.741;3.948;4.287;5.624;6.314;7.387;8.654;9.86;10.512}

The optional [j] and [k] parameters are used for debugging. If your function creates an array of values and you want to check whether the function is working, you can specify j and k (and set ret_values to TRUE). TIMER will return INDEX(function,j,k) for each iteration.

Lambda Formula

This code for using TIMER in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

/**
* Runs a function n times in sequence using BYROW and returns the time in milliseconds
* Resolution is about 10 milliseconds
*/
TIMER = LAMBDA(n_iterations,function,[ret_values],[j],[k],
LET(doc,"https://www.vertex42.com/lambda/timer.html",
    started,NOW(),
    seq,SEQUENCE(n_iterations),
    result,BYROW(seq,LAMBDA(i,INDEX(function(i),IF(ISBLANK(j),1,j),IF(ISBLANK(k),1,k)))),
    time_in_ms,(NOW()-started)*24*3600000,
    IF(ret_values=TRUE,result,time_in_ms)
));

TIMER Examples

=TIMER(1,LAMBDA(i, PASCAL(100) ))
Result: About 300ms on my machine

=TIMER(10,LAMBDA(i, PASCAL(100) ))
Result: About 3070ms on my machine

=TIMER(10,LAMBDA(i, PASCAL(100) ))
Result: About 3070ms on my machine

=TIMER(10,LAMBDA(i, PASCAL(100,"P") ))
Result: About 110ms on my machine

Acknowlegements

I got the idea for a timer function from the examples in the Excel Labs add-in github site here.