What are Iterative Calculations?
Iterative calculations can help solving problems with several variables. They find use in pretty much every field and businesses use this logic in assessing risk or determining general characteristics of a solution pool. The idea is running the given scenario repeatedly for a number of times until a solution is found.
When working on a spreadsheet, if you try to create a reference of a cell that contains a formula, you will get an infinite loop. Excel can’t auto-calculate an infinite loop and you will have to define an iteration count. Instead, Solver and Goal-Seek should be utilized as mainstream tools for what-if analysis. Adjusting the input variables, the model will give us the optimum solution after a series of iterations. Since it’s really easy and cost effective to use spreadsheets, most companies keep their models in Excel.
Applying Iterative Calculation to Your Web Applications with the SpreadsheetWEB API
Many businesses develop proprietary models in Excel using goal-seek and solver. Often, there is a need to share them with others securely or integrate these models with other applications.
Converting these spreadsheets to web applications with SpreadsheetWEB provides a secure and scalable delivery mechanism for collaboration and integration of your proprietary models. To do this, we will call the SpreadsheetWEB API for the external values used in circular reference calculations (values not affected by the circular reference), perform the circular calculation on the web, and then set the calculation results back to the workbook and perform non-circular calculation on excel.
Implementing Iterative Calculation Programmatically
The iterative calculation is set using “do-while” loop for reaching an equilibrium for the circular reference.
The iterative structure is to be set as follows:
- Define all starting criteria and target values.
- Define the inputs and outputs (I/O) that will be consumed by the API to run the calculations.
- Define the calcIterarion This will be used for applying the iterative logic and calling the SpreadsheetWEB API. The callback function will produce the results.
This specific workflow is as follows:
- The GetResultfunction of the API is executed with the initial Coefficient value, requesting baseCaseList, cyftoCufList and tableCoeffList as output from the API.
- Once the API returns the output, the callback function will map the results to the variables, respectively.
- calcIteration function will calculate the column J values and make another call to SpreadsheetWEB API with these values as input.
- Result of the last call will be displayed on a table.
And, here is the full code:
function getCoefficients() {
var inputList = new Array();
inputList[0] = 'tVal;' + $('#txtCoefficient').val();
var outputList = new Array();
outputList[0] = "baseCaseList";
outputList[1] = "cyftoCufList";
outputList[2] = "tableCoeffList";
GetResult($('#hdApplicationKey').val(), '', false, inputList, outputList, function() {
if (value != undefined) {
baseCaseList = [];
cyftoCufList = [];
tableCoeffList = [];
jsonVal = JSON.parse(value);
if (typeof jsonVal == "object" && jsonVal.OutputRanges != null) { //GetResultJSON
jsonVal.OutputRanges[0].Value.forEach(function(val) {
baseCaseList.push(val[0]);
});
jsonVal.OutputRanges[1].Value.forEach(function(val) {
cyftoCufList.push(val[0]);
});
jsonVal.OutputRanges[2].Value.forEach(function(val) {
tableCoeffList.push(val[0]);
});
calcIteration();
}
}
});
}
function calcIteration() {
var tVal = $('#txtCoefficient').val(),
//coefficient entered by user
columnJ = [],
//variable for storing iteration results
baseVal = 0.001,
//starting value
fPrev = 0,
//variable for storing previous column F value
jPrev = 0;
//variable for storing previous column J value
//calculating values inside circular reference based on excel for each row
baseCaseList.forEach(function(val, i) {
var fMultiplier = Math.exp(-1 * baseCaseList[i] * tVal),
//external multiplier used in column F
iMultiplier = cyftoCufList[i] / 100,
//external multiplier used in column I
fVal = 0,
gVal = 0,
hVal = 0,
iVal = 0,
jVal = 0,
j = 0;
//calculating values inside circular reference based on excel
do {
j++;
fVal = baseVal * fMultiplier;
gVal = fVal - fPrev;
hVal = tableCoeffList[i] + gVal;
iVal = hVal * iMultiplier;
jVal = jPrev + iVal;
if (baseVal < jVal)
baseVal += (jVal - baseVal) * 1.001;
else if (baseVal > jVal)
baseVal -= (baseVal - jVal) * 1.001;
}
while (baseVal != jVal && j < 1000);
fPrev = fVal;
jPrev = jVal;
columnJ.push(jVal);
});
//preparing SpreadsheetWebAPI call arguments
var inputList = new Array();
inputList[0] = 'tVal;' + $('#txtCoefficient').val();
inputList[1] = 'columnJ;' + columnJ.join("~");
var outputList = new Array();
outputList[0] = "results";
GetResult($('#hdApplicationKey').val(), '', false, inputList, outputList, function() {
if (value != undefined) {
jsonVal = JSON.parse(value);
if (typeof jsonVal == "object" && jsonVal.OutputRanges != null) { //GetResultJSON
dataToTable(jsonVal.OutputRanges[0]);
}
}
});
}
Every business faces optimization problems all the time. Minimizing wait times, choosing the shortest path or best combination for production can create huge profits. From product mix to warehousing problems, from investment analysis to scheduling, what-if analysis comes very useful for finding the optimum settings of a process. Microsoft Excel aids users with Solver and Goal-Seek tools, but this doesn’t allow you to publish or share your work. Transition from Excel workbook to web allows for; server-grade processors to run complex models a lot faster, enhanced security and collaboration within your company.
SpreadsheetWEB removes the hassle of creating a web UI by giving you the flexibility and simplicity of Excel, while allowing you to move your business logic to the web.
