Set Formulas

In SpreadJS, you can set formulas in cells by using the setFormula method of the Worksheet class. The below image displays resultant values in column B after different formulas are being applied to it.

Set Formula in Cells

The following code sample sets formulas in cells.

window.onload = function()
{
   var spread =
   new GC.Spread.Sheets.Workbook(document.getElementById("ss"),
   {sheetCount:3});
   var activeSheet = spread.getActiveSheet();
   activeSheet.setRowCount(5);
   activeSheet.setColumnCount(2);
   activeSheet.getRange(-1, 1, -1, 1)
   .backColor("lightYellow")
   .width(120);
   activeSheet.setValue(0, 0, 10);
   activeSheet.setValue(1, 0, 20);
   activeSheet.setValue(2, 0, 30);
   activeSheet.setValue(3, 0, 40);
   activeSheet.setValue(4, 0, 50);

   // Set SUM function (Sum of all parameter values).
   activeSheet.setFormula(0, 1, "SUM(A1:A5)");
 
   // Set PRODUCT function (Product of all parameter values).
   activeSheet.setFormula(1, 1, "PRODUCT(A1:A5)");

   // Set AVERAGE function (Average of all parameter values).
   activeSheet.setFormula(2, 1, "AVERAGE(A1:A5)");

  // Set the sum of cell(0,0) and cell(4,0).
  activeSheet.setFormula(3, 1, "A1 + A5");

  /* Multiply cell(0,0) by 2 if the value in this cell is greater than 10,
    otherwise multiply it by 3. */
   activeSheet.setFormula(4, 1, "IF(A1>10, A1*2, A1*3)");
}

Set Formula with Cross Sheet References

You can reference other sheets in a formula.

To reference other sheets in a formula while maintaining the editing status, select the target sheet from the sheet tabs or the AllSheetsList button and select a cell range of that sheet. Note that you cannot edit the formula using keyboard when the list of all sheets is visible. You must click any other area of the sheet to close the list and exit the editing status.

For example, in the following image, the values C5 to G5 of Math - Grade 5 sheet have been used to calculate the total score of a student in sheet Anna Mull.

cross-worksheet-reference

The following code sample uses cross-sheet references.

window.onload = function () {
    // create spread sheet by calling constructor 
    let spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 });
    initSpread(spread);
};

function initSpread(spread) {
    spread.suspendPaint();
    //load the Template
    spread.fromJSON(template);

    //set formula to annaMull Sheet
    let sheet = spread.getSheetFromName("Anna Mull")

    sheet.setText(3,0,"Average Score"); 
    sheet.setFormula(3,1,"=Average('Math - Grade 5'!C5:G5)"); 
    sheet.setText(4,0,"Total Assignment Score"); 
	sheet.setFormula(4,1, "=Sum('Math - Grade 5'!C5:G5)");
    spread.resumePaint();
}

Set Subtotal Formula

You can set the SUBTOTAL formula as displayed in the below image.

The following code sample uses a subtotal formula.

$(document).ready(function ()
{
    var spread =
    new GC.Spread.Sheets.Workbook(document.getElementById("ss"),
    {sheetCount:3});
   
    var activeSheet = spread.getActiveSheet();
    activeSheet.setColumnCount(2);
    activeSheet.options.colHeaderAutoText = GC.Spread.Sheets.HeaderAutoText.blank;
    activeSheet.setText(0, 1, "Value", GC.Spread.Sheets.SheetArea.colHeader);
    activeSheet.options.gridline = {showHorizontalGridline: false};
    activeSheet.setText(3, 0, "Sub-Total1");
    activeSheet.setText(7, 0, "Sub-Total2");
    activeSheet.setText(8, 0, "Aggregate");
    activeSheet.getRange(3, -1, 1, -1).backColor("lemonChiffon");
    activeSheet.getRange(7, -1, 1, -1).backColor("lemonChiffon");
    activeSheet.getRange(8, -1, 1, -1).backColor("lightPink");
    activeSheet.setValue(0, 1, 100);
    activeSheet.setValue(1, 1, 200);
    activeSheet.setValue(2, 1, 300);
    activeSheet.setValue(4, 1, 400);
    activeSheet.setValue(5, 1, 500);
    activeSheet.setValue(6, 1, 600);
  
   // Set the sub-total and the aggregate by using SUBTOTAL function.
   activeSheet.setFormula(3, 1, "SUBTOTAL(9,B1:B3)");
   activeSheet.setFormula(7, 1, "SUBTOTAL(9,B5:B7)");
   activeSheet.setFormula(8, 1, "SUBTOTAL(9,B1:B7)");
});