# Placing a Formula in Cells

Learn how to add formulas to cells, rows, or columns and optimize your spreadsheet calculations efficiently with Spread.NET.

## Content

You can add a formula to a cell or range of cells. You can also add a formula to all the cells in a row or column. The formula is a string with the expression of the formula, typically containing a combination of functions, operators, and constants.
When assigning a formula to the [Row](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.Row.html) class or [Column](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.Column.html) class, you are assigning a default formula for that row or column. In other words, the formula is used for every cell in the row or column (assuming that the formula is not overridden at the cell level). For a formula in a row or column, Spread uses the first cell in the row or column as the base location. The formula evaluates to a different result for each cell in column A if you use relative addressing. If you want each cell in column A to evaluate to the sum of the values in C2 and D2 (and not the value in the C and D columns for each row) then you would need to use the formula $C$2+$D$2, which uses absolute address. For examples of formulas that use cell references, refer to [Specifying a Cell Reference in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulacellref).
You can add a formula by specifying the **Formula** property for the object or by entering it in the Spread Designer. The procedures for using code are given below. For instructions on using Spread Designer to enter a formula, refer to [Entering a Formula in Spread Designer](/spreadnet/docs/latest/online-win/overview/spwin-designerguide/spwin-spd-design/spwin-spd-setformula). You can allow end users to enter formulas by allowing them to type the equals sign and then the formula; refer to [Allowing the User to Enter Formulas](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulaallowuser).
Be careful of the type of cell in which the data is found, and whether you use the **Text** or **Value** property when assigning data that is used in a formula. When you assign cell data using the **Text** property, the spreadsheet uses the cell type to parse an assigned string into the needed data type. For example, a number cell type parses a string into a double data type. When you assign the cell data using the **Value** property, the spreadsheet accepts the assigned object as is and no parsing occurs, so if you set it with a string, it remains a string. Some numeric functions (for example, SUM) ignore non-numeric values in a cell range. For example, if the cell range A1:A3 contains the values {1, "2", 3}, then the formula SUM(A1:A3) evaluates to 4 because the SUM function ignores the string "2". Be sure that you set the value correctly for any cells used in the calculation of a formula and that you set them with the correct data type.
A string constant in a formula can contain special characters such as the new line character (that is, '\\n'). Make sure that you enclose the string constant in quotes in the text representation of the formula. The following C# code creates a multiple-line text cell and assigns a formula that contains a string constant that contains a new line character.

```csharp
TextCellType ct = new TextCellType();
                ct.Multiline = true;
                fpspread1.Sheets[0].Cells[0,0].Formula = "\"line1\nline2\"";
```

## **Using a Shortcut**

Add a formula to a cell, row, or column by specifying the **Formula** property for that cell, row, or column.
**Example**
This example shows how to specify a formula that finds the product of five times the value in the first cell, and puts the result in another cell. Then it finds the sum of a range of cells (A1 through A4) and puts the result in every cell of the fourth column.

```csharp
fpSpread1.ActiveSheet.Cells[2, 0].Formula = "PRODUCT(A1,5)";
fpSpread1.ActiveSheet.Columns[3].Formula = "SUM(A1:A4)";
```

```vbnet
fpSpread1.ActiveSheet.Cells(2, 0).Formula = "PRODUCT(A1,5)"
fpSpread1.ActiveSheet.Columns(3).Formula = "SUM(A1:A4)"
```

## **Using Code**

1. Specify the cell, row, or column.
2. Add a formula to the cell, row, or column.

**Example**
This example shows how to specify a formula that sums two cells, doubles it, and puts the result in a third cell.

```csharp
FarPoint.Win.Spread.Cell mycell;
mycell = fpSpread1.Cells[2, 0];
mycell.Formula = "SUM(A1:A2) * 2";
```

```vbnet
Dim mycell As FarPoint.Win.Spread.Cell
mycell = fpSpread1.ActiveSheet.Cells(2, 0)
mycell.Formula = "SUM(A1:A2) * 2"
```

You can retain the suffix and prefix whitespace characters of the formula expression by using the [ReserveFormulaWhiteSpaces](/spreadnet/api/latest/online-win/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.CalcFeatures.html) enumeration. The whitespace characters can include many characters such as space, enter, new-line etc.
![](https://cdn.mescius.io/document-site-files/images/2238e618-a1fb-45a6-9ce5-9a4157bd2931/images/whitespace_formula.gif)
**Example**
This example shows how to keep the whitespace characters in the expression.

```csharp
IWorkbook workbook = fpSpread1.AsWorkbook();  
IWorksheet sheet1 = workbook.Worksheets[0];
            
sheet1.Cells["A1"].Value = 1;
sheet1.Cells["A2"].Value = 1;

fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures |= CalcFeatures.ReserveFormulaWhiteSpaces;
sheet1.Cells["C1"].Formula2 = "NOW(    )";
sheet1.Cells["C2"].Formula2 = "SUM(   Sheet1!A1:A2   )";
fpSpread1.AllowUserFormulas = true;
```

```vbnet
Dim workbook As IWorkbook = fpSpread1.AsWorkbook()
Dim sheet1 As IWorksheet = workbook.Worksheets(0)
            
sheet1.Cells("A1").Value = 1
sheet1.Cells("A2").Value = 1
            
fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures = fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures Or CalcFeatures.ReserveFormulaWhiteSpaces
sheet1.Cells("C1").Formula2 = "NOW(    )"
sheet1.Cells("C2").Formula2 = "SUM(   Sheet1!A1:A2   )"
fpSpread1.AllowUserFormulas = True
```

## Using the Formula Editor

At design time, you can enter formulas in cells using either the **Formula** bar or the **Formula Editor**, both of which are available from the Spread Designer. The **Formula Editor** is also available from the **Properties** Window. For more information, refer to [Entering a Formula in Spread Designer](/spreadnet/docs/latest/online-win/overview/spwin-designerguide/spwin-spd-design/spwin-spd-setformula). For details on the functions and operators that can be used to create a formula, refer to the [Formula Reference](https://developer.mescius.com/spreadnet/docs/latest/online-formula/overview.html).

1. Select the sheet tab of the sheet that contains the cells in which to place formulas.
2. Select the cell or cells in the sheet.
3. In the Formula property, click the arrow button. This opens the **Formula Editor**.
4. In the **Formula Editor**, you may type in the formula in the edit box. To assist in entering functions in the formula, you can double-click on a function name to have it appear in the edit box. Functions are organized by category. You can also type operators and constants to construct your formula.
5. When done, click **Apply**. Click **OK** to close the editor.
6. If you were working from within the Spread Designer, from the **File** menu choose **Apply and Exit** to apply your changes to the component and exit Spread Designer.

## See Also

[Formulas in Cells](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula)
[Specifying a Cell Reference in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulacellref)
[Specifying a Sheet Reference in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulasheetref)
[Specifying an External Reference in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-externalref)
[Using a Circular Reference in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulacircref)
[Nesting Functions in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulanested)
[Recalculating and Updating Formulas Automatically](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formularecalc)
[Finding a Value Using GoalSeek](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formula-goalseek)
[Allowing the User to Enter Formulas](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulaallowuser)
[Creating and Using a Custom Name](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulacustomname)
[Creating and Using a Custom Function](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulacustomfunc)
[Creating and Using External Variable](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-external-variable)
[Using the Array Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-arrayformula)
[Working with the Formula Text Box](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulabar)
[Setting up the Name Box](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-namebox)
[Using Language Package](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/UsingLanguagePackage)
[Accessing Data from Header or Footer](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-headerfooterformula)
[Managing External Reference](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-extrenalreference)
[Working With Dynamic Array Formulas](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/WorkingWithDynamicArrayFormulas)