How to: Use Functions and Nested Functions in Formulas

Sep 19, 2023
2 minutes to read

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.

View Example

FormulaActions.cs
FormulaActions.vb

csharp

// 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"")";

' 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®).