Using custom functions to perform Excel calculations offers a greater extensibility within your spreadsheet. For example, you may need to perform calculations using a deeply nested formula, company proprietary formulas from your finance team, or a combination of standard functions. Yes, you can add two cells using a built-in function; however, suppose you want to concatenate string values of two cells. Additionally, you may need to calculate the sum of those cells in a range. In such situations, calculations cannot be handled using a standard built-in function. You’ll need to write a custom function or a user-defined function.

Advantages of custom functions

  • They run across all Excel platforms (Win, Mac, Mobile, Office-online).
  • They run fast.
  • The look and feel are like native Excel functions (e.g., formula tips)
  • They can make web service calls
  • They can run offline if they don’t depend on the web
  • They can run even when the workbook is unattended

While built-in functions may be faster and better in memory usage, custom functions can help you add more extensibility to your Excel sheet's data calculations.

Custom functions in GrapeCity Documents for Excel

The Spread.Net and SpreadJS components have always supported custom functions. With the new GcExcel Service Pack release - 1.5.0.4, we now extend this support to our Documents product line. GrapeCity Documents for Excel (GcExcel) introduces custom functions for spreadsheets in .NET Standard 2.0 targeted applications.

Please visit this help topic to get started with the basics of adding custom functions in Excel spreadsheets using GcExcel.

This tutorial solves a use case where using a custom function would be beneficial. This article will guide you through the steps of solving the problem in a .NET Core application.

Use cases for custom functions

In this example, we'll compare a family’s monthly income vs. monthly expenses. The spreadsheet calculates percentage of income that is spent on household expenses. These calculations are repeated every month with data being replaced with the next month's information.

It can be a challenge to analyze data when the list of family expenses is large, or if the data spans across multiple sheets. It can become difficult to scan through all rows to find out the highest expenses. Suppose a person wants to analyze monthly expenses to know the highest expenditure. This can be easy to solve using a standard function. We can use =MAX(B11:B23) function, which will give the highest expense for a month.

Drilling into the data, say a person wants to explore how they can reduce current expenses. The highest two expenses that month will need to be understood. You may be able to calculate this using some combinations of standard in-built functions. However, to calculate the second highest value, there may be some coding involved. In this case, it's easier to calculate using a custom function.

How to add a custom function to the spreadsheet to calculate the highest two expenses in a month.

Step 1:

In order to add the data for monthly income and monthly expenses, follow the getting started steps to create a basic spreadsheet with GcExcel. At the end of the blog, your spreadsheet will look like this:

Creating custom function with GCExcel

Step 2:

Create a class that derives from CustomFunction class.

public class HighestValues : CustomFunction
{
}

Step 3: Within the class, initialize an instance of the custom function, with the name of the function, the return type, and parameters for the custom function.

public HighestValues(): base("HighestValues", FunctionValueType.Text, new Parameter[] { new Parameter(FunctionValueType.Object), new                Parameter(FunctionValueType.Number), new Parameter(FunctionValueType.Number) })
        {

        }

Here, the name of the custom function is Highest Values. The return type for the function would be the list of item names for the expenses, so the return type would be Text value. This function will receive the parameter values for calculating the highest two values.

Step 4:

We’ll define the Evaluate function to find two highest values. This function performs some validations, such as the length of array received and the row, col values in it. Then, it adds the given array to a list of Temp objects (a class that holds the text and number values of range of cells). This list sorts the array and returns the highest two numbers.

  public override object Evaluate(object[] arguments, ICalcContext context)
    {
        if (arguments.Length < 3)
        {
            return CalcError.Value;
        }

        var values = arguments[0];
        if (values is IEnumerable<object>)
        {
            values = (values as IEnumerable<object>).FirstOrDefault();
        }
        object[,] array = values as object[,];
        if (array == null)
        {
            return CalcError.Value;
        }
        int rowCount = array.GetLength(0);
        int colCount = array.GetLength(1);
        if (rowCount <= 0 || colCount <= 0)
        {
            return CalcError.Value;
        }
        int resultCol = (int)(double)arguments[1] - 1;
        if (resultCol < 0 || resultCol >= colCount)
        {
            return CalcError.Num;
        }

        int numberCol = (int)(double)arguments[2] - 1;
        if (numberCol < 0 || resultCol >= colCount)
        {
            return CalcError.Num;
        }

        List<temp> list = new List<temp>();
        for (int i = 0; i < rowCount; i++)
        {
            string text = array[i, resultCol]?.ToString();
            double number = array[i, numberCol] is double ? (double)array[i, numberCol] : 0;
            list.Add(new temp(text, number));
        }
        list.Sort((x, y) =>
        {
            if (x.Number > y.Number)
            {
                return -1;
            }
            else if (x.Number == y.Number)
            {
                return 0;
            }
            else
            {
                return 1;
            }
        });
        string result = null;
        int count = Math.Min(list.Count, 2);
        for (int i = 0; i < count; i++)
        {
            if (result != null)
            {
                result += ",";
            }
            result += list[i].Text;
        }

        return result;
    }
 private class temp
    {
        public string Text;
        public double Number;

        public temp(string text, double number)
        {
            this.Text = text;
            this.Number = number;
        }
    }

Step 5:

In static void Main[] function, call the AddCustomFunction() method. Create new GcExcel workbook and open the Excel spreadsheet in the workbook.

Workbook.AddCustomFunction(new HighestValues());
var workbook = new Workbook();
workbook.Open("SimpleBudget.xlsx");

Step 6:

Call the HighestValues custom function and pass the range of cells from which highest values are needed. Then collect the result in a variable and set the result in a cell.

workbook.Worksheets[0].Range["B25"].Formula = "HighestValues(B11:C23, 1, 2)";
var result = workbook.Worksheets[0].Range["B25"].Value;
int rowIndex, columnIndex;           
GrapeCity.Documents.Excel.CellInfo.CellNameToIndex("C25", out rowIndex, out columnIndex);
workbook.Worksheets[0].Range[rowIndex, columnIndex].Value = result;

Step 7:

Save your workbook.

workbook.Save("SimpleBudget.xlsx");

Run the application and you will see the highest expenses collected by the custom function in cell C25.

Creating custom function with GCExcel

Note: MS Excel does not know any of our custom functions, so after saving to Excel, a #NAME error will be shown in the cell that contains the formula (B25).

Download the complete sample

How will you use this feature? Leave your comment below!

Create Custom Functions with Documents for Excel

Download the latest version of GrapeCity Documents for Excel

Download Now!