Multiple-Variable Data Tables in Excel
Summary
To set up a Data Table in Excel, your model needs to accept at least 1 input and calculate at least 1 output. The problem with this approach is that if you have 3 outputs, you need 3 Data Tables which requires Excel to run your model 3 times for each set of inputs. To convert the string created by ARRAYTOTEXT back into separate cells, we just use the formula =VALUE(TEXTSPLIT(D1,", ")) like this: This process involves a few more steps when setting up the data table, but its not too complicated if you have an example to work from. You can download the sample file here: There are three really important steps required for setting up an x-Input/y-Output scenario: This example is doing a Monte Carlo Simulation, so the input cell D29 that will be used by the Data Table is highlighted green and uses the formula =ARRAYTOTEXT(G22:G25) 2. The Data Table in the Monte Carlo Simulation example consists of 100 rows, and even with a model this simple, the recalculation is a bit slow (still under 1 second).
Classifications
AskAI Classifications
LabelsLinked Companies
