Formulas in ASP.NET MVC Spreadsheet
23 Sep 202624 minutes to read
Formulas are used to calculate data in a worksheet. A formula can reference cells in the same worksheet or in other worksheets.
Usage
You can set a formula for a cell in the following ways:
- Use the
formulaproperty of a cell to set a formula or expression during the initial rendering. - Set the formula or expression through data binding.
- You can set formula for a cell by
editing. - Using the
updateCellmethod, you can set or update the cell formula.
The following example demonstrates how to set a formula for a cell using the updateCell method:
spreadsheet.updateCell({
formula: '=SUM(A1:A5)'
}, 'B1');After running the example, verify that cell B1 displays the sum of the values in the range A1:A5.
Culture-Based Argument Separator
Previously, although you could import culture-based Excel files into the Spreadsheet control, the formulas wouldn’t calculate correctly. This was due to the absence of culture-based argument separators and support for culture-based formatted numeric values as arguments. However, starting from version 25.1.35, you can now import culture-based Excel files into the Spreadsheet component.
Before importing culture-based Excel files, ensure that the Spreadsheet control is rendered with the corresponding culture. Additionally, launch the import/export services with the same culture to ensure compatibility.
When loading spreadsheet data with culture-based formula argument separators using cell data binding, local/remote data, or JSON, ensure to set the listSeparator property value as the culture-based list separator from your end. Additionally, note that when importing an Excel file, the listSeparator property will be updated based on the culture of the launched import/export service.
In the example below, the Spreadsheet control is rendered with the German culture [de]. Additionally, you can find references on how to set the culture-based argument separator and culture-based formatted numeric value as arguments to the formulas.
@Html.EJS().Spreadsheet("spreadsheet").Locale("de").ListSeparator(";").ShowRibbon(false).ShowSheetTabs(false).Created("created").Sheets((sheet) =>
{
sheet.SelectedRange("E14").Ranges((ranges) =>
{
ranges.DataSource(@ViewBag.defaultData).Add();
}).Rows(row =>
{
row.Index(12).Cells(cell => {
cell.Index(3).Value("Subtotal:").Add();
cell.Formula("=SUBTOTAL(9;E2:E12)").Add();
}).Add();
row.Cells(cell => {
cell.Index(3).Value("Discount (8,5%):").Add();
cell.Formula("=PRODUCT(8,5;E13)/100").Add();
}).Add();
row.Cells(cell => {
cell.Index(3).Value("Total Amount:").Add();
cell.Formula("=E13-E14").Add();
}).Add();
}).Columns(column => {
column.Width(120).Add();
column.Width(180).Add();
column.Width(100).Add();
column.Width(120).Add();
column.Width(120).Add();
}).Add();
}).Render()
<script>
function loadCultureFiles(name) {
ej.base.setCulture(name);
ej.base.setCurrencyCode('EUR');
var files = ['ca-gregorian.json', 'currencies.json', 'numbers.json', 'timeZoneNames.json', 'numberingSystems.json'];
var loader = ej.base.loadCldr;
var loadCulture = function (prop) {
var val, ajax;
if (files[prop] === 'numberingSystems.json') {
ajax = new ej.base.Ajax(location.origin + '/Content/cldr-data/supplemental/' + files[prop], 'GET', false);
} else {
ajax = new ej.base.Ajax(location.origin + '/Content/cldr-data/main/' + name + '/' + files[prop], 'GET', false);
}
ajax.onSuccess = function (value) {
val = value;
};
ajax.send();
loader(JSON.parse(val));
};
for (var prop = 0; prop < files.length; prop++) {
loadCulture(prop);
}
}
loadCultureFiles('de');
function created() {
var spreadsheet = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
spreadsheet.cellFormat({ textAlign: 'center', fontWeight: 'bold' }, 'A1:E1');
spreadsheet.numberFormat(ej.spreadsheet.getFormatFromType('Currency'), 'D2:E12');
spreadsheet.numberFormat(ej.spreadsheet.getFormatFromType('Currency'), 'E13:E15');
}
</script>public ActionResult Index()
{
List<object> data = new List<object>()
{
new { ItemCode= "I231", ItemName= "Chinese Combo Noodle", Quantity= "2", Rate= "125", Amount= "=PRODUCT(C2;D2)" },
new { ItemCode= "I245", ItemName= "Chinese Combo Rice", Quantity= "3", Rate= "125", Amount= "=PRODUCT(C3;D3)" },
new { ItemCode= "I237", ItemName= "Amritsari Chola", Quantity= "2", Rate= "225", Amount= "=PRODUCT(C4;D4)" },
new { ItemCode= "I291", ItemName= "Asian Mixed Entree Platt", Quantity= "3", Rate= "165", Amount= "=PRODUCT(C5;D5)" },
new { ItemCode= "I268", ItemName= "Chinese Combo Chicken", Quantity= "3", Rate= "125", Amount= "=PRODUCT(C6;D6)" },
new { ItemCode= "I251", ItemName= "Chivas Regal", Quantity= "1", Rate= "325", Amount= "=PRODUCT(C7;D7)" },
new { ItemCode= "I256", ItemName= "Chicken Drumsticks", Quantity= "2", Rate= "180", Amount= "=PRODUCT(C8;D8)" },
new { ItemCode= "I232", ItemName= "Manchow Soup", Quantity= "2", Rate= "160", Amount= "=PRODUCT(C9;D9)" },
new { ItemCode= "I290", ItemName= "Schezuan Chicken", Quantity= "3", Rate= "180", Amount= "=PRODUCT(C10;D10)" },
new { ItemCode= "I229", ItemName= "Manchow Soup", Quantity= "2", Rate= "125", Amount= "=PRODUCT(C11;D11)" },
new { ItemCode= "I239", ItemName= "Jw Black Lable", Quantity= "2", Rate= "175", Amount= "=PRODUCT(C12;D12)" },
};
ViewBag.DefaultData = data;
return View();
}After running the sample, verify that formulas containing culture-specific argument separators and formatted numeric values are calculated correctly.
Create User Defined Functions / Custom Functions
The Spreadsheet includes a number of built-in formulas. For your convenience, a list of supported formulas can be found here.
You can define and use an unsupported formula, i.e. a user defined/custom formula, in the spreadsheet by using the addCustomFunction function. Meanwhile, remember that you should define a user defined/custom formula whose results should only return a single value. If a user-defined/custom formula returns an array, it will be time-consuming to update adjacent cell values.
To create and use a custom function:
- Define a function that accepts the required arguments and returns a single value.
- Register the function using the
addCustomFunctionmethod. - Use the registered function in a cell formula.
- Verify that the target cell displays the expected result.
The following code example shows an unsupported formula in the spreadsheet.
@Html.EJS().Spreadsheet("spreadsheet").ShowSheetTabs(false).Created("created").ShowRibbon(false).Sheets(sheet =>
{
sheet.Ranges(ranges =>
{
ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).StartCell("A2").Add();
}).Rows(row =>
{
row.Height(40).CustomHeight(true).Cells(cell =>
{
cell.ColSpan(5).Value("Monthly Expense").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, VerticalAlign = VerticalAlign.Middle, TextAlign=TextAlign.Center, FontSize="15pt", FontStyle= FontStyle.Italic }).Add();
}).Add();
row.Height(30).Add();
row.Index(11).Cells(cell =>
{
cell.Value("Totals").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, FontStyle= FontStyle.Italic }).Add();
cell.Formula("=SUM(B3:B11)").Add();
cell.Formula("=SUM(C3:C11)").Add();
cell.Formula("=SUM(D3:D11)").Add();
}).Add();
row.Cells(cell =>
{
cell.Index(1).Value("Number of Categories").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign= TextAlign.Right }).Add();
cell.Formula("=COUNTA(A3:A11)").Index(3).Add();
}).Add();
row.Cells(cell =>
{
cell.Index(1).Value("Average Spend").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign= TextAlign.Right }).Add();
cell.Formula("=AVERAGE(B3:B11)").Index(3).Format("$#,##0").Add();
}).Add();
row.Cells(cell =>
{
cell.Index(1).Value("Min Spend").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign= TextAlign.Right }).Add();
cell.Formula("=MIN(B3:B11)").Index(3).Format("$#,##0").Add();
}).Add();
row.Cells(cell =>
{
cell.Index(1).Value("Max Spend").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign= TextAlign.Right }).Add();
cell.Formula("=MAX(B3:B11)").Index(3).Format("$#,##0").Add();
}).Add();
}).Columns(column =>
{
column.Width(150).Add();
column.Width(120).Add();
column.Width(120).Add();
column.Width(120).Add();
column.Width(140).Add();
column.Width(150).Add();
}).Add();
}).Render()
<script>
function created() {
this.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:F2');
this.numberFormat('$#,##0', 'B3:D12');
this.numberFormat('0%', 'E3:E12');
// Adding custom function for calculating the percentage between two cells.
this.addCustomFunction(calculatePercentage, 'PERCENTAGE');
// Adding custom function for calculating round down for the value.
this.addCustomFunction(roundDownHandler, 'ROUNDDOWN');
// Calculate percentage using custom added formula in E12 cell.
this.updateCell({ formula: '=PERCENTAGE(C12,D12)' }, 'E12');
// Calculate round down for average values using custom added formula in F12 cell.
this.updateCell({ formula: '=ROUNDDOWN(F11,1)' }, 'F12');
}
// Custom function to calculate percentage between two cell values.
function calculatePercentage(firstCell,secondCell) {
return (firstCell) / (secondCell);
}
// Custom function to calculate round down for values.
function roundDownHandler(value, digit) {
var multiplier = Math.pow(10, digit);
return Math.floor(value * multiplier) / multiplier;
}
</script>public ActionResult Index()
{
List<object> data = new List<object>()
{
new { Category= "Household Utilities", MonthlySpend= "=C3/12", AnnualSpend= "3000", LastYearSpend= "3000", PercentageChange= "=C3/D3", AverageChange= "=7.9/E3"},
new { Category= "Food", MonthlySpend= "=C4/12", AnnualSpend= "2500", LastYearSpend= "2250", PercentageChange= "=C4/D4", AverageChange= "=7.9/E4"},
new { Category= "Gasoline", MonthlySpend= "=C5/12", AnnualSpend= "1500", LastYearSpend= "1200", PercentageChange= "=C5/D5", AverageChange= "=7.9/E5"},
new { Category= "Clothes", MonthlySpend= "=C6/12", AnnualSpend= "1200", LastYearSpend= "1000", PercentageChange= "=C6/D6", AverageChange= "=7.9/E6"},
new { Category= "Insurance", MonthlySpend= "=C7/12", AnnualSpend= "1500", LastYearSpend= "1500", PercentageChange= "=C7/D7", AverageChange= "=7.9/E7"},
new { Category= "Taxes", MonthlySpend= "=C8/12", AnnualSpend= "3500", LastYearSpend= "3500", PercentageChange= "=C8/D8", AverageChange= "=7.9/E8"},
new { Category= "Entertainment", MonthlySpend= "=C9/12", AnnualSpend= "2000", LastYearSpend= "2250", PercentageChange= "=C9/D9", AverageChange= "=7.9/E9"},
new { Category= "Vacation", MonthlySpend= "=C10/12", AnnualSpend= "1500", LastYearSpend= "2000", PercentageChange= "=C10/D10", AverageChange= "=7.9/E10"},
new { Category= "Miscellaneous", MonthlySpend= "=C11/12", AnnualSpend= "1250", LastYearSpend= "1558", PercentageChange= "=C11/D11", AverageChange= "=7.9/E11"},
};
ViewBag.DefaultData = data;
return View();
}After running the sample, verify that the registered custom function returns the expected value in the target cell.
Compute a formula or expression
Use the computeExpression method to directly evaluate a built-in or custom formula or expression. This method works with both built-in and custom formulas.
The following code example shows how to use computeExpression method in the spreadsheet.
@Html.EJS().Spreadsheet("spreadsheet").ShowSheetTabs(false).Created("created").ShowRibbon(false).Sheets(sheet =>
{
sheet.Ranges(ranges =>
{
ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).StartCell("A2").Add();
}).Rows(row =>
{
row.Height(40).CustomHeight(true).Cells(cell =>
{
cell.ColSpan(5).Value("Monthly Expense").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, VerticalAlign = VerticalAlign.Middle, TextAlign=TextAlign.Center, FontSize="15pt", FontStyle= FontStyle.Italic }).Add();
}).Add();
row.Height(30).Add();
row.Index(11).Cells(cell =>
{
cell.Value("Totals").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, FontStyle= FontStyle.Italic }).Add();
cell.Formula("=SUM(B3:B11)").Add();
cell.Formula("=SUM(C3:C11)").Add();
cell.Formula("=SUM(D3:D11)").Add();
}).Add();
row.Cells(cell =>
{
cell.Index(1).Value("Number of Categories").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign= TextAlign.Right }).Add();
cell.Formula("=COUNTA(A3:A11)").Index(3).Add();
}).Add();
row.Cells(cell =>
{
cell.Index(1).Value("Average Spend").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign= TextAlign.Right }).Add();
cell.Formula("=AVERAGE(B3:B11)").Index(3).Format("$#,##0").Add();
}).Add();
row.Cells(cell =>
{
cell.Index(1).Value("Min Spend").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign= TextAlign.Right }).Add();
cell.Formula("=MIN(B3:B11)").Index(3).Format("$#,##0").Add();
}).Add();
row.Cells(cell =>
{
cell.Index(1).Value("Max Spend").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign= TextAlign.Right }).Add();
cell.Formula("=MAX(B3:B11)").Index(3).Format("$#,##0").Add();
}).Add();
}).Columns(column =>
{
column.Width(150).Add();
column.Width(120).Add();
column.Width(120).Add();
column.Width(120).Add();
column.Width(140).Add();
column.Width(150).Add();
}).Add();
}).Render()
<script>
function created() {
this.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:E2');
this.numberFormat('$#,##0', 'B3:D12');
this.numberFormat('0%', 'E3:E12');
// Adding custom function for calculating the percentage between two cells.
this.addCustomFunction(calculatePercentage, 'PERCENTAGE');
// Calculate percentage using custom added formula in E11 cell.
this.updateCell({ formula: '=PERCENTAGE(C11,D11)' }, 'E11');
// Calculate expressions using computeExpression in E10 cell.
this.updateCell({ value: this.computeExpression('C10/D10') },'E10');
// Calculate custom formula values using computeExpression in E12 cell.
this.updateCell({ value: this.computeExpression('=PERCENTAGE(C12,D12)'), }, 'E12');
// Calculate SUM (built-in) formula values using computeExpression in D12 cell.
this.updateCell({ value: this.computeExpression('=SUM(D3:D11)') }, 'D12');
}
// Custom function to calculate percentage between two cell values.
function calculatePercentage(firstCell,secondCell) {
return (firstCell) / (secondCell);
}
</script>public ActionResult Index()
{
List<object> data = new List<object>()
{
new { Category= "Household Utilities", MonthlySpend= "=C3/12", AnnualSpend= "3000", LastYearSpend= "3000", PercentageChange= "=C3/D3"},
new { Category= "Food", MonthlySpend= "=C4/12", AnnualSpend= "2500", LastYearSpend= "2250", PercentageChange= "=C4/D4"},
new { Category= "Gasoline", MonthlySpend= "=C5/12", AnnualSpend= "1500", LastYearSpend= "1200", PercentageChange= "=C5/D5"},
new { Category= "Clothes", MonthlySpend= "=C6/12", AnnualSpend= "1200", LastYearSpend= "1000", PercentageChange= "=C6/D6"},
new { Category= "Insurance", MonthlySpend= "=C7/12", AnnualSpend= "1500", LastYearSpend= "1500", PercentageChange= "=C7/D7"},
new { Category= "Taxes", MonthlySpend= "=C8/12", AnnualSpend= "3500", LastYearSpend= "3500", PercentageChange= "=C8/D8"},
new { Category= "Entertainment", MonthlySpend= "=C9/12", AnnualSpend= "2000", LastYearSpend= "2250", PercentageChange= "=C9/D9"},
new { Category= "Vacation", MonthlySpend= "=C10/12", AnnualSpend= "1500", LastYearSpend= "2000", PercentageChange= "=C10/D10"},
new { Category= "Miscellaneous", MonthlySpend= "=C11/12", AnnualSpend= "1250", LastYearSpend= "1558", PercentageChange= "=C11/D11"},
};
ViewBag.DefaultData = data;
return View();
}After running the sample, verify that the formula or expression is evaluated and returns the expected result.
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
definedNamescollection, you can add multiple named ranges at initial load. - Use the
addDefinedNamemethod to add a named range dynamically. - You can remove an added named range dynamically using the
removeDefinedNamemethod. - 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.
@Html.EJS().Spreadsheet("spreadsheet").ShowSheetTabs(false).BeforeDataBound("beforeDataBound").Created("created").ShowRibbon(false).Sheets(sheet =>
{
sheet.Name("Budget Details").Ranges(ranges =>
{
ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).StartCell("A2").Add();
}).Rows(row =>
{
row.Height(40).CustomHeight(true).Cells(cell =>
{
cell.ColSpan(5).Value("Monthly Expense").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, VerticalAlign = VerticalAlign.Middle, TextAlign = TextAlign.Center, FontSize = "15pt", FontStyle = FontStyle.Italic }).Add();
}).Add();
row.Height(30).Add();
row.Index(11).Cells(cell =>
{
cell.Value("Totals").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, FontStyle = FontStyle.Italic }).Add();
cell.Formula("=SUM(MonthlySpendings)").Add();
cell.Formula("=SUM(AnnualSpendings)").Add();
cell.Formula("=SUM(LastYearSpendings)").Add();
cell.Formula("=C12/D12").Add();
}).Add();
row.Cells(cell =>
{
cell.Index(1).Value("Number of Categories").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Right }).Add();
cell.Formula("=COUNTA(=C12/D12)").Index(3).Add();
}).Add();
row.Cells(cell =>
{
cell.Index(1).Value("Average Spend").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Right }).Add();
cell.Formula("=AVERAGE(MonthlySpendings)").Index(3).Format("$#,##0").Add();
}).Add();
row.Cells(cell =>
{
cell.Index(1).Value("Min Spend").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Right }).Add();
cell.Formula("=MIN(MonthlySpendings)").Index(3).Format("$#,##0").Add();
}).Add();
row.Cells(cell =>
{
cell.Index(1).Value("Max Spend").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Right }).Add();
cell.Formula("=MAX(MonthlySpendings)").Index(3).Format("$#,##0").Add();
}).Add();
}).Columns(column =>
{
column.Width(150).Add();
column.Width(120).Add();
column.Width(120).Add();
column.Width(120).Add();
column.Width(120).Add();
}).Add();
}).DefinedNames(definedName => {
definedName.Name("Categories").RefersTo("=Budget Details!A3:A11").Add();
definedName.Name("MonthlySpendings").RefersTo("=Budget Details!B3:B11").Add();
definedName.Name("AnnualSpendings").RefersTo("=Budget Details!C3:C11").Add();
}).Render()
<script>
function created() {
// Removing the unwanted `PercentageChange` named range
this.removeDefinedName('PercentageChange', '');
this.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:E2');
this.numberFormat('$#,##0', 'B3:D12');
this.numberFormat('0%', 'E3:E12');
}
function beforeDataBound() {
// Adding name dynamically for `last year spending` and `percentage change` ranges.
this.addDefinedName({ name: 'LastYearSpendings', refersTo: '=D3:D11' });
this.addDefinedName({ name: 'PercentageChange', refersTo: '=E3:E11' });
}
</script>public ActionResult Index()
{
List<object> data = new List<object>()
{
new { Category= "Household Utilities", MonthlySpend= "=C3/12", AnnualSpend= "3000", LastYearSpend= "3000", PercentageChange= "=C3/D3"},
new { Category= "Food", MonthlySpend= "=C4/12", AnnualSpend= "2500", LastYearSpend= "2250", PercentageChange= "=C4/D4"},
new { Category= "Gasoline", MonthlySpend= "=C5/12", AnnualSpend= "1500", LastYearSpend= "1200", PercentageChange= "=C5/D5"},
new { Category= "Clothes", MonthlySpend= "=C6/12", AnnualSpend= "1200", LastYearSpend= "1000", PercentageChange= "=C6/D6"},
new { Category= "Insurance", MonthlySpend= "=C7/12", AnnualSpend= "1500", LastYearSpend= "1500", PercentageChange= "=C7/D7"},
new { Category= "Taxes", MonthlySpend= "=C8/12", AnnualSpend= "3500", LastYearSpend= "3500", PercentageChange= "=C8/D8"},
new { Category= "Entertainment", MonthlySpend= "=C9/12", AnnualSpend= "2000", LastYearSpend= "2250", PercentageChange= "=C9/D9"},
new { Category= "Vacation", MonthlySpend= "=C10/12", AnnualSpend= "1500", LastYearSpend= "2000", PercentageChange= "=C10/D10"},
new { Category= "Miscellaneous", MonthlySpend= "=C11/12", AnnualSpend= "1250", LastYearSpend= "1558", PercentageChange= "=C11/D11"},
};
ViewBag.DefaultData = data;
return View();
}Calculation Mode
The Spreadsheet provides a Calculation Mode feature like the calculation options in online Excel. This feature allows you to control when and how formulas are recalculated in the spreadsheet. The available modes are:
-
Automatic: Formulas are recalculated instantly whenever a change occurs in the dependent cells. -
Manual: Formulas are recalculated only when triggered explicitly by the user using options likeCalculate SheetorCalculate Workbook.
You can configure the calculate mode using the calculationMode property of the Spreadsheet. These modes offer flexibility to balance real-time updates and performance optimization.
Automatic Mode
In Automatic Mode, formulas are recalculated instantly whenever a dependent cell is modified. This mode is perfect for scenarios where real-time updates are essential, ensuring that users see the latest results without additional actions.
For example, consider a spreadsheet where cell C1 contains the formula =A1+B1. When the value in A1 or B1 changes, C1 updates immediately without requiring any user intervention. You can enable this mode by setting the calculationMode property to Automatic.
The following code example demonstrates how to set the Automatic calculation mode in a Spreadsheet.
@Html.EJS().Spreadsheet("spreadsheet").CalculationMode(CalculationMode.Automatic).Created("created").Sheets(sheet =>
{
sheet.Name("Product Details").Ranges(ranges =>
{
ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).StartCell("A1").Add();
}).Columns(column =>
{
column.Width(130).Add();
column.Width(92).Add();
column.Width(96).Add();
}).Add();
}).Render()
<script>
function created() {
this.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
}
</script>public ActionResult Index()
{
List<object> data = new List<object>()
{
new { ItemName= "Casual Shoes", Date= "2/14/2024", Time= "11:34:32 AM", Quantity= 10, Price= 20, Amount= "=PRODUCT(D2=E2)", Discount= "2%", Profit= "=PRODUCT(G2=F2)" },
new { ItemName= "Sports Shoes", Date= "6/11/2024", Time= "05:56:32 AM", Quantity= 20, Price= 30, Amount= "=PRODUCT(D3=E3)", Discount= "5%", Profit= "=PRODUCT(G3=F3)" },
new { ItemName= "Formal Shoes", Date= "7/27/2024", Time= "03:32:44 AM", Quantity= 20, Price= 15, Amount= "=PRODUCT(D4=E4)", Discount= "7.5%", Profit= "=PRODUCT(G4=F4)" },
new { ItemName= "Sandals & Floaters", Date= "11/21/2024", Time= "06:23:54 PM", Quantity= 15, Price= 20.45, Amount= "=PRODUCT(D5=E5)", Discount= "11%", Profit= "=PRODUCT(G5=F5)" },
new { ItemName= "Flip- Flops & Slippers", Date= "6/23/2024", Time= "12:43:59 AM", Quantity= 30, Price= 10.67, Amount= "=PRODUCT(D6=E6)", Discount= "10%", Profit= "=PRODUCT(G6=F6)" },
new { ItemName= "Sneakers", Date= "7/22/2024", Time= "10:55:53 AM", Quantity= 40, Price= 20, Amount= "=PRODUCT(D7=E7)", Discount= "13.2%", Profit= "=PRODUCT(G7=F7)" },
new { ItemName= "Running Shoes", Date= "2/4/2024", Time= "03:44:34 AM", Quantity= 20, Price= 10.5, Amount= "=PRODUCT(D8=E8)", Discount= "3%", Profit= "=PRODUCT(G8=F8)" },
new { ItemName= "Loafers", Date= "11/30/2024", Time= "03:12:52 AM", Quantity= 31, Price= 10, Amount= "=PRODUCT(D9=E9)", Discount= "6.67%", Profit= "=PRODUCT(G9=F9)" },
new { ItemName= "Cricket Shoes", Date= "7/9/2024", Time= "11:32:14 PM", Quantity= 41, Price= 30, Amount= "=PRODUCT(D10=E10)", Discount= "12.5%", Profit= "=PRODUCT(G10=F10)" },
new { ItemName= "T-Shirts", Date= "10/31/2024", Time= "12:01:44 AM", Quantity= 50, Price= 10.75, Amount= "=PRODUCT(D11=E11)", Discount= "9%", Profit= "=PRODUCT(G11=F11)" }
};
ViewBag.DefaultData = data;
return View();
}After running the sample, modify a dependent cell and verify that the formula result is recalculated automatically.
Manual Mode
In Manual Mode, formulas are not recalculated automatically when cell values are modified. Instead, recalculations must be triggered explicitly. This mode is ideal for scenarios where performance optimization is a priority, such as working with large datasets or computationally intensive formulas.
For example, imagine a spreadsheet where cell C1 contains the formula =A1+B1. When the value in A1 or B1 changes, the value in C1 will not update automatically. Instead, the recalculation must be initiated manually using either the Calculate Sheet or Calculate Workbook option. To manually initiate recalculation, use one of the following options:
-
Calculate Sheet: Recalculates formulas for the active sheet only. -
Calculate Workbook: Recalculates formulas across all sheets in the workbook.
The following code example demonstrates how to set the Manual calculation mode in a Spreadsheet.
@Html.EJS().Spreadsheet("spreadsheet").CalculationMode(CalculationMode.Manual).Created("created").Sheets(sheet =>
{
sheet.Name("Product Details").Ranges(ranges =>
{
ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).StartCell("A1").Add();
}).Columns(column =>
{
column.Width(130).Add();
column.Width(92).Add();
column.Width(96).Add();
}).Add();
}).Render()
<script>
function created() {
this.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
}
</script>public ActionResult Index()
{
List<object> data = new List<object>()
{
new { ItemName= "Casual Shoes", Date= "2/14/2024", Time= "11:34:32 AM", Quantity= 10, Price= 20, Amount= "=PRODUCT(D2=E2)", Discount= "2%", Profit= "=PRODUCT(G2=F2)" },
new { ItemName= "Sports Shoes", Date= "6/11/2024", Time= "05:56:32 AM", Quantity= 20, Price= 30, Amount= "=PRODUCT(D3=E3)", Discount= "5%", Profit= "=PRODUCT(G3=F3)" },
new { ItemName= "Formal Shoes", Date= "7/27/2024", Time= "03:32:44 AM", Quantity= 20, Price= 15, Amount= "=PRODUCT(D4=E4)", Discount= "7.5%", Profit= "=PRODUCT(G4=F4)" },
new { ItemName= "Sandals & Floaters", Date= "11/21/2024", Time= "06:23:54 PM", Quantity= 15, Price= 20.45, Amount= "=PRODUCT(D5=E5)", Discount= "11%", Profit= "=PRODUCT(G5=F5)" },
new { ItemName= "Flip- Flops & Slippers", Date= "6/23/2024", Time= "12:43:59 AM", Quantity= 30, Price= 10.67, Amount= "=PRODUCT(D6=E6)", Discount= "10%", Profit= "=PRODUCT(G6=F6)" },
new { ItemName= "Sneakers", Date= "7/22/2024", Time= "10:55:53 AM", Quantity= 40, Price= 20, Amount= "=PRODUCT(D7=E7)", Discount= "13.2%", Profit= "=PRODUCT(G7=F7)" },
new { ItemName= "Running Shoes", Date= "2/4/2024", Time= "03:44:34 AM", Quantity= 20, Price= 10.5, Amount= "=PRODUCT(D8=E8)", Discount= "3%", Profit= "=PRODUCT(G8=F8)" },
new { ItemName= "Loafers", Date= "11/30/2024", Time= "03:12:52 AM", Quantity= 31, Price= 10, Amount= "=PRODUCT(D9=E9)", Discount= "6.67%", Profit= "=PRODUCT(G9=F9)" },
new { ItemName= "Cricket Shoes", Date= "7/9/2024", Time= "11:32:14 PM", Quantity= 41, Price= 30, Amount= "=PRODUCT(D10=E10)", Discount= "12.5%", Profit= "=PRODUCT(G10=F10)" },
new { ItemName= "T-Shirts", Date= "10/31/2024", Time= "12:01:44 AM", Quantity= 50, Price= 10.75, Amount= "=PRODUCT(D11=E11)", Discount= "9%", Profit= "=PRODUCT(G11=F11)" }
};
ViewBag.DefaultData = data;
return View();
}After running the sample, modify a dependent cell and verify that the formula result remains unchanged until Calculate Sheet or Calculate Workbook is selected.
Built-in Formulas and Functions
The Spreadsheet component supports a comprehensive set of built-in formulas organized by category. These formulas can be used to perform calculations, analyze data, manipulate text, process dates and times, evaluate logical conditions, retrieve information, and work with financial, engineering, and database data. The formulas supported in the Spreadsheet component are listed below by category.
Math & Trigonometry
| Formula | Description |
|---|---|
| ABS | Returns the value of a number without its sign. |
| ACOS | Returns the arccosine of a number. |
| ACOSH | Returns the inverse hyperbolic cosine of a number. |
| ASIN | Returns the arcsine of a number. |
| ASINH | Returns the inverse hyperbolic sine of a number. |
| ATAN | Returns the arctangent of a number. |
| ATAN2 | Returns the arctangent from x- and y-coordinates. |
| ATANH | Returns the inverse hyperbolic tangent of a number. |
| CEILING | Rounds a number up to the nearest multiple of a given factor. |
| COMBIN | Returns the number of combinations for a given number of objects. |
| COS | Returns the cosine of an angle. |
| COSH | Returns the hyperbolic cosine of a number. |
| DECIMAL | Converts a text representation of a number in a given base into a decimal number. |
| DEGREES | Converts radians to degrees. |
| ECMA.CEILING | Rounds a number up, away from zero, to the nearest multiple of significance. |
| EVEN | Rounds a positive number up and negative number down to the nearest even integer. |
| EXP | Returns e raised to the power of the given number. |
| FACT | Returns the factorial of a number. |
| FACTDOUBLE | Returns the double factorial of a number. |
| FLOOR | Rounds a number down to the nearest multiple of a given factor. |
| GCD | Returns the greatest common divisor. |
| INT | Rounds a number down to the nearest integer. |
| ISO.CEILING | Rounds a number up to the nearest integer or multiple of significance. |
| LCM | Returns the least common multiple. |
| LN | Returns the natural logarithm of a number. |
| LOG | Returns the logarithm of a number to the base that you specify. |
| LOG10 | Returns the base-10 logarithm of a number. |
| MDETERM | Returns the matrix determinant of an array. |
| MOD | Returns a remainder after a number is divided by divisor. |
| MROUND | Returns a number rounded to the desired multiple. |
| MULTINOMIAL | Returns the multinomial of a set of numbers. |
| ODD | Rounds a positive number up and negative number down to the nearest odd integer. |
| PERMUT | Returns the number of permutations for a given number of objects. |
| PI | Returns the value of pi. |
| POWER | Returns the result of a number raised to power. |
| PRODUCT | Multiplies a series of numbers and/or cells. |
| QUOTIENT | Returns the integer portion of a division. |
| RADIANS | Converts degrees into radians. |
| RAND | Returns a random number between 0 and 1. |
| RANDBETWEEN | Returns a random integer based on specified values. |
| ROMAN | Converts an Arabic numeral to Roman, as text. |
| ROUND | Rounds a number to the specified number of digits. |
| ROUNDDOWN | Rounds a number down, toward zero. |
| ROUNDUP | Rounds a number up, away from zero. |
| SERIESSUM | Returns the sum of a power series. |
| SIGN | Returns the sign of a number. |
| SIN | Returns the sine of an angle. |
| SINH | Returns the hyperbolic sine of a number. |
| SQRT | Returns the square root of a positive number. |
| SQRTPI | Returns the square root of a number multiplied by pi. |
| SUMSQ | Returns the sum of the squares of the arguments. |
| SUMX2MY2 | Returns the sum of the difference of squares of corresponding values in two arrays. |
| SUMX2PY2 | Returns the sum of the sum of squares of corresponding values in two arrays. |
| SUMXMY2 | Returns the sum of squares of differences of corresponding values in two arrays. |
| TAN | Returns the tangent of an angle. |
| TANH | Returns the hyperbolic tangent of a number. |
| TRUNC | Truncates a supplied number to a specified number of decimal places. |
Statistical & Aggregate
| Formula | Description |
|---|---|
| AVEDEV | Returns the average of the absolute deviations of data points from their mean. |
| AVERAGE | Calculates average for the series of numbers and/or cells excluding text. |
| AVERAGEA | Calculates the average for the cells evaluating TRUE as 1, text and FALSE as 0. |
| AVERAGEIF | Calculates the average of cells that meet a specified condition. |
| AVERAGEIFS | Calculates average for cells based on multiple specified conditions. |
| BETADIST | Returns the beta cumulative distribution function. |
| BETAINV | Returns the inverse of the beta cumulative distribution function. |
| BINOMDIST | Returns the individual term binomial distribution probability. |
| CHIDIST | Returns the one-tailed probability of the chi-squared distribution. |
| CHIINV | Returns the inverse of the one-tailed probability of the chi-squared distribution. |
| CHITEST | Returns the test for independence. |
| CONFIDENCE | Returns the confidence interval for a population mean. |
| CORREL | Returns the correlation coefficient between two data sets. |
| COUNT | Counts the cells that contain numeric values in a range. |
| COUNTA | Counts the cells that contain values in a range. |
| COUNTBLANK | Returns the number of empty cells in a specified range of cells. |
| COUNTIF | Counts the cells based on a specified condition. |
| COUNTIFS | Counts the cells based on multiple specified conditions. |
| COVAR | Returns covariance, the average of the products of paired deviations. |
| CRITBINOM | Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value. |
| DEVSQ | Returns the sum of squares of deviations. |
| EXPONDIST | Returns the exponential distribution. |
| FDIST | Returns the F probability distribution. |
| FINV | Returns the inverse of the F probability distribution. |
| FISHER | Returns the Fisher transformation. |
| FISHERINV | Returns the inverse of the Fisher transformation. |
| FORECAST | Returns a value along a linear trend. |
| FTEST | Returns the result of an F-test. |
| GAMMADIST | Returns the gamma distribution. |
| GAMMAINV | Returns the inverse of the gamma cumulative distribution. |
| GAMMALN | Returns the natural logarithm of the gamma function. |
| GEOMEAN | Returns the geometric mean of a given array or range of positive data. |
| HARMEAN | Returns the harmonic mean. |
| HYPGEOMDIST | Returns the hypergeometric distribution. |
| INTERCEPT | Calculates the point of the Y-intercept line via linear regression. |
| KURT | Returns the kurtosis of a data set. |
| LARGE | Returns the k-th largest value in a given array. |
| LOGINV | Returns the inverse of the log-normal cumulative distribution. |
| LOGNORMDIST | Returns the cumulative log-normal distribution. |
| MAX | Returns the largest number of the given arguments. |
| MAXA | Returns the maximum value in a list of arguments, including text and logicals. |
| MAXIFS | Returns the maximum value among cells specified by criteria. |
| MEDIAN | Returns the median of the given set of numbers. |
| MIN | Returns the smallest number of the given arguments. |
| MINA | Returns the minimum value in a list of arguments, including text and logicals. |
| MINIFS | Returns the minimum value among cells specified by criteria. |
| MODE | Returns the most common value in a data set. |
| NEGBINOMDIST | Returns the negative binomial distribution. |
| NORMDIST | Returns the normal cumulative distribution. |
| NORMINV | Returns the inverse of the normal cumulative distribution. |
| NORMSDIST | Returns the standard normal cumulative distribution. |
| NORMSINV | Returns the inverse of the standard normal cumulative distribution. |
| PEARSON | Returns the Pearson product moment correlation coefficient. |
| PERCENTILE | Returns the k-th percentile of values in a range. |
| PERCENTRANK | Returns the percentage rank of a value in a data set. |
| POISSON | Returns the Poisson distribution. |
| PROB | Returns the probability that values in a range are between two limits. |
| QUARTILE | Returns the quartile of a data set. |
| RANK | Returns the rank of a number in a list of numbers. |
| RSQ | Returns the square of the Pearson product moment correlation coefficient based on data points. |
| SKEW | Returns the skewness of a distribution. |
| SLOPE | Returns the slope of the line from linear regression of the data points. |
| SMALL | Returns the k-th smallest value in a given array. |
| STANDARDIZE | Returns a normalized value from a distribution. |
| STDEV | Estimates standard deviation based on a sample. |
| STDEVA | Estimates standard deviation based on a sample, including text and logicals. |
| STDEVP | Calculates standard deviation based on the entire population. |
| STDEVPA | Calculates standard deviation based on the entire population, including text and logicals. |
| STEYX | Returns the standard error of the predicted y-value for each x in the regression. |
| SUBTOTAL | Returns subtotal for a range using the given function number. |
| SUM | Adds a series of numbers and/or cells. |
| SUMIF | Adds the cells based on a specified condition. |
| SUMIFS | Adds the cells based on multiple specified conditions. |
| SUMPRODUCT | Returns the sum of the products of corresponding arrays in given arrays. |
| TDIST | Returns the Student t-distribution. |
| TINV | Returns the inverse of the Student t-distribution. |
| TRIMMEAN | Returns the mean of the interior of a data set. |
| TTEST | Returns the probability associated with a Student t-test. |
| VAR | Estimates variance based on a sample. |
| VARA | Estimates variance based on a sample, including text and logicals. |
| VARP | Calculates variance based on the entire population. |
| VARPA | Calculates variance based on the entire population, including text and logicals. |
| WEIBULL | Returns the Weibull distribution. |
| ZTEST | Returns the one-tailed probability-value of a z-test. |
Logical
| Formula | Description |
|---|---|
| AND | Returns TRUE if all the arguments are TRUE, otherwise returns FALSE. |
| FALSE | Returns the logical value FALSE. |
| IF | Returns value based on the given expression. |
| IFERROR | Returns value if no error found; else returns specified value. |
| IFNA | Returns the value you specify if the expression resolves to #N/A; otherwise returns the result of the expression. |
| IFS | Returns value based on multiple given expressions. |
| NOT | Returns the inverse of a given logical expression. |
| OR | Returns TRUE if any of the arguments are TRUE, otherwise returns FALSE. |
| SWITCH | Evaluates an expression against a list of values and returns the result corresponding to the first matching value. |
| TRUE | Returns the logical value TRUE. |
| XOR | Returns a logical exclusive OR of all arguments. |
Text
| Formula | Description |
|---|---|
| ARRAYTOTEXT | Returns the text representation of an array. |
| ASC | Changes full-width characters to half-width. |
| CHAR | Returns the character from the specified number. |
| CLEAN | Removes all non-printable characters from text. |
| CODE | Returns the numeric code for the first character in a given string. |
| CONCAT | Concatenates a list or a range of text strings. |
| CONCATENATE | Combines two or more strings together. |
| DOLLAR | Converts the number to currency formatted text. |
| EXACT | Checks whether two text strings are exactly the same and returns TRUE or FALSE. |
| FIND | Returns the position of a string within another string (case sensitive). |
| FINDB | Finds text within another string (byte-count alias of FIND). |
| FIXED | Formats a number as text with a fixed number of decimals. |
| LEFT | Returns the leftmost characters from a text value. |
| LEFTB | Returns the leftmost characters from a text value (byte count alias). |
| LEN | Returns the number of characters in a given string. |
| LENB | Returns the length of a text string (byte-count alias of LEN). |
| LOWER | Converts text to lowercase. |
| MID | Returns a specific number of characters from a text string starting at the position you specify. |
| MIDB | Returns characters from a text string (byte count alias). |
| PROPER | Converts text to proper case (first letter capitalized). |
| REPLACE | Replaces characters within text. |
| REPLACEB | Replaces characters within text (byte count alias). |
| REPT | Repeats text a given number of times. |
| RIGHT | Returns the rightmost characters from a text value. |
| RIGHTB | Returns the rightmost characters from a text value (byte count alias). |
| SEARCH | Finds one text value within another (not case-sensitive). |
| SEARCHB | Finds one text value within another (byte count alias). |
| SUBSTITUTE | Substitutes new text for old text in a text string. |
| T | Checks whether a value is text or not and returns the text. |
| TEXT | Converts the supplied value into text by using the user-specified format. |
| TEXTBEFORE | Returns text before a given character or string. |
| TEXTJOIN | Combines text from multiple ranges/strings with a delimiter. |
| TRIM | Removes spaces from text except for single spaces between words. |
| UPPER | Converts text to uppercase. |
| USDOLLAR | Converts a number to text using currency format (US dollar). |
| VALUE | Converts a text argument to a number. |
| VALUETOTEXT | Returns text from any specified value. |
Date & Time
| Formula | Description |
|---|---|
| DATE | Returns the date based on given year, month, and day. |
| DATEDIF | Calculates the number of days, months, or years between two dates. |
| DATEVALUE | Converts a date string into date value. |
| DAY | Returns the day from the given date. |
| DAYS | Returns the number of days between two dates. |
| DAYS360 | Calculates the number of days between two dates based on a 360-day year. |
| EDATE | Returns a date with given number of months before or after the specified date. |
| EOMONTH | Returns the last day of the month that is a specified number of months before or after a start date. |
| HOUR | Returns the number of hours in a specified time string. |
| MINUTE | Returns the number of minutes in a specified time string. |
| MONTH | Returns the number of months in a specified date string. |
| NETWORKDAYS | Returns the number of whole workdays between two dates. |
| NETWORKDAYS.INTL | Returns workdays between dates for a custom weekend. |
| NOW | Returns the current date and time. |
| SECOND | Returns the number of seconds in a specified time string. |
| TIME | Converts hours, minutes, seconds to the time formatted text. |
| TIMEVALUE | Converts a time in the form of text to a serial number. |
| TODAY | Returns the current date. |
| WEEKDAY | Returns the day of the week for a specified date. |
| WEEKNUM | Converts a serial number to a number representing where the week falls numerically within a year. |
| WORKDAY | Returns the serial number of the date before or after a specified number of workdays. |
| WORKDAY.INTL | Returns the date before or after a number of workdays with a custom weekend. |
| YEAR | Converts a serial number to a year. |
| YEARFRAC | Returns the year fraction representing the number of whole days between start_date and end_date. |
Lookup & Reference
| Formula | Description |
|---|---|
| ADDRESS | Returns a cell reference as text, given specified row and column numbers. |
| AREAS | Returns the number of areas in a reference. |
| CHOOSE | Returns a value from list of values, based on index number. |
| COLUMN | Returns the column number of a reference. |
| COLUMNS | Returns the number of columns in a reference. |
| FORMULATEXT | Returns the formula in a cell as text. |
| HLOOKUP | Looks for a value in the top row of an array and returns a value in the same column from a specified row. |
| HYPERLINK | Creates a shortcut that opens a document on a network server, intranet, or Internet. |
| INDEX | Returns a value of the cell in a given range based on row and column number. |
| INDIRECT | Returns a reference indicated by a text value. |
| LOOKUP | Looks for a value in a one-row or one-column range, then returns a value from the same position in another range. |
| MATCH | Returns the relative position of a specified value in a given range. |
| OFFSET | Returns a reference offset from a given reference. |
| ROW | Returns the row number of a reference. |
| ROWS | Returns the number of rows in a reference. |
| SORT | Sorts the contents of a column, range, or array in ascending or descending order. |
| UNIQUE | Returns unique values from a range or array. |
| VLOOKUP | Looks for a value in the first column of a lookup range and returns a corresponding value from a different column. |
| XLOOKUP | Searches a range for a match and returns the corresponding item. |
| XMATCH | Returns the relative position of an item in an array. |
Financial
| Formula | Description |
|---|---|
| ACCRINT | Returns the accrued interest for a security that pays periodic interest. |
| ACCRINTM | Returns the accrued interest for a security that pays interest at maturity. |
| AMORDEGRC | Returns the depreciation for each accounting period using a depreciation coefficient. |
| AMORLINC | Returns the depreciation for each accounting period. |
| COUPDAYBS | Returns the number of days from the beginning of the coupon period to the settlement date. |
| COUPDAYS | Returns the number of days in the coupon period that contains the settlement date. |
| COUPDAYSNC | Returns the number of days from the settlement date to the next coupon date. |
| COUPNCD | Returns the next coupon date after the settlement date. |
| COUPNUM | Returns the number of coupons payable between settlement and maturity. |
| COUPPCD | Returns the previous coupon date before the settlement date. |
| CUMIPMT | Returns the cumulative interest paid between two periods. |
| CUMPRINC | Returns the cumulative principal paid on a loan between two periods. |
| DB | Returns the depreciation of an asset for a period using fixed-declining balance. |
| DDB | Returns the depreciation of an asset for a period using double-declining balance. |
| DISC | Returns the discount rate for a security. |
| DOLLARDE | Converts a dollar price expressed as a fraction into a decimal number. |
| DOLLARFR | Converts a dollar price expressed as a decimal number into a fraction. |
| DURATION | Returns the annual duration of a security with periodic interest payments. |
| EFFECT | Returns the effective annual interest rate. |
| FV | Returns the future value of an investment. |
| FVSCHEDULE | Returns the future value of an initial principal after applying compound interest rates. |
| INTRATE | Returns the interest rate for a fully invested security. |
| IPMT | Returns the interest payment for an investment for a given period. |
| IRR | Returns the internal rate of return for a series of cash flows. |
| ISPMT | Returns the interest paid during a specific period of an investment. |
| MDURATION | Returns the modified Macauley duration for a security with an assumed par value of $100. |
| MIRR | Returns the modified internal rate of return for cash flows. |
| NOMINAL | Returns the nominal annual interest rate. |
| NPER | Returns the number of periods for an investment. |
| NPV | Returns the net present value of an investment based on periodic cash flows. |
| ODDFPRICE | Returns the price per $100 face value of a security with an odd first period. |
| ODDFYIELD | Returns the yield of a security with an odd first period. |
| ODDLPRICE | Returns the price per $100 face value of a security with an odd last period. |
| ODDLYIELD | Returns the yield of a security with an odd last period. |
| PMT | Returns the periodic payment for an annuity. |
| PPMT | Returns the payment on the principal for an investment for a given period. |
| PRICE | Returns the price per $100 face value of a security that pays periodic interest. |
| PRICEDISC | Returns the price per $100 face value of a discounted security. |
| PRICEMAT | Returns the price per $100 face value of a security that pays interest at maturity. |
| PV | Returns the present value of an investment. |
| RATE | Returns the interest rate per period of an annuity. |
| RECEIVED | Returns the amount received at maturity for a fully invested security. |
| SLN | Returns the straight-line depreciation of an asset for one period. |
| SYD | Returns the sum-of-years digits depreciation for a specified period. |
| TBILLEQ | Returns the bond-equivalent yield for a Treasury bill. |
| TBILLPRICE | Returns the price per $100 face value for a Treasury bill. |
| TBILLYIELD | Returns the yield for a Treasury bill. |
| VDB | Returns the depreciation of an asset for a period using the declining balance method. |
| XIRR | Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. |
| XNPV | Returns the net present value for a schedule of cash flows that is not necessarily periodic. |
| YIELD | Returns the yield on a security that pays periodic interest. |
| YIELDDISC | Returns the annual yield for a discounted security. |
| YIELDMAT | Returns the annual yield of a security that pays interest at maturity. |
Engineering
| Formula | Description |
|---|---|
| BESSELI | Returns the modified Bessel function In(x). |
| BESSELJ | Returns the Bessel function Jn(x). |
| BESSELK | Returns the modified Bessel function Kn(x). |
| BESSELY | Returns the Bessel function Yn(x). |
| BIN2DEC | Converts a binary number to decimal. |
| BIN2HEX | Converts a binary number to hexadecimal. |
| BIN2OCT | Converts a binary number to octal. |
| COMPLEX | Converts real and imaginary coefficients into a complex number. |
| CONVERT | Converts a number from one measurement system to another. |
| DEC2BIN | Converts a decimal number to binary. |
| DEC2HEX | Converts a decimal number to hexadecimal. |
| DEC2OCT | Converts a decimal number to octal. |
| DELTA | Tests whether two values are equal. |
| ERF | Returns the error function. |
| ERFC | Returns the complementary error function. |
| GESTEP | Tests whether a number is greater than a threshold. |
| HEX2BIN | Converts a hexadecimal number to binary. |
| HEX2DEC | Converts a hexadecimal number to decimal. |
| HEX2OCT | Converts a hexadecimal number to octal. |
| IMABS | Returns the absolute value (modulus) of a complex number. |
| IMAGINARY | Returns the imaginary coefficient of a complex number. |
| IMARGUMENT | Returns the argument theta of a complex number in radians. |
| IMCONJUGATE | Returns the complex conjugate of a complex number. |
| IMCOS | Returns the cosine of a complex number. |
| IMDIV | Returns the quotient of two complex numbers. |
| IMEXP | Returns the exponential of a complex number. |
| IMLN | Returns the natural logarithm of a complex number. |
| IMLOG10 | Returns the base-10 logarithm of a complex number. |
| IMLOG2 | Returns the base-2 logarithm of a complex number. |
| IMPOWER | Returns a complex number raised to an integer power. |
| IMPRODUCT | Returns the product of complex numbers. |
| IMREAL | Returns the real coefficient of a complex number. |
| IMSIN | Returns the sine of a complex number. |
| IMSQRT | Returns the square root of a complex number. |
| IMSUB | Returns the difference between two complex numbers. |
| IMSUM | Returns the sum of complex numbers. |
| OCT2BIN | Converts an octal number to binary. |
| OCT2DEC | Converts an octal number to decimal. |
| OCT2HEX | Converts an octal number to hexadecimal. |
Database
| Formula | Description |
|---|---|
| DAVERAGE | Averages the values in a column of a list or database that match criteria. |
| DCOUNT | Counts the cells that contain numbers in a column of a list or database that match criteria. |
| DCOUNTA | Counts nonblank cells in a column of a list or database that match criteria. |
| DGET | Extracts a single value from a column of a list or database that matches criteria. |
| DMAX | Returns the maximum value from a column of a list or database that matches criteria. |
| DMIN | Returns the minimum value from a column of a list or database that matches criteria. |
| DPRODUCT | Multiplies values in a column of a list or database that match criteria. |
| DSTDEV | Estimates the standard deviation of a population based on a sample of entries that match criteria. |
| DSTDEVP | Calculates the standard deviation of a population based on entries that match criteria. |
| DSUM | Adds the numbers in the field column of records in the database that match the criteria. |
| DVAR | Estimates variance based on a sample of entries that match criteria. |
| DVARP | Calculates variance based on the entire population of entries that match criteria. |
Information
| Formula | Description |
|---|---|
| CELL | Returns information about the formatting, location, or contents of a cell. |
| ERROR.TYPE | Returns a number corresponding to an error type. |
| INFO | Returns information about the current operating environment. |
| ISBLANK | Returns TRUE if the value is blank. |
| ISERR | Returns TRUE if the value is any error value except #N/A. |
| ISERROR | Returns TRUE if the value is any error value. |
| ISEVEN | Returns TRUE if the number is even. |
| ISLOGICAL | Returns TRUE if the value is a logical value. |
| ISNA | Returns TRUE if the value is the #N/A error value. |
| ISNONTEXT | Returns TRUE if the value is not text. |
| ISNUMBER | Returns true when the value parses as a numeric value; otherwise returns false. |
| ISODD | Returns TRUE if the number is odd. |
| ISREF | Returns TRUE if the value is a reference. |
| ISTEXT | Returns TRUE if the value is text. |
| N | Returns a value converted to a number. |
| NA | Returns the error value #N/A. |
| TYPE | Returns a number indicating the data type of a value. |
Formula Error Dialog
If an invalid formula is entered in a cell, an error dialog displays the corresponding error message. For example, an error occurs when a formula contains an incorrect number of arguments or missing parentheses.
| Error Message | Reason |
|---|---|
| We found that you typed a formula with invalid arguments | Occurs when passing an argument even though it wasn’t needed. |
| We found that you typed a formula with an empty expression | Occurs when passing an empty expression in the argument. |
| We found that you typed a formula with one or more missing opening or closing parentheses | Occurs when an open parenthesis or a close parenthesis is missing. |
| We found that you typed a formula which is improper | Occurs when passing a single reference but a range was needed. |
| We found that you typed a formula with a wrong number of arguments | Occurs when the required arguments were not passed. |
| We found that you typed a formula which requires 3 arguments | Occurs when the required 3 arguments were not passed. |
| We found that you typed a formula with mismatched quotes | Occurs when passing an argument with mismatched quotes. |
| We found that you typed a formula with a circular reference | Occurs when passing a formula with circular cell reference. |
| We found that you typed a formula which is invalid | Except in the cases mentioned above, all other errors will fall into this broad category. |
