How to: Use Functions and Nested Functions in Formulas
- 2 minutes to read
This example demonstrates how to use predefined functions in formulas to perform simple or complex calculations over arguments. Set the Range.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/spreadsheet-document-server-api-e4339
// 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"")";
The image below shows cells with formulas using different functions (the workbook is opened in Microsoft® Excel®).