Formulas are used for calculating the data in a worksheet. You can refer the cell reference from same sheet or from different sheets.
You can set formula for a cell in the following ways,
formula
property from cell
, you can set the formula or expression to each cell at initial load.editing
.updateCell
method, you can set or update the cell formula.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.
import { Component, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';
import { enableRipple } from '@syncfusion/ej2-base';
import { dataSource } from './datasource';
enableRipple(true);
@Component({
selector: 'app-container',
template: `<ejs-spreadsheet #spreadsheet (created)="created()" [showRibbon]="false"
[showSheetTabs]="false">
<e-sheets>
<e-sheet>
<e-ranges>
<e-range [dataSource]="data" startCell="A2"></e-range>
</e-ranges>
<e-columns>
<e-column [width]=150></e-column>
<e-column [width]=120></e-column>
<e-column [width]=120></e-column>
<e-column [width]=120></e-column>
<e-column [width]=120></e-column>
</e-columns>
<e-rows>
<e-row [height]=40 [customHeight]="true">
<e-cells>
<e-cell value="Monthly Expense" [colSpan]=5 [style]="{ textAlign: 'center', fontWeight: 'bold', verticalAlign: 'middle', fontStyle: 'italic', fontSize: '15pt' }"></e-cell>
<e-cell formula="=SUM(E2:E10)"></e-cell>
</e-cells>
</e-row>
<e-row [index]=11>
<e-cells>
<e-cell value="Totals" [style]="{ fontStyle: 'italic', fontWeight: 'bold' }"></e-cell>
<!-- Calculating total of each column data through cell binding. -->
<e-cell formula="=SUM(B3:B11)"></e-cell>
<e-cell formula="=SUM(C3:C11)"></e-cell>
<e-cell formula="=SUM(D3:D11)"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell [index]=1 value="Number of Categories" [colSpan]=2 [style]="{ fontWeight: 'bold', textAlign: 'right' }"></e-cell>
<e-cell [index]=3 formula="=COUNTA(A3:A11)"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell [index]=1 value="Average Spend" [colSpan]=2 [style]="{ fontWeight: 'bold', textAlign: 'right' }"></e-cell>
<e-cell [index]=3 formula="=AVERAGE(B3:B11)" format="$#,##0"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell [index]=1 value="Min Spend" [colSpan]=2 [style]="{ fontWeight: 'bold', textAlign: 'right' }"></e-cell>
<e-cell [index]=3 formula="=MIN(B3:B11)" format="$#,##0"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell [index]=1 value="Max Spend" [colSpan]=2 [style]="{ fontWeight: 'bold', textAlign: 'right' }"></e-cell>
<e-cell [index]=3 formula="=Max(B3:B11)" format="$#,##0"></e-cell>
</e-cells>
</e-row>
</e-rows>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>`
})
export class AppComponent {
@ViewChild('spreadsheet')
spreadsheetObj: SpreadsheetComponent;
data: object[] = dataSource;
// Custom function to calculate percentage between two cell values.
calculatePercentage(firstCell: string, secondCell: string): number {
return Number(firstCell) / Number(secondCell);
}
created() {
this.spreadsheetObj.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:E2');
this.spreadsheetObj.numberFormat('$#,##0', 'B3:D12');
this.spreadsheetObj.numberFormat('0%', 'E3:E12');
// Adding custom function for calculating the percentage between two cells.
this.spreadsheetObj.addCustomFunction(this.calculatePercentage, 'PERCENTAGE');
// Calculate percentage using custom added formula in E12 cell.
this.spreadsheetObj.updateCell({ formula: '=PERCENTAGE(C12,D12)' }, 'E12');
this.spreadsheetObj.setRowHeight(30,1);
}
}
import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { SpreadsheetAllModule } from '@syncfusion/ej2-angular-spreadsheet';
import { AppComponent } from './app.component';
/**
* Module
*/
@NgModule({
imports: [
BrowserModule,
SpreadsheetAllModule
],
declarations: [AppComponent],
bootstrap: [AppComponent]
})
export class AppModule { }
/**
* Formula data source
*/
export let dataSource: 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'
}
];
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { enableProdMode } from '@angular/core';
import { AppModule } from './app.module';
enableProdMode();
platformBrowserDynamic().bootstrapModule(AppModule);
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.
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,
definedNames
collection, you can add multiple named ranges at initial load.addDefinedName
method to add a named range dynamically.removeDefinedName
method.The following code example shows the usage of named ranges support.
import { Component, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';
import { enableRipple } from '@syncfusion/ej2-base';
import { dataSource } from './datasource';
enableRipple(true);
@Component({
selector: 'app-container',
template: `<ejs-spreadsheet #spreadsheet (created)="created()" (beforeDataBound)="beforeDataBound()" [showRibbon]="false" [showSheetTabs]="false">
<e-definednames>
<!-- Setting names for 'categories', 'monthly spendings' and 'annual spendings' ranges. -->
<e-definedname name="Categories" refersTo="=Budget Details!A3:A11"></e-definedname>
<e-definedname name="MonthlySpendings" refersTo="=Budget Details!B3:B11"></e-definedname>
<e-definedname name="AnnualSpendings" refersTo="=Budget Details!C3:C11"></e-definedname>
</e-definednames>
<e-sheets>
<e-sheet name="Budget Details">
<e-ranges>
<e-range [dataSource]="data" startCell="A2"></e-range>
</e-ranges>
<e-columns>
<e-column [width]=150></e-column>
<e-column [width]=120></e-column>
<e-column [width]=120></e-column>
<e-column [width]=120></e-column>
<e-column [width]=120></e-column>
</e-columns>
<e-rows>
<e-row [height]=40 [customHeight]="true">
<e-cells>
<e-cell value="Monthly Expense" [colSpan]=5 [style]="{ textAlign: 'center', fontWeight: 'bold', verticalAlign: 'middle', fontStyle: 'italic', fontSize: '15pt' }"></e-cell>
<e-cell formula="=SUM(E2:E10)"></e-cell>
</e-cells>
</e-row>
<e-row [index]=11>
<e-cells>
<e-cell value="Totals" [style]="{ fontStyle: 'italic', fontWeight: 'bold' }"></e-cell>
<!-- Initializing the formulas using defined names. -->
<e-cell formula="=SUM(MonthlySpendings)"></e-cell>
<e-cell formula="=SUM(AnnualSpendings)"></e-cell>
<e-cell formula="=SUM(LastYearSpendings)"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell [index]=1 value="Number of Categories" [colSpan]=2 [style]="{ fontWeight: 'bold', textAlign: 'right' }"></e-cell>
<e-cell [index]=3 formula="=COUNTA(Categories)"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell [index]=1 value="Average Spend" [colSpan]=2 [style]="{ fontWeight: 'bold', textAlign: 'right' }"></e-cell>
<e-cell [index]=3 formula="=AVERAGE(MonthlySpendings)" format="$#,##0"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell [index]=1 value="Min Spend" [colSpan]=2 [style]="{ fontWeight: 'bold', textAlign: 'right' }"></e-cell>
<e-cell [index]=3 formula="=MIN(MonthlySpendings)" format="$#,##0"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell [index]=1 value="Max Spend" [colSpan]=2 [style]="{ fontWeight: 'bold', textAlign: 'right' }"></e-cell>
<e-cell [index]=3 formula="=Max(MonthlySpendings)" format="$#,##0"></e-cell>
</e-cells>
</e-row>
</e-rows>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>`
})
export class AppComponent {
@ViewChild('spreadsheet')
spreadsheetObj: SpreadsheetComponent;
data: object[] = dataSource;
beforeDataBound() {
// Adding name dynamically for `last year spending` and `percentage change` ranges.
this.spreadsheetObj.addDefinedName({ name: 'LastYearSpendings', refersTo: '=D3:D11' });
this.spreadsheetObj.addDefinedName({ name: 'PercentageChange', refersTo: '=E3:E11' });
}
created() {
// Removing the unwanted `PercentageChange` named range
this.spreadsheetObj.removeDefinedName('PercentageChange', '');
this.spreadsheetObj.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:E2');
this.spreadsheetObj.numberFormat('$#,##0', 'B3:D12');
this.spreadsheetObj.numberFormat('0%', 'E3:E12');
this.spreadsheetObj.setRowHeight(30,1);
}
}
import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { SpreadsheetAllModule } from '@syncfusion/ej2-angular-spreadsheet';
import { AppComponent } from './app.component';
/**
* Module
*/
@NgModule({
imports: [
BrowserModule,
SpreadsheetAllModule
],
declarations: [AppComponent],
bootstrap: [AppComponent]
})
export class AppModule { }
/**
* Named range data source
*/
export let dataSource: 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'
}
];
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { enableProdMode } from '@angular/core';
import { AppModule } from './app.module';
enableProdMode();
platformBrowserDynamic().bootstrapModule(AppModule);
The list of supported formulas can be find in following link
.