Advanced C# Excel Functions for Data Analysis - Part 2

Welcome back to our blog series on using advanced Excel functions in a .NET application. In the previous blog post, we learned how to use the Document Solutions for Excel API (DsExcel) to add advanced Excel functions such as LOOKUP, VLOOKUP, etc. Now, in this blog, we will continue to explore some additional advanced Excel functions that are helpful in the analysis of financial data.

• SUBTOTAL
• CHOOSEROWS/CHOOSECOLS
• SUMIF/AVERAGEIF
• PMT/PPMT/IPMT
• IF combined with AND/OR

Download Document Solutions for Excel, .NET Edition now and try them yourself!

Let’s use the following monthly budget data to understand these Excel functions:

To obtain this data, deserialize the attached JSON and assign it to the specified range in the workbook. As an example, the code below showcases the extraction of the operating expenses table:

MonthlyBudget.json

public class OperatingExpenses
{
    public string? Expense { get; set; }
    public string? Category { get; set; }
    public decimal Estimated { get; set; }
    public decimal Actual { get; set; }
}

string jsonData = File.ReadAllText("MonthlyBudget.json");
//Deserialize the JSON into a JObject

JObject jsonObject = JObject.Parse(jsonData);
// Access the "operatingExpenses" array

JArray operatingExpensesArray = (JArray?)jsonObject["financialData"]!["operatingExpenses"]!;

// Parse the data into a C# array
OperatingExpenses[]? operatingExpenses = operatingExpensesArray?.ToObject<OperatingExpenses[]?>();

//Create Normal Object array to assign it to Workbook
object[,] objectOperatingExpensesArray = new object[operatingExpensesArray!.Count + 1, typeof(OperatingExpenses).GetProperties().Length];

colIndex = 0;
rowIndex = 0;
foreach (var property in typeof(OperatingExpenses).GetProperties())
{
    objectOperatingExpensesArray[rowIndex, colIndex] = property.Name;
    colIndex++;
}

rowIndex++;
//Iterate personnelExpenses array to get the values
foreach (var operatingExpense in operatingExpenses!)
{
    colIndex = 0;
    foreach (var property in typeof(OperatingExpenses).GetProperties())
    {
        objectOperatingExpensesArray[rowIndex, colIndex] = property.GetValue(operatingExpense)!;
        colIndex++;
    }
    rowIndex++;
}
//assign array to Excel Range
monthlyBudgetDetailsDataSheet.Range["B20:E40"].Value = objectOperatingExpensesArray;

Without any further delay, let’s dive straight into the function.

SUBTOTAL

The SUBTOTAL function calculates different aggregates, like sum, average, count, etc., on the specified range of values. Unlike direct aggregate Excel functions such as SUM, AVERAGE, etc., the SUBTOTAL function allows you to omit hidden or filtered rows in the computation.

Let's understand this function with an example by calculating the sum of all operational expenses in the data above. But before we proceed, take a look at the syntax of the SUBTOTAL function:

 =SUBTOTAL(function_num, ref1, [ref2],...)

function_num: This parameter specifies the function code to use for the subtotal. This code goes from 1 to 11 or 101 to 111, depending on the function you want to perform. For example, 1 represents the average, 9 represents the sum, and so on. In our case, we are calculating the sum and do not have any hidden range, so its corresponding code is 109.

ref1, [ref2]..: The remaining parameters define the ranges for the operation, which, in this case, is E21:E40.

After assigning all the parameters, the SUBTOTAL function looks like this:

=SUBTOTAL(109,E21:E40)

You can apply this function to your workbook using the DsExcel code below:

monthlyBudgetDetailsDataSheet.Range["E41"].Formula ="=SUBTOTAL(109,E21:E40)";

CHOOSEROWS/CHOOSECOLS

The CHOOSEROWS function extracts (particular) rows from the range based on specified row indexes. Using this function, you can focus on relevant rows and exclude unnecessary information from the data.

Say you need to find the top 5 operating expenses from this data. The function gets the records for the specified row indexes. So, if you mentioned row index from 1 to 5, you get the top 5 rows of the current unsorted data.

Now, sort the values of the Actual column from high to low and specify the row index from 1 to 5. That is it! You have got the top 5 expenses.

Let's go step by step and understand the working of the CHOOSEROWS function to fetch the top 5 expenses. First, understand the syntax of the CHOOSEROWS function.

=CHOOSEROWS(array, row_num1, [row_num2], ...)

array: The first parameter refers to the range of data that is “'Monthly Budget Details'!B21:E40” here.

row_Number: The next parameters specify the row index numbers to fetch from the range i.e. 1,2,3,4,5.

Put these parameters in the CHOOSEROWS functions, and fetch the top 5 rows first:

=CHOOSEROWS('Monthly Budget Details'!$B$21:$E$40,1,2,3,4,5)

Let’s apply the SORT function to our range and then fetch the top 5 rows to get the top 5 expenses.

=CHOOSEROWS(SORT('Monthly Budget Details'!$B$21:$E$40,4,-1),1,2,3,4,5)

Here is the final DsExcel code to retrieve the top 5 operational expenses:

monthlyBudgetSummaryDataSheet.Range["B12"].Formula2 = "=CHOOSEROWS(SORT('Monthly Budget Details'!$B$21:$E$40,4,-1),{1,2,3,4,5})";

Similarly, you can use the CHOOSECOLS function to extract specific columns.

SUMIF/AVERAGEIF Function

The SUMIF function performs conditional summation for a range of values. You can apply this condition to the specific range or the value itself.

Suppose we want to find out the sum of operational data category-wise. We can use the SUMIF function for this, but first, let’s see the syntax of the SUMIF function:

=SUMIF(range,criteria,[sum_range])

range: The first parameter specifies the range to apply the condition, i.e., the Category column “Monthly Budget Details'!$C$21:$C$40”.

criteria: This parameter defines the condition. In this case, we have specified the category name in cell B21, so the condition is "="&B21.

[sum_range]: The last parameter is the range to perform the sum, i.e., the actual cost column 'Monthly Budget Details'!$E$21:$E$40.After applying all these parameters, the formula is structured as follows:

=SUMIF('Monthly Budget Details'!$C$21:$C$40,\"=\"&B21,'Monthly Budget Details'!$E$21:$E$40)

Check out the following DsExcel code to calculate the total of expenses for a certain category.

monthlyBudgetSummaryDataSheet.Range["D21"].Formula = =SUMIF('Monthly Budget Details'!$C$21:$C$40,\"=\"&B21,'Monthly Budget Details'!$E$21:$E$40) ";

To calculate the average operational cost categorically, you can use the AVERAGEIF function.

PMT

PMT is a financial Excel function that calculates the monthly payment for a loan with a fixed interest rate over a specific duration.

Suppose you want to know the monthly EMI for a loan amount of $1,000,000 taken for a period of 20 years. If the yearly interest rate is 8%, what amount would you need to pay as an installment every month? Let’s understand the syntax of the PMT function and then use it to find the EMI amount.

=PMT(rate,nper,pv,[fv],[type])

rate: The first parameter stands for the monthly interest rate for the loan, which is equal to the yearly annual rate/12, i.e., 8/12 in this case.

nper: This parameter defines the total number of months for the loan. Let’s consider a loan term of 20 years, which is equivalent to 240 months.

pv: The third parameter is the present value, or the total loan amount, i.e., $1,000,000 in this case.

fv: This optional parameter represents the future value or remaining cash balance after the last payment. To keep this use case simple to understand, let’s keep its value at default.

Type: This parameter represents the payment timing. Here, the default value, “0”, indicates payments at the end of the period, and the value “1” signifies payments at the beginning. We are keeping this optional parameter at default, too.

Applying all the necessary parameters in the PMT function, it appears as follows:

=PMT(F7/12,F6,F5)

Take a look at this DsExcel code! It shows you how to use the PMT function to calculate the monthly EMI.

loanCalculatorDataSheet.Range["F8"].Formula = "=PMT(F7/12,F6,F5)";

Similarly, you can use PPMT and IPMT to calculate the principle and interest value of your monthly EMI separately.

IF combined with AND/OR

The IF function evaluates a condition and returns a value based on whether the condition is true or false. You can expand its capabilities by combining it with other logical functions like AND or OR to test multiple conditions.

Let's extend our last use-case, where we calculated the monthly EMI for a loan. So, to decide if we can opt for this loan, let’s check two conditions: first, the remaining balance should be greater than the EMI amount, and second, the universal rule that EMI should not exceed 40% of the total income.

Let’s see the syntax of the IF function:

=IF(logical_test, [value_if_true], [value_if_false])

As previously noted, we can expand the function above by integrating it with the AND function.

=IF(AND(logical_test1,[ logical_test2],…), value_if_true, value_if_false)

logical_test1: This parameter specifies the first condition to evaluate, which is whether the monthly balance is greater than the EMI amount or not. That is, set the parameter to 'Monthly Budget Summary'!D8>=ABS(F8).

[logical_test2],..: This parameter mentions the second condition: the EMI should not be greater than 40% of income. So, value of this parameter should be (ABS(F8)*100/'Monthly Budget Summary'!D6)<40

value_if_true: This parameter defines the value to return when logical_test evaluates to TRUE. We set it to Yes here.

value_if_false: This parameter indicates the value to return when logical_test evaluates to FALSE, i.e., no here.

Fill in the abovementioned values in the function, and the IF function appears as:

=IF(AND('Monthly Budget Summary'!D8>=ABS(F8),(ABS(F8)*100/'Monthly Budget Summary'!D6)<40),\"Yes\",\"No\")

Below is the DsExcel code that illustrates how to use the IF function to check multiple conditions:

loanCalculatorDataSheet.Range["G9"].Formula = "=IF(AND('Monthly Budget Summary'!D8>=ABS(F8),(ABS(F8)*100/'Monthly Budget Summary'!D6)<40),\"Yes\",\"No\")";

Download the sample to see all the above-explained functions in action.

Conclusion

In this blog, we saw how DsExcel made complex financial analysis simpler using Excel functions without any dependency on MS Excel. In addition to advanced Excel functions, DsExcel offers many other helpful features in financial analysis. Charts, conditional formatting, data validation, filters, slicers, and sparklines tables are a few to name.

The best part? You can easily try it out yourself! Just download the Document Solutions library and boost your app with advanced Excel-like features.

More References:

• Product Demo .NET
• Documentation .NET
• Product Demo JAVA
• Documentation JAVA