Timing function execution using LAMBDA

I've been developing a large library of LAMBDA functions and I found myself in need of some crude performance testing, as many of my functions return large arrays and can take some time to execute. I've seen many solutions using Evaluate() in VBA, but I eventually realized I could do the same thing using a LAMBDA function. Here it is, unrolled for readability:

Name: EvaluationTime

=LAMBDA(F,
  LET(
    startTime, NOW(),
    result, F(),
    stopTime, NOW(),
    reducedResult, REDUCE(, result, LAMBDA(accumulator, value,
      IF(ISERROR(accumulator),
        accumulator,
        value
      )
    )),
    IF(ISERROR(reducedResult),
      reducedResult,
      TEXT(stopTime - startTime, "m:ss.000")
    )
  )
)

Rolled up so that it can be added to Name Manager:

=LAMBDA(F, LET(startTime, NOW(), result, F(), stopTime, NOW(), reducedResult, REDUCE(, result, LAMBDA(accumulator, value, IF(ISERROR(accumulator), accumulator, value))), IF(ISERROR(reducedResult), reducedResult, TEXT(stopTime - startTime, "m:ss.000"))))

Suppose you want to time how long generating a 5000×5000 array of squares will take, using a combination of MAP() and SEQUENCE():

=EvaluationTime(LAMBDA(MAP(SEQUENCE(5000, 5000), LAMBDA(value, value * value))))

On my laptop, the result is "0:27.240".

To time your own LAMBDA function, assuming it's in Name Manager, wrap it as follows:

=EvaluationTime(LAMBDA(YourFunction(param1, param2, param3...)))

The parameters can, as always, be constants or refer to other cells, and your function doesn't get evaluated until inside EvaluationTime(). You can then play around with the code or the parameters to get different timing metrics.

This is how it works:

1. Line 1: The only parameter is a LAMDBA function that itself takes no parameters. The function call you want to time is inside the LAMBDA.
2. Line 3: Get the start time, using the NOW() function.
3. Line 4: Evaluate the function and store the result.
4. Line 5: Get the stop time, using the NOW() function.
5. Line 6: Reduce the result by iterating over the array. The reduced result is the first error (if any) in the array.
6. Line 12: If any error occurred during the evaluation, the timing is assumed to be invalid so the first error is returned. Otherwise, the evaluation time in minutes, seconds, and milliseconds is returned.

Because the NOW() function is volatile, the EvaluationTime() function is volatile as well, which means that it will be re-evaluated every time another cell changes. It's best to follow some rules so that you don't hang up your workbook for too long:

• Once you have the timing you need, delete the formula from the cell or disable it by putting the apostrophe in front, which forces Excel to treat everything that follows as a string.
• Don't have more than one EvaluationTime() call active at the same time, as they could interfere with each other by "stealing" processor time and skewing the timing.
• The evaluation time is affected by other processes on your computer, so the results may vary, even within the same session. If you can, do the timing on a device that has more limited background processing (e.g., an iPad); you'll get more consistent results.

Have fun.