L_TIMER
=L_TIMER(n_iterations, LAMBDA(i, expression), [ret_values], [j], [k])
| Argument | Description | Example |
|---|---|---|
| n_iterations | Number of times to repeat the LAMBDA function | 100 |
| i | Your function can optionally use i which is the iteration number | n/a |
| ret_values | If TRUE, L_TIMER returns the result of your function for each iteration instead of the time | TRUE or Blank |
| j,k | If returning results, only one value per iteration can be returned. These are the row,column indices you want to return | 1,1 |
In the template file, navigate to the General worksheet to see the L_TIMER function in action.
Description
L_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, L_TIMER is designed to let you specify a number of iterations (which can be 1 if you want).
Procedure Used by L_TIMER
- Record the Start time with NOW()
- Repeat the Function n times in sequence using BYROW
- Record the Final time with NOW()
- Return Final-Start converted to milliseconds
To demonstrate how to use L_TIMER with any formula or function, we will test using the RAND() function. We'll start with repeating the function 10 times:
=L_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.
=L_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.
=L_TIMER(100000,LAMBDA(i, RAND() )) Result: About 100ms on my machine
IMPORTANT: The function that is passed to L_TIMER is LAMBDA(i,expression), not just expression. Here is an example of using the iteration number within the test expression:
=L_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). L_TIMER will return INDEX(function,j,k) for each iteration.
Lambda Formula
This code for using L_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.
Code for AFE Workbook Module (Excel Labs Add-in)
/** * Runs a function n times in sequence using BYROW and returns the time in milliseconds * Resolution is about 10 milliseconds */ L_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) ));
L_TIMER Examples
=L_TIMER(1,LAMBDA(i, L_PASCAL(100) )) Result: About 300ms on my machineTo test whether the iterator was performing as expected, test the same function using 10 iterations. In theory, it should take about 10 times as long.
=L_TIMER(10,LAMBDA(i, L_PASCAL(100) )) Result: About 3070ms on my machine
=L_TIMER(10,LAMBDA(i, L_PASCAL(100) )) Result: About 3070ms on my machine
=L_TIMER(10,LAMBDA(i, L_PASCAL(100,"P") )) Result: About 110ms on my machine
This is a pretty significant difference (nearly 30 times faster).
Acknowlegements
I got the idea for a timer function from the examples in the Excel Labs add-in github site here.
