Formulas in JavaScript Spreadsheet control

01 Jul 2022 / 11 minutes to read

Formulas are used for calculating the data in a worksheet. You can refer the cell reference from same sheet or from different sheets.

Usage

You can set formula for a cell in the following ways,

Using the formula property from cell, you can set the formula or expression to each cell at initial load.
Set the formula or expression through data binding.
You can set formula for a cell by editing.
Using the updateCell method, you can set or update the cell formula.

User Defined Functions

The list of formulas supported in the spreadsheet is sufficient for most of your calculations. If not, you can add your own custom function using the addCustomFunction method. Use computeExpression method, if you want to compute any formula or expression.

The following code example shows the calculation of data using supported and custom formulas in the spreadsheet.

Source

Preview

app.ts

index.html

datasource.ts

import { Spreadsheet, SheetModel, ColumnModel, RowModel } from '@syncfusion/ej2-spreadsheet';
import { data } from './datasource.ts';
import { enableRipple } from '@syncfusion/ej2-base';

enableRipple(true);

let columns: ColumnModel[] = [{ width: 150 }, { width: 120 }, { width: 120 }, { width: 120 }, { width: 120 }];

let rows: RowModel[] = [
{
    height: 40, customHeight: true, cells: [{ value: 'Monthly Expense', colSpan: 5, style:
    { textAlign: 'center', fontWeight: 'bold', verticalAlign: 'middle', fontStyle: 'italic', fontSize: '15pt' } }]
},
{ height: 30 },
{
    index: 11, cells: [{ value: 'Totals', style: { fontStyle: 'italic', fontWeight: 'bold' } }, { formula: '=SUM(B3:B11)' },
        // Calculating total of each column data through cell binding.
        { formula: '=SUM(C3:C11)' }, { formula: '=SUM(D3:D11)' }]
},
{
    cells: [{ index: 1, value: 'Number of Categories', colSpan: 2, style: { fontWeight: 'bold', textAlign: 'right' } },
        { index: 3, formula: '=COUNTA(A3:A11)' }]
},
{
    cells: [{ index: 1, value: 'Average Spend', colSpan: 2, style: { fontWeight: 'bold', textAlign: 'right' } },
        { index: 3, formula: '=AVERAGE(B3:B11)', format: '$#,##0' }]
},
{
    cells: [{ index: 1, value: 'Min Spend', colSpan: 2, style: { fontWeight: 'bold', textAlign: 'right' } },
        { index: 3, formula: '=MIN(B3:B11)', format: '$#,##0' }]
},
{
    cells: [{ index: 1, value: 'Max Spend', colSpan: 2, style: { fontWeight: 'bold', textAlign: 'right' } },
        { index: 3, formula: '=MAX(B3:B11)', format: '$#,##0' }]
}
];

let sheets: SheetModel[] = [{ ranges: [{ dataSource: data, startCell: 'A2' }], columns: columns, rows }];

// Custom function to calculate percentage between two cell values.
function calculatePercentage(firstCell: string, secondCell: string): number {
return Number(firstCell) / Number(secondCell);
}

let spreadsheet: Spreadsheet = new Spreadsheet({
sheets: sheets,
created: (): void => {
    spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:E2');
    spreadsheet.numberFormat('$#,##0', 'B3:D12');
    spreadsheet.numberFormat('0%', 'E3:E12');

    // Adding custom function for calculating the percentage between two cells.
    spreadsheet.addCustomFunction(calculatePercentage, 'PERCENTAGE');
    // Calculate percentage using custom added formula in E12 cell.
    spreadsheet.updateCell({ formula: '=PERCENTAGE(C12,D12)' }, 'E12');
},
showRibbon: false, showSheetTabs: false
});

spreadsheet.appendTo('#spreadsheet');

<!DOCTYPE html>
<html lang="en">

<head>
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0" />
        <meta name="description" content="Typescript UI Controls" />
        <meta name="author" content="Syncfusion" />
        <link rel="shortcut icon" href="resources/favicon.ico" />
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.2.36/ej2-base/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.2.36/ej2-inputs/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.2.36/ej2-buttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.2.36/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.2.36/ej2-lists/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.2.36/ej2-navigations/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.2.36/ej2-popups/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.2.36/ej2-dropdowns/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.2.36/ej2-grids/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.2.36/ej2-spreadsheet/styles/material.css" rel="stylesheet" />
        <link href="styles.css" rel="stylesheet" />
        <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
        <script src="system.config.js"></script>
        <script src="es5-datasource.js" type="text/javascript"></script>
</head>

<body>
        <!--Element which is going to render-->
        <div id='loader'>Loading....</div>
        <div id='container'>
                <div id="spreadsheet"></div>
        </div>
</body>

</html>

/**
 * Formula data source
 */
export let data: Object[] = [
    {
        'Category': 'Household Utilities',
        'Monthly Spend': '=C3/12', // Setting formula through data binding
        'Annual Spend': 3000,
        'Last Year Spend': 3000,
        'Percentage Change': '=C3/D3' // You can set the expression or formula as string
    },
    {
        'Category': 'Food',
        'Monthly Spend': '=C4/12',
        'Annual Spend': 2500,
        'Last Year Spend': 2250,
        'Percentage Change': { formula: '=C4/D4' } // You can also set as object with formula field
    },
    {
        'Category': 'Gasoline',
        'Monthly Spend': '=C5/12',
        'Annual Spend': 1500,
        'Last Year Spend': 1200,
        'Percentage Change': { formula: '=C5/D5' }
    },
    {
        'Category': 'Clothes',
        'Monthly Spend': '=C6/12',
        'Annual Spend': 1200,
        'Last Year Spend': 1000,
        'Percentage Change': '=C6/D6'
    },
    {
        'Category': 'Insurance',
        'Monthly Spend': '=C7/12',
        'Annual Spend': 1500,
        'Last Year Spend': 1500,
        'Percentage Change': '=C7/D7'
    },
    {
        'Category': 'Taxes',
        'Monthly Spend': '=C8/12',
        'Annual Spend': 3500,
        'Last Year Spend': 3500,
        'Percentage Change': '=C8/D8'
    },
    {
        'Category': 'Entertainment',
        'Monthly Spend': '=C9/12',
        'Annual Spend': 2000,
        'Last Year Spend': 2250,
        'Percentage Change': '=C9/D9'
    },
    {
        'Category': 'Vacation',
        'Monthly Spend': '=C10/12',
        'Annual Spend': 1500,
        'Last Year Spend': 2000,
        'Percentage Change': '=C10/D10'
    },
    {
        'Category': 'Miscellaneous',
        'Monthly Spend': '=C11/12',
        'Annual Spend': 1250,
        'Last Year Spend': 1558,
        'Percentage Change': '=C11/D11'
    }
];

Formula bar

Formula bar is used to edit or enter cell data in much easier way. By default, the formula bar is enabled in the spreadsheet. Use the showFormulaBar property to enable or disable the formula bar.

Named Ranges

You can define a meaningful name for a cell range and use it in the formula for calculation. It makes your formula much easier to understand and maintain. You can add named ranges to the Spreadsheet in the following ways,

Using the definedNames collection, you can add multiple named ranges at initial load.
Use the addDefinedName method to add a named range dynamically.
You can remove an added named range dynamically using the removeDefinedName method.
Select the range of cells, and then enter the name for the selected range in the Name box.

The following code example shows the usage of named ranges support.