Sensitivity Analysis with 100K Formulas

February 11, 2022 - by Bill Jelen

Problem: The article Do 40 What-if Analyses Quickly undersells how powerful the Data Table tool is. The loan payment scenario contains just one formula.

Solution: In a 2018 competition with other Excel channels on YouTube, I created a model with 100,000 VLOOKUP formulas based on 100,000 NORM.INV formulas with 100,000 RAND functions. This model predicted the chance of asteroid Bennu impacting Earth in the 2192 time frame. I then ran the model with 300,000 calculations through a data table 100 times. In all, the table performed 30 million calculations and would calculate in under 30 seconds.

A model with 200,000 formulas that lead to a single result. Using What-If Data Tables, this model runs the 200,000 calculations 100 times each and logs the results.

Figure 329. Modeling the likelihood of the Bennu Crater tourist attraction by 2196.

This article is an excerpt from Power Excel With MrExcel

Title photo by agata herbata on Unsplash