How to iteratively run an Excel calculation model
Suppose we have a calculation model, in the form on an Excel workbook, that we want to “run” many times for different input values in order to compare or summarize results of each scenario. This is a very common task that many users of Excel are faced with on a regular basis – you may wish to do a “what-if” analysis and see and compare outcomes of different scenarios of the same model. SolveXia allows users to automate this task via the Run Excel Calculation Model step.
The Run Excel Calculation Model step is useful in any scenario where you need to repetitively apply the same Excel calculations to a sequence of different inputs. The inputs into the step are:
- a list of input values and
- a calculation model template in the form of an Excel workbook that contains the necessary formulae.
When run, the step will loop through the list of inputs one at a time, and for each input, apply the calculation model workbook to that input. The output of each calculation is then appended to a cell range within an output Excel sheet. Alternatively SolveXia allows to create a separate worksheet for each output )all within one output workbook), or to create a separate output workbook for each input.
The Run Excel Calculation Model step can be found in SolveXia Design View by adding an Action Step and then selecting Run Excel Calculation Model from the menu:
Selecting the step, you will see that it consists of four sections:
- a sequence of inputs,
- a calculation model template,
- an action to be performed on calculation and
- an action to be applied to outputs.
Configuring the Step:
In order to be able to iteratively execute an Excel calculation model, we configure the step specifications as follows:
Step 1 – Inputs: link to the workbook and cell range that contains the sequence of inputs. For example if the inputs are “Input 1, Input 2, …, Input 5” and are stored in cells A1:A5 of Sheet 1 of workbook Inputs.xlsx, as follows:
then the first section of the Run Excel Calculation Model will have the following configuration:
Step 2 – Calculation Model: now link the calculation model that you wish to apply to each of the inputs above. For example if the calculation model is in a workbook called Calcs.xlsx , Sheet 1, then link this workbook as follows:
The “calculation model” can be any Excel workbook that takes inputs and produces outputs. As a very simple example of a calculation model, please see the screenshot below. This model takes a string input (into cell C3) and if that input string is “Input 1” then it returns an output string (in cell C5) equal to “One”, and otherwise equal to “NOT One” – as per formula in the top right hand corner of the diagram.
Step 3 – Action: now link the inputs from Step 1 with the calculation model from Step 2. For example if we want to iterate through values in A1:A5 in the input worksheet (Inputs.xlsx, Sheet 1), placing each, one at a time, into cell C3 in Sheet 1 of the calculation model (Sheet 1 of workbook Calc.xlsx) the step will look as follows:
Note that since in Step 1 we have already specified the workbook and the sheet that contain inputs, we do not specify these again here, but instead we just specify the particular column that contains inputs.
Step 4 – Output: Here we can select what we want done with the output in each iteration of the step. The options available are as follows:
- On each iteration, create a new Excel file and write the output into this new file. SolveXia allows to dynamically specify names of Excel files and in the end they will all be stored in one zip file;
- On each iteration, create a new sheet within one consolidated Excel file. SolveXia allows to dynamically specify sheet names as we iterate through the inputs;
- On each iteration, add outputs to a designated cell range, all in one workbook and worksheet.
In the SolveXia Designer View these options appear as follows:
Depending on which method of output we select, the configuration will vary:
Output Option 1: Creating a new Excel file on each iteration:
and having run the step we would have a zip file called MyZipFile.zip that contains five Excel files. The names of these five Excel files will be taken from column 1 of the input parameter range (which in this case is Inputs.xlsx, Sheet1, cells A1:A5) and will therefore be “Input 1.xlsx”, …, “Input 5.xlsx”. Each such file will contain a worksheet called Sheet1 that will be a copy of Sheet1 from the Calc.xlsc model where input (cell C3) has been set to “Input 1”,…, “Input 5” respectively and formulae and results updated accordingly, i.e. “One”, “NOT One”,…, “NOT One”.
Output Option 2: Creating a new sheet within one consolidated Excel file:
Having run this we would end up with a workbook called NewlyCreatedOutputExcel.xlsx that contains five sheets: ‘Input 1’, ‘Input 2’, …, ‘Input 5’ – where the names ‘Input 1’,… ‘Input 5’ come from column A (column number 1) of the input range of Step 1. Each such sheet will contain in cell C3 the relevant input value (“Input 1”,…, “Input 5”) and in cell C5 the corresponding output value (“One”, “NOT One”,…, “NOT One”).
Output Option 3: Write all output values into one worksheet of the same Excel book:
This option will create a new Excel workbook (name specified by user, for example NewlyCreatedExcel.xlxs) prior to running the calculation models. Afterwards, as we iteratively apply the calculation model to the set of inputs, the outputs are appended to a range within this Excel workbook, starting from row as specified. In this example, as we apply calculation model to each input, we are copying cells C3 and C5 from each calculation model (i.e. input and output) and creating a table (starting from row 1) as follows: