General Information
A formula is an algebraic expression that contains values, operators, functions, and references to cells. The following list takes a closer look at the compound parts of formulas:
Values: Expressions can contain numbers, booleans, strings, arrays. For example, all of the following formulas represent valid expressions that contain values of different type: =3+4, ="Rad"&"Spreadsheet", =AND(TRUE, FALSE), =SUM({1, 2; 3, 4}).
Operators: Formulas can include arithmetic, comparison, cell reference, and text operators. Examples of expressions that use operators are:=-1+2, =B1<=4, =SUM(A1:B4), ="Rad"&"Spreadsheet". More information about the supported operators can be found in the Operator article.
Cell references: Formulas can contain references to cells or ranges of cells. For example, the expression =B1+B2 adds the values of cells B1 and B2 of the current worksheet, while the formula =SUM(B1:B5) sums up the values of all cells between B1 and B5. Further information about cell references is available in the Cell References article.
Built-in functions: You can take advantage of a large set of predefined functions that can be directly included in expressions. Examples of formulas that use built-in functions are: =ABS(-5), =COS(PI()), =AND(B1, B2)<>OR(C1, C2). You can find the full list in the Functions article.
Get and Set Formulas in Cells
In order to set the value of a cell to a formula use the SetValue() method of the CellSelection class and pass the formula string as an argument. In order to produce a formula value the string you enter should start with either = (equal) or – (minus) sign, otherwise, the method treats the input as plain text.
The examples in the topic use numbers for simplicity. It doesn't matter whether the formula is with numbers or with cell references and you can work with any formula.
Example 1 creates a workbook from scratch and adds a worksheet. Further, the code assigns the value =3+4 to cell A1.
Example 1: Set formula
Workbook workbook = new Workbook();
workbook.Worksheets.Add();
workbook.ActiveWorksheet.Cells[0, 0].SetValue("=3+4");
Additionally to entering formulas in cells, you can retrieve and inspect formula values. If you get the value of Cell[0, 0], the result will be an object of type FormulaCellValue. Just like other cell values, the FormulaCellValue class inherits from CellValueBase class and conforms to the ICellValue interface. Thus, the class exposes several important properties and useful methods that allow you to get information about both the entered value and its result value.
A closer look at the value of cell A1 will reveal that the two methods GetValueAsString() and GetResultValueAsString() return the original string of the expression and string of the computed result, respectively. Similarly, the FormulaCellValue offers information about the CellValueType of its value and result value through the ValueType and ResultValueType properties.
Example 2: Get formula value
FormulaCellValue formulaCellValue = workbook.ActiveWorksheet.Cells[0, 0].GetValue().Value as FormulaCellValue;
if (formulaCellValue != null)
{
RadExpression expression = formulaCellValue.Value;
//expression is AdditionExpression with operands 3 and 4
CellValueFormat format = workbook.ActiveWorksheet.Cells[0, 0].GetFormat().Value;
string valueAsString = formulaCellValue.GetValueAsString(format);
//valueAsString = "=3+4"
string resultValueAsString = formulaCellValue.GetResultValueAsString(format);
//resultAsString = "7"
CellValueType valueType = formulaCellValue.ValueType;
//valueType = Formula
CellValueType resultValueType = formulaCellValue.ResultValueType;
//resultValueType = Number
}