Use Timer To Micro-time Events

May 24, 2021 - by Bill Jelen

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:

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.

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.