Click or drag to resize
CellRangeFormula Property
Gets or sets formula string.

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

Property Value

Type: String
Exceptions
ExceptionCondition
InvalidOperationExceptionProperty get is attempted on a cell range which is not merged.
Remarks

GemBox.Spreadsheet can read and write formulas, but cannot calculate formula results. However, when you open a file in MS Excel, formula results will be calculated automatically.

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 supported formula features by GemBox.Spreadsheet XLS formula parser are:

  • Named cell
  • Named range
  • Absolute cell/range
  • Relative cell/range
  • Functions( partly, see the list of supported functions below )
  • Missed argument
  • Unary operator
  • Binary operator
  • Parentheses
  • 3d cell reference
  • 3d cell range reference
  • Boolean
  • Integer
  • Float
  • String
  • Error

Currently unsupported formula features are:

  • Const array
  • Array formula
  • R1C1 reference

Currently supported functions 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.

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";
See Also