A formula is a combination of operators, functions, and constants that adhere to specific syntax rules and can perform various calculations. In Spread for WPF, you can apply a formula to a single cell or a range of cells to perform dynamic calculations and manipulate data efficiently within the worksheet.

Add Built-in Formula

You can add built-in formulas to cells by using the Formula property of the IRange interface. For more information about the built-in functions and operators, see the What is Formula topic of Formula Reference.

The following image illustrates how the SUM formula is used to add values in a cell. This illustrates the process of summing a range of values using the formula directly within the cell, showing the resulting total calculation.

Refer to the following example code to add numbers directly in the cell using the SUM formula.

// SUM formula directly adds the numbers and displays the result.
spreadSheet1.Workbook.ActiveSheet.Cells["A1"].Formula = "SUM(4,3,2,1)";

' SUM formula directly adds the numbers and displays the result.
spreadSheet1.Workbook.ActiveSheet.Cells("A1").Formula = "SUM(4,3,2,1)"

Add Cell Reference in Formula

In addition to values, operators, and functions, a formula can also include references to values in other cells. Cell references are used to refer to specific cells or ranges of cells to perform calculations or operations on the values stored in those cells. For more details on cell references, see the Cell References in a Formula topic of Formula Reference.

The following image illustrates how the SUM formula is used to add values stored in cells B2 through B6. This shows how the formula sums up the range of values and displays the result.

Refer to the following example code to method to use the SUM formula with a cell reference.

// SUM formula with cell reference.
spreadSheet1.Workbook.ActiveSheet.Cells["C8"].Formula = "SUM(B2:B6)";

' SUM formula with cell reference.
spreadSheet1.Workbook.ActiveSheet.Cells("C8").Formula = "SUM(B2:B6)"

Add Sheet Reference in Formula

Sheet references allow you to refer to cells, ranges, or ranges from another worksheet (or sheet) within a formula. This is especially useful when you need to work with data spread across multiple sheets and want to perform calculations using data from different sources. For more details on sheet references, see the Sheet References in a Formula topic of Formula Reference.

The following GIF illustrates how the SUM of total sales is calculated and displayed on a different sheet within the workbook.

Refer to the following example code to use the SUM formula with sheet reference.

GrapeCity.Spreadsheet.IWorksheet worksheet2 = spreadSheet1.Workbook.Worksheets.Add();
worksheet2.Cells["A1"].Text = "Total Sales:";
worksheet2.Cells["B1"].Formula = "SUM(Sheet1!B2:Sheet1!B6)";

Dim worksheet2 As GrapeCity.Spreadsheet.IWorksheet = spreadSheet1.Workbook.Worksheets.Add()
worksheet2.Cells("A1").Text = "Total Sales:"
worksheet2.Cells("B1").Formula = "SUM(Sheet1!B2:Sheet1!B6)"