Gets or sets the formula contained in the cell or cell range.
A string expression that represents a formula contained in the cell or cell range. It starts with an equal sign (=).
A spreadsheet formula is an equation that performs a calculation on data contained in worksheet cells. A formula expression can contain constants, operators, cell references, calls to functions, and defined names.
To associate a formula with a cell, use the Formula property. When you assign a formula string to the Formula property of the cell rage, the shared formula is created and applied to this range. This means that the specified formula is associated with each cell contained in the specified cell range and each cell's formula includes automatically adjusted relative cell references. This approach allows you to avoid duplicating and adjusting the same formula for each cell in the range, and reduce the size of the spreadsheet file.
This example demonstrates how to use predefined functions in formulas to perform simple or complex calculations over arguments. Set the CellRange.Formula property to a formula. Follow the rules below to create a formula that uses a function.
- Start a formula with the "=" sign, as you usually do when creating a formula.
- Type the function name.
- Type the function arguments in parentheses. Arguments can be numbers, text and logical values, cell references and names, or other functions.
A complete sample project is available at https://github.com/DevExpress-Examples/winforms-spreadsheetcontrol-api-e4655
// If the number in cell A2 is less than 10, the formula returns "Normal" // and this text is displayed in cell C2. Otherwise, cell C2 displays "Excess". worksheet.Cells["C2"].Formula = @"=IF(A2<10, ""Normal"", ""Excess"")"; // Calculate the average value for cell values within the "A2:A7" range. worksheet.Cells["C3"].Formula = "=AVERAGE(A2:A7)"; // Add the values contained in cells A3 through A5, add the value contained in cell A6, // and add 100 to that result. worksheet.Cells["C4"].Formula = "=SUM(A3:A5,A6,100)"; // Use a nested function in a formula. // Round the sum of the values contained in cells A6 and A7 to two decimal places. worksheet.Cells["C5"].Formula = "=ROUND(SUM(A6,A7),2)"; // Add the current date to cell C6. worksheet.Cells["C6"].Formula = "=Today()"; worksheet.Cells["C6"].NumberFormat = "m/d/yy"; // Convert the specified text to uppercase. worksheet.Cells["C7"].Formula = @"=UPPER(""formula"")";