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. The formula can use cell and cross-sheet references.

When assigning a formula to the Row class or Column 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 addressing. For examples of formulas that use cell references, refer to Specifying a Cell Reference Style in a Formula.

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 Adding Formulas to Cells.

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 NumberCellType 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.

If the AllowUserFormulas property is true, the user can copy formulas to other cells (type = and select the formula and use Control-C to copy it).

For more information about formulas, refer to the Formula Reference.

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 third column.

FpSpread2.ActiveSheetView.Cells[2, 0].Formula = "PRODUCT(A1,5)";
FpSpread2.ActiveSheetView.Columns[3].Formula = "SUM(A1:A4)";

FpSpread2.ActiveSheetView.Cells(2, 0).Formula = "PRODUCT(A1,5)"
FpSpread2.ActiveSheetView.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 puts the sum of two cells in a third cell.

FarPoint.Web.Spread.Cell mycell;
mycell = FpSpread1.Cells[2, 0];
mycell.Formula = "SUM(A1:A2)";

Dim mycell as FarPoint.Web.Spread.Cell
mycell = FpSpread1.Cells(2, 0)
mycell.Formula = "SUM(A1:A2)"

Example

This example shows how to use a cross-sheet reference in a formula.

FpSpread1.Sheets.Count = 3;
FpSpread1.Sheets[0].Cells[0, 0].Formula = "sheet1!A2+sheet2!A1";

FpSpread1.Sheets.Count = 3
FpSpread1.Sheets(0).Cells(0, 0).Formula = "sheet1!A2+sheet2!A1"