by Sam Savage, Executive Director, and and Dave Empey, Director of Software Development, ProbabilityManagement.org
No, no, no! We don’t mean a killer app using the Excel Data Table, like SIPmath simulation. We mean an app that will kill your data table, for example, your SIPmath simulation.
It all started last week when the Dice Calculator (Excel file), which is supposed to instantaneously roll a pair of dice 10,000 times, suddenly started taking ten seconds on one of our machines. Flash back to the late 1980’s when Bill Sharpe, Nobel Laureate in Economics, discovered that the data table in Lotus 1-2-3 could perform simple Monte Carlo simulations. We tried this in Excel in the early 1990s, and although it showed great promise, it often caused the spreadsheet program to crash unceremoniously. When we discovered in 2012 that the Excel Data Table could instantaneously perform tens of thousands of calculations of the Rand() formula, we were ecstatic. Furthermore, using the Index formula, Excel could read SIPs as well. With interactive Monte Carlo simulation available on every desktop, we were able to get corporate sponsorship for ProbabilityManagement.org, and we incorporated as a 501(c)(3) nonprofit in 2013.
But where were we? Oh yes. There are only two things that really keep us at up at night.
The first is that Jensen’s Inequality (the strong form of the Flaw of Averages) will be declared to be Fake Math. We have been working together for decades, offering a money back guarantee to our consulting clients on the validity of this well-established mathematical result. If the internet deems it false, our careers are over.
The second nightmare is that the Excel Data Table, which has done for simulation what penicillin did for bacterial disease, ceases to work. This would spell the end of SIPmath for Excel.
So, when something that was supposed to be instantaneous took ten seconds, we freaked out. We re-installed Excel twice on the offending machine, but nothing worked. Then we realized that the installation process was so seamless that it left all the Excel add-ins in place. By process of elimination we found that one of our own experimental add-ins was slowing down any instance of the Data Table by orders of magnitude.
Volatile Functions
Here’s the scoop. Some formulas in Excel are known as Volatile, because they recalculate with each keystroke. Most formulas do not have this feature. For example, if cell A1 contains =B1+C1, then A1 will not re-calculate unless either B1 or C1 change. RAND(), on the other hand is Volatile. Since it doesn’t depend on anything it needs to change with every keystroke.
Warning: Do not use a SIPmath model in Excel while another workbook is open that contains RAND() or it will run very slowly.
We have known that for a long time. But what does that have to do with an add-in? Well, our add-in had Excel worksheets built into it for use as templates. They didn’t use RAND(), but they did use other Volatile functions, such as OFFSET. Worse they used OFFSETs that drove hundreds of other cells. It was like having hundreds of Volatile cells in Excel all the time, whenever the add-in was loaded.
Updated Warning: Do not use Volatile functions in the vicinity of SIPmath models. That is, close all worksheets with Volatile functions before using a SIPmath model. You can use RAND() in a SIPmath model, but not the model next door. And there are some other exceptions that seem to work. But please be careful or your models will grind to a halt just as you are making that great analytical pitch.
To better understand this phenomenon, we created a killer app in Excel that destroys the performance of the Data Table in any worksheet. At first, we planned to publish it as a download with this blog. But on second thought that would be like publishing plans for a weapon of mass destruction, so we are keeping it in hermetically sealed container in the lab.
To learn more about Volatile functions, see http://www.decisionmodels.com/calcsecretsi.htm.