Calculation Engine
GemBox.Spreadsheet supports the calculation of cell formulas with an embedded calculation engine. It can handle everything from simple mathematical operations to complex functions.
Formula calculation
The engine calculates formulas stored in the ExcelCell.Formula property. The format is the same as that in Microsoft Excel. A formula has to start with '=' and can include operators, functions and references. Function parameters are separated by ','.
Once a formula has been entered, a method ExcelCell.Calculate() has to be called to calculate its value and, if successful, the result will be stored as ExcelCell.Value.
Since the GemBox.Spreadsheet calculation engine will not calculate methods automatically, you need to call one of the following methods:
- ExcelCell.Calculate() - Calculates a single ExcelCell formula and its references, if any.
- ExcelWorksheet.Calculate() - Calculates all of the cells in a single ExcelWorksheet.
- ExcelFile.Calculate() - Calculates all the cells in an ExcelFile.
Iterative calculation
GemBox.Spreadsheet also supports iterative calculations for formulas where some cells refer to themselves directly or indirectly (circular reference). By default, it is disabled, but can be enabled by setting CalculationOptions.EnableIterativeCalculation to true.
If enabled, the calculation engine will iterate through formula(s) until the number of iterations reaches CalculationOptions.MaximumIterations, or the value difference between the previous and the current calculated cell value is less than CalculationOptions.MaximumChange value.
Supported functions
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
- COLUMNS
- COMBIN
- CONCAT
- CONCATENATE
- CONFIDENCE
- CONFIDENCE.NORM
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTBLANK
- COUNTIF
- COUNTIFS
- DATE
- DATEDIF
- DATEVALUE
- DAY
- DAYS
- DAYS360
- DEGREES
- DOLLAR
- EDATE
- EOMONTH
- EVEN
- EXACT
- EXP
- FACT
- FALSE
- FIND
- FILTER
- 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
- ROWS
- RSQ
- SEARCH
- SECOND
- SEQUENCE
- 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