Excel formulas

The following example shows how to read and write Excel formulas using GemBox.Spreadsheet in C# and VB.NET.

using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("Formulas");

        worksheet.Rows[0].Style = workbook.Styles[BuiltInCellStyleName.Heading1];
        worksheet.Columns[0].Width = 9 * 256;
        worksheet.Columns[1].Width = 36 * 256;
        worksheet.Columns[2].Width = 18 * 256;

        worksheet.Cells[0, 0].Value = "Data";
        worksheet.Cells[0, 1].Value = "Formula";
        worksheet.Cells[0, 2].Value = "Result";

        // Add sample data values.
        worksheet.Cells["A2"].Value = 3;
        worksheet.Cells["A3"].Value = 4.1;
        worksheet.Cells["A4"].Value = 5.2;
        worksheet.Cells["A5"].Value = 6;
        worksheet.Cells["A6"].Value = 7;

        // Add named range.
        worksheet.NamedRanges.Add("MyRange1", worksheet.Cells.GetSubrange("A2:A6"));

        // Sample formulas.
        string[] formulas =
        {
            "=NOW()+123",
            "=MINUTE(0.5)-1343/35",
            "=HOUR(56)-23/35",
            "=YEAR(DATE(2020,1,1)) + 12",
            "=MONTH(3)-2342/235345",
            "=RAND()",
            "=TEXT(\"text\", \"$d\")",
            "=VAR(1,2)",
            "=MOD(1,2)",
            "=NOT(FALSE)",
            "=AND(TRUE)",
            "=TRUE()",
            "=VALUE(3)",
            "=LEN(\"hello\")",
            "=MID(\"hello\",1,1)",
            "=ROUND(1,2)",
            "=SIGN(-2)",
            "=INT(3)",
            "=ABS(-3)",
            "=LN(2)",
            "=EXP(4)",
            "=SQRT(2)",
            "=PI()",
            "=COS(4)",
            "=MAX(1,2)",
            "=MIN(1,2)",
            "=AVERAGE(1,2)",
            "=SUM(1,3)",
            "=IF(1,2,3)",
            "=COUNT(1,2,3)",
            "=SUBTOTAL(1,A2:A4)",                           // Function with cells range.
            "=SUM(MyRange1)",                               // Function with named range.
            "=COUNT(1,  ,  ,,,2, 23,,,,,, 34,,,54,,,,  ,)", // Function with miss argument.
            "=cOs( 1 )",                                    // Functions with different letters case.
            "=+++5",                                        // Unary operators.
            "=(1)-(2)+(3/2+34)/2+12232-32-4",               // Binary operators.
            "=TRUE",                                        // Operand tokens, bool.
            "=20",                                          // Operand tokens, int.
            "=2235.5132",                                   // Operand tokens, num.
            "=\"hello world!\"",                            // Operand tokens, str.
            "=#NULL!"                                       // Operand tokens, error.
        };

        // Write formulas to Excel cells.
        for (int i = 0; i < formulas.Length; i++)
        {
            string formula = formulas[i];
            worksheet.Cells[i + 1, 1].Value = formula;
            worksheet.Cells[i + 1, 2].Formula = formula;
        }

        workbook.Save("Formulas.%OutputFileType%");
    }
}
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook As New ExcelFile()
        Dim worksheet = workbook.Worksheets.Add("Formulas")

        worksheet.Rows(0).Style = workbook.Styles(BuiltInCellStyleName.Heading1)
        worksheet.Columns(0).Width = 9 * 256
        worksheet.Columns(1).Width = 36 * 256
        worksheet.Columns(2).Width = 18 * 256

        worksheet.Cells(0, 0).Value = "Data"
        worksheet.Cells(0, 1).Value = "Formula"
        worksheet.Cells(0, 2).Value = "Result"

        ' Add sample data values.
        worksheet.Cells("A2").Value = 3
        worksheet.Cells("A3").Value = 4.1
        worksheet.Cells("A4").Value = 5.2
        worksheet.Cells("A5").Value = 6
        worksheet.Cells("A6").Value = 7

        ' Add named range.
        worksheet.NamedRanges.Add("MyRange1", worksheet.Cells.GetSubrange("A2:A6"))

        ' Sample formulas.
        Dim formulas As String() =
        {
            "=NOW()+123",
            "=MINUTE(0.5)-1343/35",
            "=HOUR(56)-23/35",
            "=YEAR(DATE(2020,1,1)) + 12",
            "=MONTH(3)-2342/235345",
            "=RAND()",
            "=TEXT(""text"", ""$d"")",
            "=VAR(1,2)",
            "=MOD(1,2)",
            "=NOT(FALSE)",
            "=AND(TRUE)",
            "=TRUE()",
            "=VALUE(3)",
            "=LEN(""hello"")",
            "=MID(""hello"",1,1)",
            "=ROUND(1,2)",
            "=SIGN(-2)",
            "=INT(3)",
            "=ABS(-3)",
            "=LN(2)",
            "=EXP(4)",
            "=SQRT(2)",
            "=PI()",
            "=COS(4)",
            "=MAX(1,2)",
            "=MIN(1,2)",
            "=AVERAGE(1,2)",
            "=SUM(1,3)",
            "=IF(1,2,3)",
            "=COUNT(1,2,3)",
            "=SUBTOTAL(1,A2:A4)",                           ' Function with cells range.
            "=SUM(MyRange1)",                               ' Function with named range.
            "=COUNT(1,  ,  ,,,2, 23,,,,,, 34,,,54,,,,  ,)", ' Function with miss argument.
            "=cOs( 1 )",                                    ' Functions with different letters case.
            "=+++5",                                        ' Unary operators.
            "=(1)-(2)+(3/2+34)/2+12232-32-4",               ' Binary operators.
            "=TRUE",                                        ' Operand tokens, bool.
            "=20",                                          ' Operand tokens, int.
            "=2235.5132",                                   ' Operand tokens, num.
            "=""hello world!""",                            ' Operand tokens, str.
            "=#NULL!"                                       ' Operand tokens, error.
        }

        ' Write formulas to Excel cells.
        For i = 0 To formulas.Length - 1
            Dim formula As String = formulas(i)
            worksheet.Cells(i + 1, 1).Value = formula
            worksheet.Cells(i + 1, 2).Formula = formula
        Next

        workbook.Save("Formulas.%OutputFileType%")

    End Sub
End Module
Reading and writing Excel cell formulas
Screenshot of reading and writing Excel cell formulas

Calculate Excel formulas

The following example shows how you can calculate Excel cell formulas using GemBox.Spreadsheet's calculation engine.

using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("Formula Calculation");

        // Some formatting.
        var row = worksheet.Rows[0];
        row.Style.Font.Weight = ExcelFont.BoldWeight;

        var column = worksheet.Columns[0];
        column.SetWidth(250, LengthUnit.Pixel);
        column.Style.HorizontalAlignment = HorizontalAlignmentStyle.Left;
        column = worksheet.Columns[1];
        column.SetWidth(250, LengthUnit.Pixel);
        column.Style.HorizontalAlignment = HorizontalAlignmentStyle.Right;

        // Use first row for column headers.
        worksheet.Cells["A1"].Value = "Formula";
        worksheet.Cells["B1"].Value = "Calculated value";

        // Enter some Excel formulas as text in first column.
        worksheet.Cells["A2"].Value = "=1 + 1";
        worksheet.Cells["A3"].Value = "=3 * (2 - 8)";
        worksheet.Cells["A4"].Value = "=3 + ABS(B3)";
        worksheet.Cells["A5"].Value = "=B4 > 15";
        worksheet.Cells["A6"].Value = "=IF(B5, \"Hello world\", \"World hello\")";
        worksheet.Cells["A7"].Value = "=B6 & \" example\"";
        worksheet.Cells["A8"].Value = "=CODE(RIGHT(B7))";
        worksheet.Cells["A9"].Value = "=POWER(B8, 3) * 0.45%";
        worksheet.Cells["A10"].Value = "=SIGN(B9)";
        worksheet.Cells["A11"].Value = "=SUM(B2:B10)";

        // Set text from first column as second row cell's formula.
        int rowIndex = 1;
        while (worksheet.Cells[rowIndex, 0].ValueType != CellValueType.Null)
            worksheet.Cells[rowIndex, 1].Formula = worksheet.Cells[rowIndex++, 0].StringValue;

        // GemBox.Spreadsheet supports single Excel cell calculation, ...
        worksheet.Cells["B2"].Calculate();

        // ... Excel worksheet calculation,
        worksheet.Calculate();

        // ... and whole Excel file calculation.
        worksheet.Parent.Calculate();

        workbook.Save("Formula Calculation.%OutputFileType%");
    }
}
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook As New ExcelFile()
        Dim worksheet = workbook.Worksheets.Add("Formula Calculation")

        ' Some formatting.
        Dim row = worksheet.Rows(0)
        row.Style.Font.Weight = ExcelFont.BoldWeight

        Dim column = worksheet.Columns(0)
        column.SetWidth(250, LengthUnit.Pixel)
        column.Style.HorizontalAlignment = HorizontalAlignmentStyle.Left
        column = worksheet.Columns(1)
        column.SetWidth(250, LengthUnit.Pixel)
        column.Style.HorizontalAlignment = HorizontalAlignmentStyle.Right

        ' Use first row for column headers.
        worksheet.Cells("A1").Value = "Formula"
        worksheet.Cells("B1").Value = "Calculated value"

        ' Enter some Excel formulas as text in first column.
        worksheet.Cells("A2").Value = "=1 + 1"
        worksheet.Cells("A3").Value = "=3 * (2 - 8)"
        worksheet.Cells("A4").Value = "=3 + ABS(B3)"
        worksheet.Cells("A5").Value = "=B4 > 15"
        worksheet.Cells("A6").Value = "=IF(B5, ""Hello world"", ""World hello"")"
        worksheet.Cells("A7").Value = "=B6 & "" example"""
        worksheet.Cells("A8").Value = "=CODE(RIGHT(B7))"
        worksheet.Cells("A9").Value = "=POWER(B8, 3) * 0.45%"
        worksheet.Cells("A10").Value = "=SIGN(B9)"
        worksheet.Cells("A11").Value = "=SUM(B2:B10)"

        ' Set text from first column as second row cell's formula.
        Dim rowIndex As Integer = 0
        While worksheet.Cells(rowIndex, 0).ValueType <> CellValueType.Null
            worksheet.Cells(rowIndex, 1).Formula = worksheet.Cells(rowIndex, 0).StringValue
            rowIndex += 1
        End While

        ' GemBox.Spreadsheet supports single Excel cell calculation, ...
        worksheet.Cells("B1").Calculate()

        ' ... Excel worksheet calculation,
        worksheet.Calculate()

        ' ... and whole Excel file calculation.
        worksheet.Parent.Calculate()

        workbook.Save("Formula Calculation.%OutputFileType%")

    End Sub
End Module

GemBox.Spreadsheet also supports advanced calculation features such as iterative calculation and dynamic array formulas. Please refer to our Calculation article for more information.

Here is a list of functions currently supported in GemBox.Spreadsheet:

ABS, ACOS, ACOSH, ACOT, ACOTH, ADDRESS, ANCHORARRAY, AND, ASIN, ASINH, ATAN, ATAN2, ATANH, AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS, CEILING, CEILING.PRECISE, CHAR, CHOOSE, CLEAN, CODE, COLUMN, COMBIN, CONCAT, CONCATENATE, COS, COSH, COT, COTH, CONFIDENCE, CONFIDENCE.NORM, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, DATE, DATEDIF, DATEVALUE, DAY, DAYS, DAYS360, DEGREES, DOLLAR, EDATE, EOMONTH, EVEN, EXACT, EXP, FACT, FALSE, FILTER, FIND, FIXED, FLOOR, FORECAST, FORECAST.LINEAR, FREQUENCY, FV, GETPIVOTDATA, HLOOKUP, HOUR, HYPERLINK, IF, IFERROR, IFNA, IFS, INDEX, INDIRECT, INT, INTERCEPT, IPMT, IRR, ISBLANK, ISERR, ISERROR, ISEVEN, ISFORMULA, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD, ISREF, ISTEXT, LARGE, LEFT, LEN, LINEST, LN, LOG, LOG10, LOOKUP, LOWER, MATCH, MAX, MAXA, MAXIFS, MEDIAN, MID, MIN, MINA, MINIFS, MINUTE, MOD, MODE, MONTH, MROUND, N, NA, NETWORKDAYS, NORMDIST, NORMSDIST, NOT, NOW, NPV, NUMBERVALUE, ODD, OFFSET, OR, PEARSON, PI, PMT, POWER, PPMT, PRODUCT, PROPER, QUOTIENT, RADIANS, RAND, RANDARRAY, RANDBETWEEN, RANK, RANK.AVG, RANK.EQ, RATE, REPLACE, REPT, RIGHT, ROUND, ROUNDDOWN, ROUNDUP, ROW, RSQ, SEARCH, SECOND, SIGN, SIN, SINGLE, SINH, SLOPE, SMALL, SORT, SORTBY, SQRT, STDEV, STDEVA, STDEVP, STDEVPA, SUBSTITUTE, SUBTOTAL, SUM, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, SWITCH, T, T.DIST, TAN, TANH, TEXT, TEXTJOIN, TEXTSPLIT, TIME, TIMEVALUE, TODAY, TRANSPOSE, TRIM, TRUE, TRUNC, TYPE, UNICODE, UNIQUE, UPPER, VALUE, VAR, VARA, VARP, VARPA, VLOOKUP, WEEKDAY, WEEKNUM, XIRR, XLOOKUP, XMATCH, YEAR, YEARFRAC.

Note that we are continuously adding more formulas based on user feedback. You can send your request for a feature or formula here.

See also


Next steps

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy