Measure Excel LAMBDA Execution Time with a Custom Function in WPS Office Spreadsheets
Question details
The user needs to measure how long a custom LAMBDA function takes to calculate in a spreadsheet, including formulas that return large dynamic arrays, and return the elapsed time in minutes, seconds, and milliseconds.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user wants to benchmark or evaluate the calculation time of a LAMBDA formula inside a workbook.
- Observed behavior
- Elapsed calculation time is not shown by default, so a custom LAMBDA is required to record start and stop times and return the duration.
Confirm that your WPS Office version supports LAMBDA and dynamic arrays, and open Name Manager so you can create the timing function before entering a test formula.
Create an EvaluationTime LAMBDA in Name Manager to Time Another LAMBDA
Use this solution when you need to measure calculation time for any LAMBDA function in WPS Office Spreadsheets, including large dynamic array formulas.
The timer captures NOW() before and after the supplied LAMBDA is evaluated and calculates the difference. Because NOW has limited timing precision and system load affects results, run measurements separately and consider averaging multiple runs.
The timing formula is volatile, so remove it after testing to avoid unnecessary recalculations.
Go to the Formulas tab and click Name Manager, then select New to create a new defined name.
Enter the name EvaluationTime, set the scope to Workbook, and in Refers to enter a LAMBDA that stores NOW() before and after evaluating the supplied function and returns the elapsed time.
Select the cell where you will enter the timing formula, open Format Cells, and apply a custom number format such as "[mm]:ss.000" to display minutes, seconds, and milliseconds.
Enter =EvaluationTime(LAMBDA(MAP(SEQUENCE(5000,5000),LAMBDA(value,value*value)))) in the formatted cell, or replace the inner LAMBDA with the function you want to measure.
Delete or clear the timing formula when benchmarking is complete, because the NOW function is volatile and can trigger recalculation.
Average Multiple EvaluationTime Runs for More Stable Results
Use this after creating EvaluationTime when you want to reduce the effect of system load and first-run calculation penalties.
Measure LAMBDA Execution Time in WPS Office Spreadsheets
Create a custom EvaluationTime LAMBDA in WPS Office Spreadsheets with Name Manager and NOW to benchmark any LAMBDA formula, including large dynamic array calculations, and format the result in minutes, seconds, and milliseconds.
- 1. Open WPS Spreadsheets: Launch WPS Office and open the workbook that contains the LAMBDA formula you want to measure.
- 2. Create the timer name: Go to Formulas > Name Manager and create a new name called EvaluationTime.
- 3. Enter the LAMBDA timer: In Refers to, enter a LAMBDA that records NOW before and after evaluating the supplied function and returns the elapsed time.
- 4. Run and format the result: Enter =EvaluationTime(LAMBDA(...)) in a cell and apply the custom format "[mm]:ss.000" to show minutes, seconds, and milliseconds.
- 5. Remove the timer after use: Delete the timing formula when finished to avoid unnecessary recalculation.
Frequently Asked Questions
Does WPS Office Spreadsheets support LAMBDA and dynamic arrays?
Yes, the current WPS Office version supports LAMBDA and dynamic array functions. Open the Formulas tab and use Name Manager to create custom LAMBDA names in .xlsx workbooks.
Why is my EvaluationTime result different every time I recalculate?
Differences come from system load and the limited timing precision of NOW. Run measurements separately, average multiple runs, and expect the first calculation to be slower.
How do I display elapsed time in minutes, seconds, and milliseconds?
Select the formula cell, open Format Cells, and apply a custom number format such as "[mm]:ss.000" to the returned time value.
Should I leave the timing LAMBDA in my workbook?
No, remove it after testing because it uses the volatile NOW function and can cause additional recalculations during normal use.
Can this method time any LAMBDA function?
Yes, pass any LAMBDA as the function argument, including LAMBDA formulas that return large dynamic arrays. Wrap the target calculation as LAMBDA(...) inside the EvaluationTime call.
