- Initial Load
- Method
- User Interface
Contact Support
Formulas
15 Mar 201924 minutes to read
Formulas are used for calculation of data in sheet. You can refer the formula cell reference in following ways,
- Cell reference from same sheet
- Cell reference from different sheets
You can set formula for a cell in following ways,
- Initial Load
- Method
- User Interface
Initial Load
You can set formula for a cell by specifying value property in cell data binding.
The following code example describes the above behavior,
<div id="Spreadsheet"></div>
<script>
$(function () {
$("#Spreadsheet").ejSpreadsheet({
sheets: [{
rows: [{
cells: [{
value: 1
}]
},
{
cells: [{
value: 2
}]
},
{
cells: [{
value: "=SUM(A1,A2)"
}]
}]
},
{
rows: [{
cells: [{
value: "=SUM(Sheet1!A1:A2)"
}]
}]
}],
});
});
</script>The following output is displayed as a result of the above code example.
Cell Reference from Same sheet
Cell Reference from different sheets
Method
You can set formula for a cell using updateCellValue method. The following code example describes the above behavior,
<div id="Spreadsheet"></div>
<script>
$(function () {
$("#Spreadsheet").ejSpreadsheet({
sheets: [{
rows: [{
cells: [{
value: 1
}]
},
{
cells: [{
value: 2
}]
}]
}],
loadComplete: "loadComplete"
});
});
function loadComplete() {
this.XLEdit.updateCellValue({ rowIndex: 2, colIndex: 0 }, "=SUM(A1,A2)");
}
</script>The following output is displayed as a result of the above code example.
User Interface
You can set formula for a cell by edit and save a cell through user interface using Editing feature. The following code example and screenshot describes the above behavior,
<div id="Spreadsheet"></div>
<script>
$(function () {
$("#Spreadsheet").ejSpreadsheet({
sheets: [{
rows: [{
cells: [{
value: 1
}]
},
{
cells: [{
value: 2
}]
}]
}]
});
});
</script>
The following output is displayed while saving edited cell with above code example.
NOTE
- The list of supported formulas can be find in following
link- Constant values, cell references, formulas and named ranges can be passed as argument to formulas
- Selection can be used to mention cell references within formula
User Defined Functions
The list of formulas supported in Spreadsheet is sufficient for most of your calculations. If not, you can create and use your own function using user defined function option. You can add user defined function to Spreadsheet in following ways,
-
Initial Load
-
Method
Initial Load
You can add your own function to Spreadsheet at initial load with customFormulas API. The following code example describes the above behavior,
<div id="Spreadsheet"></div>
<script>
$(function () {
$("#Spreadsheet").ejSpreadsheet({
sheets: [{
rows: [{
cells: [{
value: 1
}]
},
{
cells: [{
value: 2
}]
},
{
cells: [{
value: "=CUSTOMTOTAL(A1,A2,3)"
}]
}]
}],
customFormulas: [{"formulaName":"CUSTOMTOTAL", "functionName":"customTotal"}]
});
});
function customTotal(args) {
var param1, param2, param3, value, excelObj = $('#Spreadsheet').data("ejSpreadsheet"),
argument = excelObj.getValueFromFormulaArg(args);
param1 = argument["arg1"];
param2 = argument["arg2"];
param3 = argument["arg3"];
value = param1 * param2 + param3;
return value;
}
</script>The following output is displayed as a result of the above code example.
Method
You can add your own function to Spreadsheet using addCustomFormula method. The following code example describes the above behavior,
You can also calculate formulas in the specified sheet by usingcalcNow method in Spreadsheet.
<div id="Spreadsheet"></div>
<script>
$(function () {
$("#Spreadsheet").ejSpreadsheet({
sheets: [{
rows: [{
cells: [{
value: 1
}]
},
{
cells: [{
value: 2
}]
}]
}],
loadComplete: "loadComplete"
});
});
function loadComplete() {
this.addCustomFormula("CUSTOMTOTAL", "customTotal");
this.XLEdit.updateCellValue({ rowIndex: 2, colIndex: 0 }, "=CUSTOMTOTAL(A1,A2,3)");
}
function customTotal(args) {
var param1, param2, param3, value, excelObj = $('#Spreadsheet').data("ejSpreadsheet"),
argument = excelObj.getValueFromFormulaArg(args);
param1 = argument["arg1"];
param2 = argument["arg2"];
param3 = argument["arg3"];
value = param1 * param2 + param3;
return value;
}
</script>The following output is displayed as a result of the above code example.
To remove user defined function from Spreadsheet use removeCustomFormula method.
Named Ranges
To understand the purpose of cell reference or table, you can define a meaningful name using named ranges support. By using names, you can make your formula much easier to understand and maintain. You can add named ranges to Spreadsheet in following ways,
-
Initial Load
-
Method
-
User Interface
Initial Load
To add named ranges at initial load by using nameManager API, and also you can specify the name and address of the range by using name and refersto API. You can find the added named range list collection in Spreadsheet by accessing nameManager API.
The following code example describes the above behavior,
<div id="Spreadsheet"></div>
<script>
$(function () {
$("#Spreadsheet").ejSpreadsheet({
sheets: [{
rows: [{
cells: [{
value: 1
}]
},
{
cells: [{
value: 2
}]
},
{
cells: [{
value: "=SUM(inputrange)"
}]
}]
}],
nameManager: [{name: "inputrange", refersto: "=Sheet1!$A$1:$A$2"}]
});
});
</script>The following output is displayed as a result of the above code example.
Method
You can add named range to Spreadsheet with addNamedRange method and it can be removed with removeNamedRange method. The following code example describes the addNamedRange behavior,
<div id="Spreadsheet"></div>
<script>
$(function () {
$("#Spreadsheet").ejSpreadsheet({
sheets: [{
rows: [{
cells: [{
value: 1
}]
},
{
cells: [{
value: 2
}]
}]
}],
loadComplete: "loadComplete"
});
});
function loadComplete() {
this.XLRibbon.addNamedRange("inputrange", "=Sheet1!$A$1:$A$2", "named range demo", this.getActiveSheetIndex());
this.XLEdit.updateCellValue({rowIndex: 2, colIndex: 0}, "=SUM(inputrange)");
}
</script>The following output is displayed as a result of the above code example.
User Interface
You can define name for range of cells through user interface using Define Name option in OTHERS tab. The following screenshot describes the above behavior,
NOTE
Defining name for cell reference or table will be accessible across all sheets.
Named Ranges will be displayed in Name Manger dialog box.
Formula Bar
Formula bar is used to edit or enter cell data in much easier way. To enable formula bar set allowFormulaBar as true.You have following formula bar options in Spreadsheet.
1) Hide Formula Bar
2) Show Formula Bar
3) Update Formula Bar
Hide Formula Bar
To hide the formula bar in Spreadsheet. Use hideFormulaBar method to hide formula bar via code.
Show Formula Bar
To show the formula bar in Spreadsheet. Use showFormulaBar method to show formula bar via code.
Update Formula Bar
To Update the formula bar in Spreadsheet. Use updateFormulaBar method to Update formula bar via code.
Auto Sum
To sum a row or column of numbers, select a cell next to the numbers you want to sum, click AutoSum on the HOME tab and press enter. To enable auto sum set allowAutoSum API as true.
The auto sum options in ribbon is used to perform basic operations like sum, average, count, minimum, maximum etc.
You can also perform auto sum operations by using autoSummethod.
Array Formula
Array formula gives multiple results to the selected cells based on the provided formula and the formula reference values. It evaluates all individual values in the formula reference. you can achieve this array formula by pressing the ctrl+shift+enter.
Types of Array Formula
-
Single Array Formula
-
Multiple Array Formula
Single Array Formula
The selected range is single cell is called Single array formula. And you can modify the Single Array formula to default formula anytime by editing the formula cell.
Multiple Array Formula
The selected range contains multiple cells is called Multiple array formula. And you can’t modify it once the multiple array formula is created.
You can achieve this by the following steps,
- Enter the values in A1:B3
- Select a range C1:D3
- Give the formula =A1:A3 * B1:B3 in the formula Bar and press Ctrl + Shift + Enter key to perform the array formula.
- This formula is applied to all the selected cells with Curly braces ({=A1:A3 * B1:B3}) and displayed the calculated results.
Please find the output screen below.
Structured references with Table
The combination of Table and Column name is called as Structured reference. The names in structured reference adjust whenever the table data is modified. Table names and column header names are assigned while creating Tables. So, you can refer the Table cell value by using the Structured reference. The column header name should be enclosed with “[]”. This can be done by following ways.
- Enter the formula and define the structured reference by manually entering the column header name and table names.
- Enter the formula and Selecting the cell references will automatically update the column header names and Table names.
You can create the structured reference with table by the following steps:
-
The created Table Name is “Order”
-
The column header names are “OrderID, Price, Count, Amount “.
-
The formula should be “=SUM(Order[Amount])”
Structured reference components
-
Table Name
-
Column Specifier
-
Item Specifier
Table Name
- Order is a table name. It references the table data without header and total rows.
Column Specifier
-
[OrderID], [Price] are column specifiers. They reference the column data, without any column header and total row.
-
The formula should be “=SUM(Order[Amount])”, here Amount is the Column Specifier.
Please find the Column Specifier output screen below,
Item Specifier
-
[#All] and [#Data] are special item specifiers, that refers to the exact portion of the table.
-
To differentiate the [#All] and [#Data], you can enable the Total Row of the table.
-
The formula should be “=SUM(Order[#All])”, here All is the Item Specifier. In this formula calculates the sum of the entire table including Total row.
Please find the Item Specifier output screen below,
- The formula should be “=SUM(Order[#Data])”, here Data is the Item Specifier. In this formula calculates only the data of the table.
Please find the Item Specifier output screen below,
