Use Timer To Micro-time Events


May 24, 2021 - by Bill Jelen

Use Timer To Micro-time Events

Challenge: Two Excel gurus walk into a bar. One of them says it is faster to use =MAX(0,MIN(A2,B2)), and the other thinks it is better to use =MEDIAN(0,A2,B2). Which one is correct?

Solution: You can find the answer by firing up a VBA macro to calculate each formula 50,000 times. Before the macro starts, you save the value of Timer to a variable. When the 50,000 calculations end, you can compare the original and final values of Timer.

On a Windows PC, Timer shows the number of seconds and fractional seconds elapsed since midnight. On a Mac, the function returns only whole seconds but no fractions. You need to make the process repeat enough times to actually show a difference in the number of seconds. Also, you need to make sure that the process does not extend past midnight!

The following code compares MIN(MAX to MEDIAN on 60,000 cells:


e9781615474011_i0238.jpg

By comparing the Timer values before and after critical sections of code, you can compare the times required for various approaches. As shown in Figure 130, using the functions MIN and MAX is faster than using the MEDIAN function.

Figure 130. MEDIAN is just a bit slower than MAX and MIN.
Figure 130. MEDIAN is just a bit slower than MAX and MIN.

Additional Details: Different computers might run at different speeds, so it is important to test the two processes on the same computer, preferably with similar items running in both cases.

Also, note that the difference in Figure 130 is just over two-hundredths of a second for 60,000 cells. The actual time difference for one cell is 3.9E-7 seconds—a time that will not matter to most end users. However, you, as the reader of this book, know that even such a small difference makes a difference between winning and losing a bar bet.

Summary: You can use the Timer function to calculate how long a process takes.

Source: Cells(1,1) vs Range("A1") on the MrExcel Message Board and Tricky Commission Formula Excel Tip.

Title Photo: Agê Barros on Unsplash


This article is an excerpt from Excel Gurus Gone Wild.



Bill Jelen is the author / co-author of:
MrExcel 2021 – Unmasking Excel

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.