Iterative Calculation

SpreadJS provides support for iterative calculation, also referred to as a circular reference, just like in Excel. In general, Iterative calculation is the repeated calculation of a worksheet until a definite numeric condition is met. It is used when the formula in a cell directly or indirectly refers to its cell in spreadsheets to recalculate the formula until a specific result is found.

You can enable iterative calculation in worksheets by using the iterativeCalculation property. You can also specify the number of times the formula should recalculate by setting the iterativeCalculationMaximumIterations property. Further, you can also specify the maximum amount of change between two calculation values by setting the iterativeCalculationMaximumChange property.

On enabling the iterative calculation, all the circular references will iteratively calculate until all the value change is smaller than the IterativeCalculationMaximumChange or the iterate count is IterativeCalculationMaximumIterations. However, on disabling the iterative calculation, all the circular reference cell values will become zero, and the other cells referenced by it will also become zero.

Use-Cases

You can use iterative calculation in the following use-cases:

Calculating Customer's Future Investment Value

Let's assume that a customer has $50,000, and wants to invest this money in a cash deposit (CD) account to incur a monthly interest rate of 4.75%. To calculate the total value after 2 years, we’re going to calculate the principal for each month, and add the interest to the previous month. Here, we set the iterativeCalculation property to True to enable iterative calculations and set the Maximum Iterations to 24 to find the interest after 24 months (2 years), that is, the total cash value at the end of the 25th month.

This use-case can be depicted using the code sample below:

$(document).ready(function () {
    // Initializing Spread
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
    // Get the activesheet
    var sheet = spread.getSheet(0);
    
    // set column width
    sheet.setColumnWidth(0, 30);
    sheet.setColumnWidth(1, 110);
    sheet.setColumnWidth(2, 130);
    sheet.setColumnWidth(3, 30);
    sheet.setColumnWidth(4, 30);
    sheet.setColumnWidth(5, 230);
    sheet.setColumnWidth(6, 130);
    
    // enable IterativeCalculation
    spread.options.iterativeCalculation = true;
    spread.options.iterativeCalculationMaximumIterations = 24;
    spread.options.iterativeCalculationMaximumChange = 0.1;
    
    // set text and values
    sheet.setText(1, 1, "Initial Cash");
    sheet.setValue(1, 2, 50000);
    sheet.setText(2, 1, "Interest");
    sheet.setValue(2, 2, 0.0475);
    sheet.setText(4, 1, "Month/ Year");
    sheet.setText(4, 2, "Total Cash");
    /
    / create array
    var items = [
    "Jan' 2019", "Feb' 2019", "Mar' 2019", "April' 2019", "May' 2019", "June' 2019", "July' 2019", "Aug' 2019", "Sept' 2019", "Oct' 2019", "Nov' 2019", "Dec' 2019",
    "Jan' 2020", "Feb' 2020", "Mar' 2020", "April' 2020", "May' 2020", "June' 2020", "July' 2020", "Aug' 2020", "Sept' 2020", "Oct' 2020", "Nov' 2020", "Dec' 2020", "Jan' 2021"
    ];
    // set array
    sheet.setArray(5, 1, items);
    
    // set formulas
    sheet.setFormula(5, 2, '=C2*(1+C3)');
    sheet.setFormula(6, 2, '=C6*(1+C3)');
    for (var i = 6; i < 30; i++) {
        sheet.setFormula(i, 2, '=C' + i + '*(1+C3)');
    }
    
    // create table to add style on a range
    sheet.tables.add("Table1", 4, 1, 26, 2, GC.Spread.Sheets.Tables.TableThemes.medium6);
    // set values and formulas
    sheet.setText(1, 5, "Initial Cash");
    sheet.setValue(1, 6, 50000);
    sheet.setText(2, 5, "Interest");
    sheet.setValue(2, 6, 0.0475);
    sheet.setText(3, 5, "Total Cash after 2 yrs (as on Jan' 2021)");
    sheet.setFormula(3, 6, '=G2*(1+G3)');
    
    // this formula will generate circle reference
    sheet.setFormula(1, 6, '=G4');
    
    // set formatter
    sheet.setFormatter(1, 2, '$#,##0.00');
    sheet.setFormatter(2, 2, '0.00%');
    sheet.setFormatter(1, 6, '$#,##0.00');
    sheet.setFormatter(2, 6, '0.00%');
    sheet.setFormatter(3, 6, '$#,##0.00');
    sheet.setFormatter(5, 2, '$#,##0.00');
    for (var i = 6; i < 30; i++) {
        sheet.setFormatter(i, 2, '$#,##0.00');
    }
    
    // set border for cell ranges
    sheet.getRange("B2:C3", GC.Spread.Sheets.SheetArea.viewport).setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { all: true }, 3);
    sheet.getRange("F2:G4", GC.Spread.Sheets.SheetArea.viewport).setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { all: true }, 3);
    sheet.getRange("B5:C30", GC.Spread.Sheets.SheetArea.viewport).setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { all: true }, 3);
    // set backcolor for cell ranges
    sheet.getRange("C2:C3").backColor("#D9E3F2");
    sheet.getRange("G2:G4").backColor("#D9E3F2");
});

Adding Timestamps to Cells

Iterative calculation can be used to set timestamps in worksheets. For instance, you are managing a list of tasks and want to add a date and time in the adjacent cell after each task. To enable iterative calculation for timestamp use-case, we have set the iterativeCalculation property to true. Here, we have used the iterativeCalculationMaximumIterations property and set it to 24, so that it tells SpreadJS the maximum number of times it should run the formula. With iterative calculation, each time we enter a new item in the new row of the 'Employee Name' column, a timestamp will be automatically updated in the 'TimeStamp' column until it reaches the 23rd row.

This use-case can be depicted using the code sample below:

$(document).ready(function () {
    // Initializing Spread
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
    // Get the activesheet
    var sheet = spread.getSheet(0);
    sheet.suspendPaint();
    // set column width
    sheet.setColumnWidth(0, 200);
    sheet.setColumnWidth(1, 200);
    
    // enable IterativeCalculation
    spread.options.iterativeCalculation = true;
    spread.options.iterativeCalculationMaximumIterations = 24;
    spread.options.iterativeCalculationMaximumChange = 0.1;
    
    // set backcolor for cell ranges
    sheet.getRange("A1:B1").backColor("#D9E3F2");
    // set text in cells
    sheet.setText(0, 0, "Employee Name");
    sheet.setText(0, 1, "TimeStamp");
    for (var i = 2; i < spread.options.iterativeCalculationMaximumIterations; i++) {
        sheet.setFormula(i - 1, 1, '=IF(A' + i + '<>"",IF(B' + i + '<>"",B' + i + ',NOW()),"")');
        sheet.setFormatter(i - 1, 1, 'dd-mm-yyyy hh:mm:ss');
    }
    
    // Sample data
    sheet.setText(1, 0, "Andy");
    var style = new GC.Spread.Sheets.Style();
    style.font = "bold 12px Arial";
    style.foreColor = "white";
    style.backColor = "#5B9BD5";
    style.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    style.vAlign = GC.Spread.Sheets.VerticalAlign.center;
    for (var i = 0; i < 2; i++)
        sheet.setStyle(0, i, style, GC.Spread.Sheets.SheetArea.viewport);
    sheet.resumePaint();
});