﻿ Set Formulas | SpreadJS 15
Features / Formulas / Set Formulas
In This Topic
Set Formulas
In This Topic

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

#### Set Formula in Cells

This example sets formulas in cells.

JavaScript
Copy Code
```window.onload = function()
{
{sheetCount:3});
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. Taking an example, the following image displays the first sheet.

The following image displays the second sheet.

This example uses cross-sheet references.

JavaScript
Copy Code
```window.onload = function()
{
sheet1.name("Sheet1");
sheet2.name("Sheet2");

sheet1.setValue(0, 0, 1000);
sheet1.setValue(1, 0, 2000);
sheet1.setValue(2, 0, 3000);
sheet1.setValue(3, 0, 4000);
sheet1.getRange(0, 0, 4, 1).backColor("lightPink");
sheet2.setValue(0, 0, 5000);
sheet2.getRange(0, 0, 1, 1).backColor("lightCyan");

// Set a sum formula of "Sheet1: total of cell(0,0) to cell(3,0)" and "Sheet2: cell(0,0)".
sheet2.setFormula(1, 1, "SUM(Sheet1!A1:A4) + Sheet2!A1");
sheet2.getRange(1, 1, 1, 1).backColor("lemonChiffon");
}
```

### Set Subtotal Formula

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

This example uses a subtotal formula.

JavaScript
Copy Code
```\$(document).ready(function ()
{
{sheetCount:3});

activeSheet.setColumnCount(2);
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)");
});
```