Most Used Formulas in Excel using C#

Excel is one of the most popular and widely used spreadsheet applications. It provides a built-in feature to apply various formulas/functions to process data. These formulae help to perform different types of calculations or computations. A formula is an expression that calculates the value of a cell. Excel also provides functions that are predefined formulas, which are readily available to use. In this article, we will learn how to apply the most used formulas in Excel using C#.

The following topics shall be covered in this article:

C# API to Execute Most Used Formulas in Excel

We will be using Aspose.Cells for .NET API for using the formulas in XLSX files. It supports a huge set of mathematical, string, boolean, date/time, statistical, database, lookup, and reference formulas. Please either download the DLL of the API or install it using NuGet.

PM> Install-Package Aspose.Cells

Count Cells in Excel using C#

The COUNT function counts the number of cells in a provided range that contains numbers. We can use the count formula programmatically by following the steps given below:

  1. Firstly, load an Excel file using the Workbook class.
  2. Next, access the worksheet, either by its index (zero-based) or by name.
  3. Then, set the formula for a cell accessed by its name.
  4. After that, call the function CalculateFormula() to calculate formula results.
  5. Finally, save the Excel file using the Save() method. It takes output file path as an argument.

The following code sample shows how to execute the COUNT formula in Excel using C#.

Count Cells in Excel using C#.

Count Cells in Excel using C#.

Similarly, we can use the COUNTA function to count all the cells that are not empty. In this case, the COUNTA function shall return 10.

SUM Function in Excel using C#

The SUM function in Excel sums all the values in a given range. Please follow the steps mentioned earlier to calculate the sum of values. However, we just need to set the SUM formula at step 3.

The following code sample shows how to apply the SUM formula in Excel using C#.

SUM Function in Excel using C#.

SUM Function in Excel using C#.

Calculate Average in Excel using C#

We can calculate the average of the provided range of values in Excel using the AVERAGE function. Please follow the steps mentioned earlier to calculate the average of given values. However, we just need to set the AVERAGE formula at step 3.

The following code sample shows how to calculate the average in Excel using C#.

Calculate Average in Excel using C#.

Calculate the Average in Excel using C#.

IF Function in Excel using C#

We can apply the IF function in Excel programmatically to check whether a condition is met or not. It returns one value if true and another value if false. Please follow the steps mentioned earlier to use the IF function. However, we just need to set the IF condition at step 3.

The following code sample shows how to apply the IF function in Excel using C#.

IF Function in Excel using C#.

IF Function in Excel using C#.

Percentage Formula in Excel using C#

We can also apply the percentage formula in Excel using the basic percentage formula, e.g., “(part/total)*100”. Please follow the steps mentioned below to calculate the percentage in Excel.

  1. Firstly, create an instance of the Workbook class.
  2. Next, add a new Worksheet to the newly created Workbook.
  3. Then, access the added Worksheet either by its index (zero-based) or by name.
  4. Next, add values to the required Cells using the PutValue function.
  5. Then, set the percentage formula for a cell accessed by its name.
  6. After that, call the function CalculateFormula() to calculate formula results.
  7. Finally, save the Excel file using the Save() method. It takes output file path as an argument.

The following code sample shows how to apply the percentage formula in Excel using C#.

Percentage Formula in Excel using C#

Percentage Formula in Excel using C#.

Get a Free License

Please try the API without evaluation limitations by requesting a free temporary license.

Conclusion

In this article, we have learned how to apply the most used formulas in Excel using C#. Specifically, we have learned how to calculate SUM, Count, and Average in Excel programmatically. We have also seen how to apply the percentage formula in Excel. Besides, you can learn more about Aspose.Cells for .NET API using the documentation. In case of any ambiguity, please feel free to contact us on the forum.

See Also