Create User Defined Functions / Custom Functions in React Spreadsheet
27 May 202624 minutes to read
The Spreadsheet includes a set of built-in formulas. For convenience, you can find the list of supported formulas here.
You can also define and use formulas that are not supported by default, known as user defined/custom formulas, by using the addCustomFunction function. Keep in mind that a user defined/custom formula should return only a single value. If the formula returns an array, updating adjacent cell values will take more time and may affect performance.
The following code example shows how to use an unsupported formula in the Spreadsheet.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, RowsDirective, RowDirective, CellDirective, CellsDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';
function App() {
const spreadsheetRef = React.useRef(null);
const styles = { textAlign: 'center', fontWeight: 'bold', verticalAlign: 'middle', fontStyle: 'italic', fontSize: '15pt' };
const cellStyle = { fontStyle: 'italic', fontWeight: 'bold' };
const fontStyle = { fontWeight: 'bold', textAlign: 'right' };
// Custom function to calculate percentage between two cell values.
const calculatePercentage = (firstCell, secondCell) => {
return Number(firstCell) / Number(secondCell);
};
// Custom function to calculate round down for values.
const roundDownHandler = (value, digit) => {
let multiplier = Math.pow(10, digit);
return Math.floor(value * multiplier) / multiplier;
}
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:F2');
spreadsheet.numberFormat('$#,##0', 'B3:D12');
spreadsheet.numberFormat('0%', 'E3:E12');
// Adding custom function for calculating the percentage between two cells.
spreadsheet.addCustomFunction(calculatePercentage, 'PERCENTAGE');
// Adding custom function for calculating round down for the value.
spreadsheet.addCustomFunction(roundDownHandler, 'ROUNDDOWN');
// Calculate percentage using custom added formula in E12 cell.
spreadsheet.updateCell({ formula: '=PERCENTAGE(C12,D12)' }, 'E12');
// Calculate round down for average values using custom added formula in F12 cell.
spreadsheet.updateCell({ formula: '=ROUNDDOWN(F11,1)' }, 'F12');
}
}, []);
return (<div>
<SpreadsheetComponent ref={spreadsheetRef} showSheetTabs={false} showRibbon={false}>
<SheetsDirective>
<SheetDirective>
<RowsDirective>
<RowDirective height={40} customHeight={true}>
<CellsDirective>
<CellDirective value={'Monthly Expense'} style={styles} colSpan={5}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective height={30}></RowDirective>
<RowDirective index={11}>
<CellsDirective>
<CellDirective value={'Totals'} style={cellStyle}></CellDirective>
<CellDirective formula={'=SUM(B3:B11)'} ></CellDirective>
<CellDirective formula={'=SUM(C3:C11)'}></CellDirective>
<CellDirective formula={'=SUM(D3:D11)'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Number of Categories'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=COUNTA(A3:A11)'} index={3}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Average Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=AVERAGE(B3:B11)'} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Min Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MIN(B3:B11)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Max Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MAX(B3:B11)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<RangesDirective>
<RangeDirective dataSource={data} startCell={"A2"}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={150}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={140}></ColumnDirective>
<ColumnDirective width={150}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent> </div>
);
};
export default App;
const root = createRoot(document.getElementById('root'));
root.render(<App />);import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, RowsDirective, RowDirective, CellDirective, CellsDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { CellStyleModel } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';
function App() {
const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
const styles: CellStyleModel = { textAlign: 'center', fontWeight: 'bold', verticalAlign: 'middle', fontStyle: 'italic', fontSize: '15pt' };
const cellStyle: CellStyleModel = { fontStyle: 'italic', fontWeight: 'bold' };
const fontStyle: CellStyleModel = { fontWeight: 'bold', textAlign: 'right' };
// Custom function to calculate percentage between two cell values.
const calculatePercentage = (firstCell: string, secondCell: string): number => {
return Number(firstCell) / Number(secondCell);
};
// Custom function to calculate round down for values.
const roundDownHandler = (value: number, digit: number) => {
let multiplier: number = Math.pow(10, digit);
return Math.floor(value * multiplier) / multiplier;
}
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:F2');
spreadsheet.numberFormat('$#,##0', 'B3:D12');
spreadsheet.numberFormat('0%', 'E3:E12');
// Adding custom function for calculating the percentage between two cells.
spreadsheet.addCustomFunction(calculatePercentage, 'PERCENTAGE');
// Adding custom function for calculating round down for the value.
spreadsheet.addCustomFunction(roundDownHandler, 'ROUNDDOWN');
// Calculate percentage using custom added formula in E12 cell.
spreadsheet.updateCell({ formula: '=PERCENTAGE(C12,D12)' }, 'E12');
// Calculate round down for average values using custom added formula in F12 cell.
spreadsheet.updateCell({ formula: '=ROUNDDOWN(F11,1)' }, 'F12');
}
}, []);
return (<div>
<SpreadsheetComponent ref={spreadsheetRef} showSheetTabs={false} showRibbon={false}>
<SheetsDirective>
<SheetDirective>
<RowsDirective>
<RowDirective height={40} customHeight={true}>
<CellsDirective>
<CellDirective value={'Monthly Expense'} style={styles} colSpan={5}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective height={30}></RowDirective>
<RowDirective index={11}>
<CellsDirective>
<CellDirective value={'Totals'} style={cellStyle}></CellDirective>
<CellDirective formula={'=SUM(B3:B11)'} ></CellDirective>
<CellDirective formula={'=SUM(C3:C11)'}></CellDirective>
<CellDirective formula={'=SUM(D3:D11)'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Number of Categories'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=COUNTA(A3:A11)'} index={3}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Average Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=AVERAGE(B3:B11)'} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Min Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MIN(B3:B11)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Max Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MAX(B3:B11)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<RangesDirective>
<RangeDirective dataSource={data} startCell={"A2"}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={150}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={140}></ColumnDirective>
<ColumnDirective width={150}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent> </div>
);
};
export default App;
const root = createRoot(document.getElementById('root')!);
root.render(<App />);/**
* Formula data source
*/
export let data = [
{
'Category': 'Household Utilities',
'Monthly Spend': '=C3/12',
'Annual Spend': 3000,
'Last Year Spend': 3000,
'Percentage Change': '=C3/D3', // You can set the expression or formula as string
'Average Change': '=7.9/E3',
},
{
'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
'Average Change': '=7.9/E4',
},
{
'Category': 'Gasoline',
'Monthly Spend': '=C5/12',
'Annual Spend': 1500,
'Last Year Spend': 1200,
'Percentage Change': { formula: '=C5/D5' },
'Average Change': '=7.9/E5',
},
{
'Category': 'Clothes',
'Monthly Spend': '=C6/12',
'Annual Spend': 1200,
'Last Year Spend': 1000,
'Percentage Change': '=C6/D6',
'Average Change': '=7.9/E6',
},
{
'Category': 'Insurance',
'Monthly Spend': '=C7/12',
'Annual Spend': 1500,
'Last Year Spend': 1500,
'Percentage Change': '=C7/D7',
'Average Change': '=7.9/E7',
},
{
'Category': 'Taxes',
'Monthly Spend': '=C8/12',
'Annual Spend': 3500,
'Last Year Spend': 3500,
'Percentage Change': '=C8/D8',
'Average Change': '=7.9/E8',
},
{
'Category': 'Entertainment',
'Monthly Spend': '=C9/12',
'Annual Spend': 2000,
'Last Year Spend': 2250,
'Percentage Change': '=C9/D9',
'Average Change': '=7.9/E9',
},
{
'Category': 'Vacation',
'Monthly Spend': '=C10/12',
'Annual Spend': 1500,
'Last Year Spend': 2000,
'Percentage Change': '=C10/D10',
'Average Change': '=7.9/E10',
},
{
'Category': 'Miscellaneous',
'Monthly Spend': '=C11/12',
'Annual Spend': 1250,
'Last Year Spend': 1558,
'Percentage Change': '=C11/D11',
'Average Change': '=7.9/E11',
}
];/**
* 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
'Average Change': '=7.9/E3',
},
{
'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
'Average Change': '=7.9/E4',
},
{
'Category': 'Gasoline',
'Monthly Spend': '=C5/12',
'Annual Spend': 1500,
'Last Year Spend': 1200,
'Percentage Change': { formula: '=C5/D5' },
'Average Change': '=7.9/E5',
},
{
'Category': 'Clothes',
'Monthly Spend': '=C6/12',
'Annual Spend': 1200,
'Last Year Spend': 1000,
'Percentage Change': '=C6/D6',
'Average Change': '=7.9/E6',
},
{
'Category': 'Insurance',
'Monthly Spend': '=C7/12',
'Annual Spend': 1500,
'Last Year Spend': 1500,
'Percentage Change': '=C7/D7',
'Average Change': '=7.9/E7',
},
{
'Category': 'Taxes',
'Monthly Spend': '=C8/12',
'Annual Spend': 3500,
'Last Year Spend': 3500,
'Percentage Change': '=C8/D8',
'Average Change': '=7.9/E8',
},
{
'Category': 'Entertainment',
'Monthly Spend': '=C9/12',
'Annual Spend': 2000,
'Last Year Spend': 2250,
'Percentage Change': '=C9/D9',
'Average Change': '=7.9/E9',
},
{
'Category': 'Vacation',
'Monthly Spend': '=C10/12',
'Annual Spend': 1500,
'Last Year Spend': 2000,
'Percentage Change': '=C10/D10',
'Average Change': '=7.9/E10',
},
{
'Category': 'Miscellaneous',
'Monthly Spend': '=C11/12',
'Annual Spend': 1250,
'Last Year Spend': 1558,
'Percentage Change': '=C11/D11',
'Average Change': '=7.9/E11',
},
];To directly compute a formula or expression, use the computeExpression method. This method will work for both built-in and used-defined/custom formula.
The following code example shows how to use computeExpression method in the spreadsheet.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, RowsDirective, RowDirective, CellDirective, CellsDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';
function App() {
const spreadsheetRef = React.useRef(null);
const styles = { textAlign: 'center', fontWeight: 'bold', verticalAlign: 'middle', fontStyle: 'italic', fontSize: '15pt' };
const cellStyle = { fontStyle: 'italic', fontWeight: 'bold' };
const fontStyle = { fontWeight: 'bold', textAlign: 'right' };
// Custom function to calculate percentage between two cell values.
const calculatePercentage = (firstCell, secondCell) => {
return Number(firstCell) / Number(secondCell);
};
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
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 E11 cell.
spreadsheet.updateCell({ formula: '=PERCENTAGE(C11,D11)' }, 'E11');
// Calculate expressions using computeExpression in E10 cell.
spreadsheet.updateCell({ value: spreadsheet.computeExpression('C10/D10') }, 'E10');
// Calculate custom formula values using computeExpression in E12 cell.
spreadsheet.updateCell({ value: spreadsheet.computeExpression('=PERCENTAGE(C12,D12)') }, 'E12');
// Calculate SUM (built-in) formula values using computeExpression in D12 cell.
spreadsheet.updateCell({ value: spreadsheet.computeExpression('=SUM(D3:D11)') }, 'D12');
}
}, []);
return (<div>
<SpreadsheetComponent ref={spreadsheetRef} showSheetTabs={false} showRibbon={false}>
<SheetsDirective>
<SheetDirective>
<RowsDirective>
<RowDirective height={40} customHeight={true}>
<CellsDirective>
<CellDirective value={'Monthly Expense'} style={styles} colSpan={5}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective height={30}></RowDirective>
<RowDirective index={11}>
<CellsDirective>
<CellDirective value={'Totals'} style={cellStyle}></CellDirective>
<CellDirective formula={'=SUM(B3:B11)'} ></CellDirective>
<CellDirective formula={'=SUM(C3:C11)'}></CellDirective>
<CellDirective formula={'=SUM(D3:D11)'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Number of Categories'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=COUNTA(A3:A11)'} index={3}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Average Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=AVERAGE(B3:B11)'} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Min Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MIN(B3:B11)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Max Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MAX(B3:B11)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<RangesDirective>
<RangeDirective dataSource={data} startCell={"A2"}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={150}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent> </div>
);
};
export default App;
const root = createRoot(document.getElementById('root'));
root.render(<App />);import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, RowsDirective, RowDirective, CellDirective, CellsDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { CellStyleModel } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';
function App() {
const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
const styles: CellStyleModel = { textAlign: 'center', fontWeight: 'bold', verticalAlign: 'middle', fontStyle: 'italic', fontSize: '15pt' };
const cellStyle: CellStyleModel = { fontStyle: 'italic', fontWeight: 'bold' };
const fontStyle: CellStyleModel = { fontWeight: 'bold', textAlign: 'right' };
// Custom function to calculate percentage between two cell values.
const calculatePercentage = (firstCell: string, secondCell: string): number => {
return Number(firstCell) / Number(secondCell);
};
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
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 E11 cell.
spreadsheet.updateCell({ formula: '=PERCENTAGE(C11,D11)' }, 'E11');
// Calculate expressions using computeExpression in E10 cell.
spreadsheet.updateCell({ value: spreadsheet.computeExpression('C10/D10') as string }, 'E10');
// Calculate custom formula values using computeExpression in E12 cell.
spreadsheet.updateCell({ value: spreadsheet.computeExpression('=PERCENTAGE(C12,D12)') as string }, 'E12');
// Calculate SUM (built-in) formula values using computeExpression in D12 cell.
spreadsheet.updateCell({ value: spreadsheet.computeExpression('=SUM(D3:D11)') as string }, 'D12');
}
}, []);
return (<div>
<SpreadsheetComponent ref={spreadsheetRef} showSheetTabs={false} showRibbon={false}>
<SheetsDirective>
<SheetDirective>
<RowsDirective>
<RowDirective height={40} customHeight={true}>
<CellsDirective>
<CellDirective value={'Monthly Expense'} style={styles} colSpan={5}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective height={30}></RowDirective>
<RowDirective index={11}>
<CellsDirective>
<CellDirective value={'Totals'} style={cellStyle}></CellDirective>
<CellDirective formula={'=SUM(B3:B11)'} ></CellDirective>
<CellDirective formula={'=SUM(C3:C11)'}></CellDirective>
<CellDirective formula={'=SUM(D3:D11)'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Number of Categories'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=COUNTA(A3:A11)'} index={3}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Average Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=AVERAGE(B3:B11)'} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Min Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MIN(B3:B11)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Max Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MAX(B3:B11)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<RangesDirective>
<RangeDirective dataSource={data} startCell={"A2"}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={150}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent> </div>
);
};
export default App;
const root = createRoot(document.getElementById('root')!);
root.render(<App />);/**
* Formula data source
*/
export let data = [
{
'Category': 'Household Utilities',
'Monthly Spend': '=C3/12',
'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 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'
}
];