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 calculationThe 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 calculationGemBox.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 functionsHere is a list of functions currently supported in GemBox.Spreadsheet:
ABS
ACOS
ACOSH
ACOT
ACOTH
ADDRESS
AND
ASIN
ASINH
ATAN
ATAN2
ATANH
AVEDEV
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
CEILING
CEILING.PRECISE
CHAR
CHOOSE
CLEAN
CODE
COLUMN
COMBIN
CONCATENATE
COS
COSH
COT
COTH
COUNT
COUNTA
COUNTBLANK
COUNTIF
COUNTIFS
DATE
DATEDIF
DATEVALUE
DAY
DAYS
DEGREES
EDATE
EOMONTH
EVEN
EXACT
EXP
FACT
FALSE
FIND
FIXED
FLOOR
HLOOKUP
HOUR
IF
IFERROR
INDEX
INDIRECT
INT
IRR
ISBLANK
ISERR
ISERROR
ISEVEN
ISFORMULA
ISLOGICAL
ISNA
ISNONTEXT
ISNUMBER
ISODD
ISREF
ISTEXT
LARGE
LEFT
LEN
LN
LOG
LOG10
LOOKUP
LOWER
MATCH
MAX
MAXA
MID
MIN
MINA
MINUTE
MEDIAN
MOD
MODE
MONTH
N
NA
NORMSDIST
NOT
NOW
NPV
ODD
OR
PI
PMT
POWER
PRODUCT
QUOTIENT
RADIANS
RAND
RANDBETWEEN
RANK
RANK.AVG
RANK.EQ
RATE
REPLACE
REPT
RIGHT
ROUND
ROUNDDOWN
ROUNDUP
ROW
SEARCH
SECOND
SIGN
SIN
SINH
SQRT
STDEV
STDEVA
STDEVP
STDEVPA
SUBSTITUTE
SUBTOTAL
SUM
SUMIF
SUMIFS
SUMPRODUCT
SUMSQ
T
TAN
TANH
TEXT
TIME
TIMEVALUE
TODAY
TRIM
TRUE
TRUNC
TYPE
UPPER
VALUE
VAR
VARA
VARP
VARPA
VLOOKUP
WEEKDAY
WEEKNUM
XIRR
XLOOKUP
YEAR
YEARFRAC