Search results

Formulas in ASP.NET Core Spreadsheet control

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.

tagHelper
formulaController.cs
<ejs-spreadsheet id="spreadsheet" created="created" showRibbon="false" showSheetTabs="false">
        <e-spreadsheet-sheets>
            <e-spreadsheet-sheet>
                <e-spreadsheet-ranges>
                    <e-spreadsheet-range dataSource="ViewBag.DefaultData" startCell="A2"></e-spreadsheet-range>
                </e-spreadsheet-ranges>
                <e-spreadsheet-rows>
                    <e-spreadsheet-row height="40" customHeight="true">
                        <e-spreadsheet-cells>
                            <e-spreadsheet-cell value="Monthly Expense" colSpan="5">
                                <e-spreadsheet-cellstyle textAlign="Center" fontWeight="Bold" verticalAlign="Middle" fontStyle="Italic" fontSize="15pt"></e-spreadsheet-cellstyle>
                            </e-spreadsheet-cell>
                        </e-spreadsheet-cells>
                    </e-spreadsheet-row>
                    <e-spreadsheet-row height="30"></e-spreadsheet-row>
                    <e-spreadsheet-row index="11">
                        <e-spreadsheet-cells>
                            <e-spreadsheet-cell value="Totals" colSpan="2">
                                <e-spreadsheet-cellstyle fontWeight="Bold" fontStyle="Italic"></e-spreadsheet-cellstyle>
                            </e-spreadsheet-cell>
                            <e-spreadsheet-cell formula="=SUM(B3:B11)"></e-spreadsheet-cell>
                            <e-spreadsheet-cell formula="=SUM(C3:C11)"></e-spreadsheet-cell>
                            <e-spreadsheet-cell formula="=SUM(D3:D11)"></e-spreadsheet-cell>
                        </e-spreadsheet-cells>
                    </e-spreadsheet-row>
                    <e-spreadsheet-row>
                        <e-spreadsheet-cells>
                            <e-spreadsheet-cell index="1" value="Number of Categories" colSpan="2">
                                <e-spreadsheet-cellstyle fontWeight="Bold" textAlign="Right"></e-spreadsheet-cellstyle>
                            </e-spreadsheet-cell>
                            <e-spreadsheet-cell index="3" formula="=COUNTA(A3:A11)"></e-spreadsheet-cell>
                        </e-spreadsheet-cells>
                    </e-spreadsheet-row>
                    <e-spreadsheet-row>
                        <e-spreadsheet-cells>
                            <e-spreadsheet-cell index="1" value="Average Spend" colSpan="2">
                                <e-spreadsheet-cellstyle fontWeight="Bold" textAlign="Right"></e-spreadsheet-cellstyle>
                            </e-spreadsheet-cell>
                            <e-spreadsheet-cell index="3" formula="=AVERAGE(B3:B11)" format="$#,##0"></e-spreadsheet-cell>
                        </e-spreadsheet-cells>
                    </e-spreadsheet-row>
                    <e-spreadsheet-row>
                        <e-spreadsheet-cells>
                            <e-spreadsheet-cell index="1" value="Min Spend" colSpan="2">
                                <e-spreadsheet-cellstyle fontWeight="Bold" textAlign="Right"></e-spreadsheet-cellstyle>
                            </e-spreadsheet-cell>
                            <e-spreadsheet-cell index="3" formula="=MIN(B3:B11)" format="$#,##0"></e-spreadsheet-cell>
                        </e-spreadsheet-cells>
                    </e-spreadsheet-row>
                    <e-spreadsheet-row>
                        <e-spreadsheet-cells>
                            <e-spreadsheet-cell index="1" value="Max Spend" colSpan="2">
                                <e-spreadsheet-cellstyle fontWeight="Bold" textAlign="Right"></e-spreadsheet-cellstyle>
                            </e-spreadsheet-cell>
                            <e-spreadsheet-cell index="3" formula="=MAX(B3:B11)" format="$#,##0"></e-spreadsheet-cell>
                        </e-spreadsheet-cells>
                    </e-spreadsheet-row>
                </e-spreadsheet-rows>
                <e-spreadsheet-columns>
                    <e-spreadsheet-column width="150"></e-spreadsheet-column>
                    <e-spreadsheet-column width="120"></e-spreadsheet-column>
                    <e-spreadsheet-column width="120"></e-spreadsheet-column>
                    <e-spreadsheet-column width="120"></e-spreadsheet-column>
                    <e-spreadsheet-column width="120"></e-spreadsheet-column>
                </e-spreadsheet-columns>
            </e-spreadsheet-sheet>
        </e-spreadsheet-sheets>
    </ejs-spreadsheet>


    <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 E12 cell.
            this.updateCell({ formula: '=PERCENTAGE(C12,D12)' }, 'E12');
        }

        // Custom function to calculate percentage between two cell values.
        function calculatePercentage(firstCell, secondCell) {
            return (firstCell) / (secondCell);
        }
    </script>
public IActionResult 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();
        }

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.

tagHelper
definedNameController.cs
<ejs-spreadsheet id="spreadsheet" created="created" showRibbon="false" showSheetTabs="false" beforeDataBound="beforeDataBound">
        <e-spreadsheet-sheets>
            <e-spreadsheet-sheet name="Budget Details">
                <e-spreadsheet-ranges>
                    <e-spreadsheet-range dataSource="ViewBag.DefaultData" startCell="A2"></e-spreadsheet-range>
                </e-spreadsheet-ranges>
                <e-spreadsheet-rows>
                    <e-spreadsheet-row height="40" customHeight="true">
                        <e-spreadsheet-cells>
                            <e-spreadsheet-cell value="Monthly Expense" colSpan="5">
                                <e-spreadsheet-cellstyle textAlign="Center" fontWeight="Bold" verticalAlign="Middle" fontStyle="Italic" fontSize="15pt"></e-spreadsheet-cellstyle>
                            </e-spreadsheet-cell>
                        </e-spreadsheet-cells>
                    </e-spreadsheet-row>
                    <e-spreadsheet-row height="30"></e-spreadsheet-row>
                    <e-spreadsheet-row index="11">
                        <e-spreadsheet-cells>
                            <e-spreadsheet-cell value="Totals" colSpan="2">
                                <e-spreadsheet-cellstyle fontWeight="Bold" fontStyle="Italic"></e-spreadsheet-cellstyle>
                            </e-spreadsheet-cell>
                            <e-spreadsheet-cell formula="=SUM(MonthlySpendings)"></e-spreadsheet-cell>
                            <e-spreadsheet-cell formula="=SUM(AnnualSpendings)"></e-spreadsheet-cell>
                            <e-spreadsheet-cell formula="=SUM(LastYearSpendings)"></e-spreadsheet-cell>
                            <e-spreadsheet-cell formula="=C12/D12"></e-spreadsheet-cell>
                        </e-spreadsheet-cells>
                    </e-spreadsheet-row>
                    <e-spreadsheet-row>
                        <e-spreadsheet-cells>
                            <e-spreadsheet-cell index="1" value="Number of Categories" colSpan="2">
                                <e-spreadsheet-cellstyle fontWeight="Bold" textAlign="Right"></e-spreadsheet-cellstyle>
                            </e-spreadsheet-cell>
                            <e-spreadsheet-cell index="3" formula="=COUNTA(Categories)"></e-spreadsheet-cell>
                        </e-spreadsheet-cells>
                    </e-spreadsheet-row>
                    <e-spreadsheet-row>
                        <e-spreadsheet-cells>
                            <e-spreadsheet-cell index="1" value="Average Spend" colSpan="2">
                                <e-spreadsheet-cellstyle fontWeight="Bold" textAlign="Right"></e-spreadsheet-cellstyle>
                            </e-spreadsheet-cell>
                            <e-spreadsheet-cell index="3" formula="=AVERAGE(MonthlySpendings)" format="$#,##0"></e-spreadsheet-cell>
                        </e-spreadsheet-cells>
                    </e-spreadsheet-row>
                    <e-spreadsheet-row>
                        <e-spreadsheet-cells>
                            <e-spreadsheet-cell index="1" value="Min Spend" colSpan="2">
                                <e-spreadsheet-cellstyle fontWeight="Bold" textAlign="Right"></e-spreadsheet-cellstyle>
                            </e-spreadsheet-cell>
                            <e-spreadsheet-cell index="3" formula="=MIN(MonthlySpendings)" format="$#,##0"></e-spreadsheet-cell>
                        </e-spreadsheet-cells>
                    </e-spreadsheet-row>
                    <e-spreadsheet-row>
                        <e-spreadsheet-cells>
                            <e-spreadsheet-cell index="1" value="Max Spend" colSpan="2">
                                <e-spreadsheet-cellstyle fontWeight="Bold" textAlign="Right"></e-spreadsheet-cellstyle>
                            </e-spreadsheet-cell>
                            <e-spreadsheet-cell index="3" formula="=MAX(MonthlySpendings)" format="$#,##0"></e-spreadsheet-cell>
                        </e-spreadsheet-cells>
                    </e-spreadsheet-row>
                </e-spreadsheet-rows>
                <e-spreadsheet-columns>
                    <e-spreadsheet-column width="150"></e-spreadsheet-column>
                    <e-spreadsheet-column width="120"></e-spreadsheet-column>
                    <e-spreadsheet-column width="120"></e-spreadsheet-column>
                    <e-spreadsheet-column width="120"></e-spreadsheet-column>
                    <e-spreadsheet-column width="120"></e-spreadsheet-column>
                </e-spreadsheet-columns>
            </e-spreadsheet-sheet>
        </e-spreadsheet-sheets>
        <e-spreadsheet-definednames>
            <e-spreadsheet-definedname name="Categories" refersTo="=Budget Details!A3:A11"></e-spreadsheet-definedname>
            <e-spreadsheet-definedname name="MonthlySpendings" refersTo="=Budget Details!B3:B11"></e-spreadsheet-definedname>
            <e-spreadsheet-definedname name="AnnualSpendings" refersTo="=Budget Details!C3:C11"></e-spreadsheet-definedname>
        </e-spreadsheet-definednames>
    </ejs-spreadsheet>


    <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 IActionResult 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();
        }

Supported Formulas

The following are the list of formulas supported in spreadsheet,

Formula Description
ABS Returns the value of a number without its sign.
AND Returns TRUE if all the arguments are TRUE, otherwise returns FALSE.
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 Clears content of the active cell and enables edit mode.
AVERAGEIFS Calculates average for the cells based on specified conditions.
CEILING Rounds a number up to the nearest multiple of a given factor.
CHOOSE Returns a value from list of values, based on index number.
CONCAT Concatenates a list or a range of text strings.
CONCATENATE Combines two or more strings together.
COUNT Counts the cells that contain numeric values in a range.
COUNTA Counts the cells that contains values in a range.
COUNTIF Counts the cells based on specified condition.
COUNTIFS Counts the cells based on specified conditions.
DATE Returns the date based on given year, month, and day.
DAY Returns the day from the given date.
DAYS Returns the number of days between two dates.
FIND Returns the position of a string within another string, which is case sensitive.
FLOOR Rounds a number down to the nearest multiple of a given factor.
IF Returns value based on the given expression.
IFERROR Returns value if no error found else it will return specified value.
IFS Returns value based on the given multiple expressions.
INDEX Returns a value of the cell in a given range based on row and column number.
INTERCEPT Calculates the point of the Y-intercept line via linear regression.
LN Returns the natural logarithm of a number.
MATCH Returns the relative position of a specified value in given range.
MAX Returns the largest number of the given arguments.
MIN Returns the smallest number of the given arguments.
OR Returns TRUE if any of the arguments are TRUE, otherwise returns FALSE.
PRODUCT Multiplies a series of numbers and/or cells.
RADIANS Converts degrees into radians.
RAND Returns a random number between 0 and 1.
RANDBETWEEN Returns a random integer based on specified values.
SLOPE Returns the slope of the line from linear regression of the data points.
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 specified condition.
SUMIFS Adds the cells based on specified conditions.

See Also