In Use Excel VBA to Test Report Calculation Times I described a macro that finds the total time necessary to calculate Excel for a specified number of times. This post describes the workbook I set up to test various lookup methods. The next post (Excel’s Fastest Lookup Methods: The Tested Results) will explain what I learned from these tests.
The testing workbook needed to meet two criteria.
First, it needed to have a database large enough to cause the lookup formulas to work fairly hard. Second, it needed to be volatile.
The first criterion is self-explanatory. But what’s volatility?
A volatile formula is one that calculates each time your workbook calculates. It might surprise you to learn that most Excel functions are non-volatile. That is, to speed up apparent calculation times, most Excel functions calculate only if their source data changes.
Generally, having non-volatile functions is a good thing. If nothing has changed in a formula since the last time you calculated, there’s no reason for Excel to take the time to calculate that formula all over again.
However, we want to test the calculation times for different lookup functions. We therefore need them to calculate each time Excel calculates. And therefore, to make these functions volatile, we need to make sure that the source data changes each time we recalculate.
So here’s how I did it…
The Data Worksheet for the Test Workbook
This figure shows the top of the simple database. Here are the two key formulas:
A2: =”R”&ROW()
This formula returns a unique “code” value to each cell in column A to which it’s copied. It simulates product codes, customer codes, or other codes that you might want to look up.
B2: =ROW()&”-”&COLUMN()
This formula simulates the data that might exist in a row and column of your Excel database.
I now needed to copy the formula in cell A2 to the range A2:A50001. The potential problem is that cell A50001 is a LONG way down my worksheet. To get to cell A50001 the way I normally do when moving around the spreadsheet I would have had to tap the Page Down key more than 1,000 times.
But I didn’t do that, because there’s an easier way:
I selected cell A2 and pressed Ctrl+C to copy it, just as I normally would. But then, I pressed the F5 key to launch the Go To dialog; entered A50001 as the Reference; and pressed OK. Now that cell A50001 was selected I pressed Ctrl+Shift+Up-Arrow to extend the selection upwards to cell A2. Then I pressed Ctrl+V to paste the formula.
Remember this F5 trick when you need to move a long distance into your worksheet.
Then I used a similar approach to copy the formula in cell B2 to the range B2:F50001.
Because I wanted to test the lookup functions against both sorted and unsorted data, I decided to start with unsorted data. So I entered this formula in the cell shown above…
G2: =RAND()
…and copied it to G2:G50001. I sorted the data by this column, giving me the “unsorted” results shown in the figure above. Then I deleted column G.
I assigned the range name Data to the range A2:F50001. And finally, I assigned the name Data to the database worksheet. (There’s no problem when a worksheet and a range have the same name.)
The Report Worksheet for the Test Workbook
In a second worksheet, I set up a report with 1,000 rows of results. This figure shows the top of that report.
The formulas in columns B:F will use different sets of formulas to test different lookup methods. I’ll discuss these formulas in the next post.
But because we want to force all the formulas in columns B:F to recalculate each time Excel recalculates, we need the Codes in column A to change each time Excel recalculates. Here’s how we do it:
A3: =INDEX(Code,1+INT(RAND()*50000))
The RAND function is volatile; so each time Excel calculates, RAND returns a new random number between 0 and 1. The formula in cell A3 multiplies that random decimal value by 50,000; it finds the integer value of this result, which will be in the range 0-49,999; then it adds 1 to the integer value to bring it to the range 1-50,000. Finally, this formula returns the value from the randomly chosen index number within the Code range.
Copy the cell A3 to the range A3:A1002, giving you a report with 1000 rows.
In the next post, I’ll explain the formulas in the other columns and discuss their calculation times.
{ 2 comments }
As is usually the case with Excel, there are multiple ways to achieve the same thing. Another way to jump to a specific cell is to type it into the name box (which is the box near the top left of your excel window, to the left of the formula bar). This normally displays the cell address or range name of the current active cell. If you type a cell address and hit enter you jump to that cell. If you type a valid name and hit enter, it assigns the name to the currently selected range.
PS Great blog Charley
Hi Charley,
I hadn’t thought about the volatility issue when testing calculations. Nice one!
Another way to fill the formula down:
Enter formula in A2
In the Name box type A50001, then Shift+Enter
F2
Ctrl+Enter