Click or drag to resize
ExcelCellFormula Property
Gets or sets cell formula string.

Namespace:  GemBox.Spreadsheet
Assembly:  GemBox.Spreadsheet (in GemBox.Spreadsheet.dll) Version: 41.3.30.1032
Syntax
public override string Formula { get; set; }

Property Value

Type: String
Remarks

Old XLS format requires all formulas to be parsed and saved to XLS files as special tokens in RPN (Reverse Polish notation). GemBox.Spreadsheet only knows how to parse limited set of formulas listed below.

New XLSX (Open XML) format stores formulas as strings and leaves formula parsing to applications that read XLSX documents. Therefore, ALL formulas are supported when writing/reading XLSX files.

Depending on ExcelFile.DelayFormulaParse property, formula string can be parsed when this property is set or when one of ExcelFile.SaveXls methods is called.

GemBox.Spreadsheet formula parser will use English culture to parse numbers.

Currently unsupported formula features are:

  • Const array
  • Array formula
  • R1C1 reference

Currently supported functions in XLS format are: ABS, ACOS, ACOSH, ADDRESS, AND, ARCTAN, AREAS, ASC, ASIN, ASINH, ATAN2, ATANH, AVEDEV, AVERAGE, AVERAGEA, BETADIST, BETAINV, BINOMDIST, CEILING, CELL, CHAR, CHIDST, CHIINV, CHOOSE, CLEAN, CODE, COLUMN, COLUMNS, COMBIN, CONCATENATE, CONFIDENCE, COS, COSH, COUNT, COUNTA, COUNTBLANK, COUNTIF, CRITBINOM, DATE, DATEDIF, DATESTRING, DATEVALUE, DAVERAGE, DAY, DAYS360, DB, DBSC, DCOUNT, DCOUNTA, DDB, DEGREES, DEVSQ, DGET, DMAX, DMIN, DOLLAR, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP, EVEN, EXACT, EXP, EXPONDIST, FACT, FDIST, FIND, FINDB, FINV, FISHER, FISHERINV, FIXED, FLOOR, FV, GAMMADIST, GAMMAINV, GAMMALN, GEOMEAN, GROWTH, HARMEAN, HLOOKUP, HOUR, HYPERLINK, HYPGEOMVERT, IF, INDEX, INDIRECT, INFO, INT, IPMT, IRR, ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISPMT, ISREF, ISTEXT, KURT, LARGE, LEFT, LEFTB, LEN, LENB, LINEST, LN, LOG, LOG10, LOGEST, LOGINV, LOOKUP, LOWER, MATCH, MAX, MAXA, MDETERM, MEDIAN, MID, MIDB, MIN, MINA, MINUTE, MINVERSE, MIRR, MNORMSINV, MOD, MODE, MONTH, N, NA, EGBINOMDIST, NORMDIST, NORMINV, NORMSDIST, NORMSDIST, NOT, NOW, NPER, NPV, NUMBERSTRING, ODD, OFFSET, OR, PERCENTILE, PERCENTRANK, PERMUT, PHONETIC, PI, PMT, POISSON, POWER, PPMT, PRODUCT, PROPER, PV, QUARTILE, RADIANS, RAND, RANK, RATE, REPLACE, REPLACEB, REPT, RIGHT, RIGHTB, ROMAN, ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS, SEARCH, SEARCHB, SECOND, SIGN, SIN, SINH, SKEW, SLN, SMALL, SQRT, STANDARDIZE, STDEV, STDEVA, STDEVP, STDEVPA, SUBSTITUTE, SUBTOTAL, SUM, SUMIF, SUMSQ, SYD, T, TAN, TANH, TDIST, TEXT, TIME, TIMEVALUE, TINV, TODAY, TRANSPOSE, TREND, TRIM, TRIMMEAN, TRUNC, TYPE, UPPER, USDOLLAR, VALUE, VAR, VARA, VARP, VARPA, VDB, VLOOKUP, WEEKDAY, WEIBULL, YEAR, ZTEST, FALSE, TRUE.

For more information on formulas, consult Microsoft Excel documentation.

Examples

Following code demonstrates how to use formulas and named ranges. It shows next features: cell references (both absolute and relative), unary and binary operators, constant operands (integer and floating point), functions and named cell ranges. At the end, code shows how to calculate worksheet formulas.

ws.Cells["A1"].Value = 5;
ws.Cells["A2"].Value = 6;
ws.Cells["A3"].Value = 10;

ws.Cells["C1"].Formula = "=A1+A2";
ws.Cells["C2"].Formula = "=$A$1-A3";
ws.Cells["C3"].Formula = "=COUNT(A1:A3)";
ws.Cells["C4"].Formula = "=AVERAGE($A$1:$A$3)";
ws.Cells["C5"].Formula = "=SUM(A1:A3,2,3)";
ws.Cells["C7"].Formula = "= 123 - (-(-(23.5)))";

ws.NamedRanges.Add("DataRange", ws.Cells.GetSubrange("A1", "A3"));
ws.Cells["C8"].Formula = "=MAX(DataRange)";

CellRange cr = ws.Cells.GetSubrange("B9", "C10");
cr.Merged = true;
cr.Formula = "=A1*25";

ws.Calculate();
See Also