Calculations - editable cell formulas

TreeGrid documentation

This document describes EDITABLE formulas entered by users directly into cells.
To read about NOT EDITABLE formulas entered by developers into data see Cell data formulas.
When calculating the grid, the editable formulas are calculated independently after the not editable formulas.

If set FormulaEditing="1", a user can enter formula in given cell(s) by starting the value by "=", like in Excel.
When entered the value starting by "=", the cell EFormula is updated and calculated and the cell value gets the formula result.

Strings
Strings are written in quotes, there can be used both " or '. To place the quote to string, double it, e.g. "aaa""bbb" => aaa"bbb. All other characters, including '\' have standard meaning.

Numbers
Numbers are written as they are in digits 0 - 9, optionally with decimal separator.

Dates
Date constants are not accepted. They must be created by formulas like date() from string or separate arguments.

Operators
Default operators use standard C++/JavaScript syntax:
Arithmetic: + (add, both numbers and strings), - (subtract), * (multiply), / (divide), % (modulo)
Logical: && (logical and), || (logical OR), ! (logical not)
Bit: & (bit AND), | (bit OR), ^ (bit XOR), ~ (bit negate), << (bit shift left), >> (bit shift right)
Compare: == (equals), != (not equals), < (less), <= (less or equal), > (greater), >= (greater or equal)
Condition: ?: (three arguments, condition?result_true:result_false)
Special MS Excel operators: = (equals), <> (not equals), ^ (power), & (concatenate strings), % (percent, = *100), ! (external reference)
The operators ^ & % ! have different meaning in MS Excel and JavaScript, by default since 13.0 the MS Excel meaning is used. It can be changed in <Lang><FormulaOperators/><Lang>.
Priority of operators is the same as in JavaScript and cannot be changed. You can always use parenthesis ( ) to change the priority.
All the operators are defined in <Lang><FormulaOperators/><Lang>, it is possible to modify, add or delete the operators.

Constants
There are defined constants: pi (3.14), ln2 (ln(2)), ln10 (ln(10)), log2e (log2(e)), log10e (log10(e)), sqrt2 (sqrt(2)), sqrt1_2 (1/sqrt(2)), null (nothing).
All the constants are defined in <Lang><FormulaConstants/><Lang>, it is possible to modify, add or delete the constants.

Cell references
In the formulas there can be addressed any the grid cell as Row.Name (or Row.id) + Col.SearchNames (or column name).
For example, <C Name="AB"/> <C Name="BC"/> <C Name="XY" ... <I id="20" XY="=AB20+BC20" />

Cell range references
In the formulas there can be addressed any range of cells in grid as CornerCell : OppositeCornerCell or Column1 : Column2 or Row1 : Row2
For example, <C Name="AB"/> <C Name="BC"/> <C Name="XY" ... <I id="20" XY="=sum(AB5:BC19,1:3)+AB20+100" />
The range separator : is defined by FormulaRangeSeparator.
Since 13.0 It is possible to get intersection of cell ranges by operator space, e.g. XY="=sum(A1:D5 A2:D8)". The operator space is defined by FormulaIntersectionSeparator.

External references
It is possible to refer also cells or cells ranges in other sheet in the same xlsx by "!" operator. Since 14.0 the sheet name is case insensitive.
If the sheet name contains spaces, it must be quoted. For example XY='="Sheet 1"!A1:A2'.
External references to other xlsx files are not supported!

Functions
It is possible to use more than 200 predefined functions.
The functions have MS Excel English names and syntax and are completely different from the data (not editable) formula functions, although they can have the same name!
For example sum function in data usage and in editable usage.
In the function arguments it is possible to pass one string in "" or number as is, one cell value as R1C1, cell values range as R1C1:R2C2, the whole column(s) as C1:C2, the whole row(s) as R1:R2 or any function call.
The arguments in the function call are separated by comma, it can be changed by FormulaValueSeparator.
For example, <C Name="AB"/> <C Name="BC"/> <C Name="XY" ... <I id="20" XY="=sum(AB5:BC19,1:3)+AB20+100" />
See the Functions list

Localization
The values in formula during editing are localized, they are affected by <Format> settings like DecimalSeparator and InputDecimalSeparators and formula special locale settings like FormulaValueSeparator, FormulaRangeSeparator, FormulaIntersectionSeparator and FormulaPrefix.
For example if the system has set comma as decimal separator, the decimal numbers in the formula show comma. In this case the function parameter separator FormulaValueSeparator should be set to semicolon.
The function names can be localized (translated) in <Lang><FormulaFunctions /></Lang, for example <Lang><FormulaFunctions sum="localsum" /></Lang>
The input and output xml/json data can contain localized or English formulas according to FormulaLocal setting, default is English.

White spaces
All white spaces are automatically removed from formula, except from strings.

Compatibility with MS Excel 2016
The editable formulas are compatible with MS Excel formula syntax and function names.
The MS Excel array formulas are not implemented yet.
The external references to other workbook are not implemented. Note, references to other sheets are supported.
TreeGrid does not differ between error and N/A.
TreeGrid Auto type does not recognize special boolean type, as boolean there used values 0 and 1, although constants and functions TRUE and FALSE are available.
There are implemented 243 more frequent MS Excel functions. But next 201 less frequent and special MS Excel functions are not implemented yet, they can be implemented as custom functions or on request:
Other: info, error.type, bahttext, getpivotdata, indirect, hyperlink, rtd
Date: workday, workday.intl, days360, networkdays, networkdays.intl, yearfrac
Matrix: minverse, mmult, munit, arabic, roman
Trend line: forecast.ets, forecast.ets.confint, forecast.ets.seasonality, forecast.ets.stat, trend, growth, logest
Confidence: confidence, confidence.norm, confidence.t
Distribution: betadist, beta.dist, betainv, beta.inv, binomdist, binom.dist, binom.dist.range, negbinomdist, negbinom.dist, critbinom, binom.inv, chdist, chisq.dist.rt, chisq.dist, chiinv, chisq.inv.rt, chisq.inv, chitest, chisq.test, correl, expondist, expon.dist, fdist, f.dist.rt, f.dist, finv, f.inv.rt, f.inv, fisher, fisherinv, ftest, f.test, gammadist, gammainv, gamma.inv, gamma, gammaln, gammaln.precise, gauss, hypgeomdist, hypgeom.dist, kurt, lognormdist, lognorm.dist, loginv, lognorm.inv, normdist, norm.dist, norminv, norm.inv, normsdist, norm.s.dist, normsinv, norm.s.inv, pearson, rsq, phi, poisson, poisson.dist, prob, skew,skew.p, standardize, tdist, t.dist.2t, t.dist.rt, t.dist, tinv, t.inv.2t, t.inv, ttest, t.test, weibull, weibull.dist, ztest, z.test.
Database: daverage, dcount, dcounta, dget, dmax, dmin
Financial: fv, fvschedule, npv, pv, received, xnpv, disc, price, pricedics, pricemat, oddfprice, oddlprice, tbillprice, irr, mirr, xirr, amordegrc, amorlinc, db, ddb, sln, syd, vdb, dollarde, dollarfr, cumipmt, cumprinc, ipmt, ispmt, pmt, ppmt, accrint, accrintm, effect, intrate, nominal, rate, rri, yield, yielddisc, yieldmat, oddfyield, oddlyield, tbilleq, tbillyield, coupdaybs, coupdays, coupdaysnc, coupncd, coupnum, couppcd, duration, mduration, nper, pduration,
Bessel: besseli, besselj, besselk, bessely
Error: erf, erf.precise, erfc, erfc.precise
Complex: complex, imabs, imaginary, imargument, imconjugate, imcos, imcosh, imcot, imcsc, imcsch, imdiv, imexp, imln, imlog10, imlog2, impower, improduct, imreal, imsec, imsech, imsin, imsinh, imsqrt, imsub, imsum, imtan
Cube: cubekpimember, cubemember, cubememberproperty, cuberankedmember, cubeset, cubesetcount, cubevalue
Web: encodeurl, filterxml, webservice

If set, the grid supports calculated formulas in cells. The formulas can be entered into cell by editing. Or in input data in the cell value with '=' or in EFormula cell attribute.
If set to 1, the formula is uploaded in cell value with '=' and the formula result is not uploaded.
If set to 2, the formula is uploaded in EFormula attribute (without '=') and the formula result in the cell value. Set also <treegrid>/<bdo> Upload_Attrs="*EFormula,...." to upload the EFormula attribute.
If and how the cells can be referenced relatively to the formula source cell.

0
The cell references are only absolute, they refer to exact row and column and don't change after the row or column is moved or deleted.
All the cell references are set as ColRow or RowCol (e.g. A1 or A1:B5).
The row is identified by row Name, value in NameCol or row id.
Column is identified by any value in its SearchNames or the column Name.

1 or 2
The cell references are relative to the cell with the formula or absolute.
The absolute reference is with "$" prefix, it can be changed by FormulaAbsolute.
For example "A1:B3" is relative, "$A$1:$B$3" is absolute, "$A1:$B3" is absolute column, relative row, "A$1:B$3" is relative column, absolute row.
Both the absolute and relative references are updated when the row or column is moved or deleted.
The relative references are updated The rows and columns are identified by their position, the rows by value in RowIndex, the columns by value in ColIndex.
If the RowIndex is not defined, it is automatically added as new column named "Index".
If the ColIndex is not defined, it is automatically set to the main Header row and defined with ColIndexChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".

1

The cell references are the same (ColRow / $Col$Row) also in input and output data.

2

The cell references in input and output data are in special format: RxCx for absolute reference where x is row or column position from the top left corner from 1. R[x]C[x] for relative. Where x is the cell position relative to the cell with formula, 1 means next cell, -1 means previous cell. RC for the same row or column. The references can be mixes, e.g. R1C means the first row in the same column, R[-2]C5 means two rows above the row and the fifth column. It counts only rows / columns with RowIndex / ColIndex set. It counts rows / cols regardless on FormulaCanUse value.

Since 15.0 If loading xlsx file, FormulaRelative is always set to 1. Prior 15.0 it was set to 2.
If the localized functions, operators and constants will be stored also in input and output XML / JSON data and in EFormula for API.
If loading xlsx file, FormulaLocal is always set to 0.

1.bit (&1)	If the cell gets an Error attribute with the formula result error
2.bit (&2)	If the formula result values are restricted by cell limit attributes. Checks Size, EditMask, ResultMask and Enum values
3.bit (&4)	If the NaN / Infinity results are restricted. Otherwise it is converted to empty string or zero according to the cell type
4.bit (&8)	If the null result is restricted. Otherwise it is converted to empty string or zero according to the cell type
5.bit (&16)	If the new error messages are shown after formula change

If set to 0, the operator + can be used also to concatenate strings. Using + between string and number will result to string! Including empty strings (cells)!
If set to 1, the operator + is used to add up numbers. If the operator + is used for not empty string, it leads to NaN. If used for empty string (cell), it uses it as 0.
If the cell or cells in row or column can accept its Formula by entering its value starting with "=".
Since 15.0 if set to 2 the formula can be edited also if the grid has locked formulas by Locked="formula".
When importing / exporting xlsx, the value 1 is read / set as hidden formulas, therefore for xlsx manipulation the default value of FormulaCanEdit should be set to 2.
If the row or column can be used as source for editable formulas.
Default value is 0 for solid space rows, Panel and Gantt columns and 1 for all other rows and columns. Since 13.0 all the formula items except strings are case insensitive.
How the cells can be addressed in editable formula and letter case in formula items, bit array:

1.bit (&1)	Order in cell reference: 0 - ColRow, 1 - RowCol
2.bit (&2)	If the order in cell reference is strict. 0 - both the orders can be used in input and the result is converted to selected order, 2 - only selected order can be entered
3.bit (&4)	(changed 13.0) Letter case conversion of formula items (cell references, functions, operators, constants) for editing. 0 - to upper case, 4 - to lower case.
4.bit (&8)	If permits spaces in function or cell names. If permitted, textual operators (like AND) are restricted.
5.bit (&16)	(new 13.0) Letter case conversion of formula items (cell references, functions, operators, constants) for data. 0 - to upper case, 16 - to lower case.

The Row is RowIndex value if set FormulaRelative, otherwise it is Name attribute, or if it is not set, the row cell value in NameCol or if it is not set, the row id attribute.
The Col is ColIndex value if set FormulaRelative, otherwise it is one of the items in column SearchNames attribute, or if it is not set, the column Name attribute. How the formula iterates rows and columns, bit array.

1.bit (&1)	Include deleted rows and columns.
2.bit (&2)	Include filtered rows and columns.
3.bit (&4)	Include manually hidden rows and columns
4.bit (&8)	(new 13.0) Include left fixed columns
5.bit (&16)	(new 13.0) Include right fixed columns
6.bit (&32)	(new 13.0) Include fixed rows (only Kind="Data" and without ColIndex)

If loading xlsx file, FormulaType has automatically added 56 = 8+16+32.
How the circular references between cells will be checked and reported.
0 - permit, 1 - permit and mark by Error, 2 - restrict and cancel editing, 3 - ask 0 or 2, 4 - ask 1 or 2, 5 - 1 with alert, 6 - 2 with alert, 7 - (new 13.3) restrict and continue editing, 8 (new 13.3) 7 with alert. Maximum calculated cells when testing one formula for circular dependencies.
Maximal time that can be spent by recalculating sheet, in milliseconds.
If set to 1, it marks as changed all cells and rows changed by editable formula result.
If set to 2, it marks as changed also the cell calculated for first time if resulted to different value.
In the first cell calculation it stores the calculated value. If some next calculation returns different result the stored one, it marks the cell changed.
If the next calculation returns the same result as the first stored result, the changed flag is cleared.
It will also upload these changes to server.
If set, tries to add ending parenthesis to incorrect formula. If cell formula is shown as cell tip. If all the editable formulas are displayed in their cells instead of their results. If edited cell formula colors cell ranges in different colors in the formula text and also in grid. Comma separated list of HTML colors to color the cell ranges in edited formula, if set FormulaColorRanges.
Comma separated list of HTML colors to color the cell ranges in grid, if set FormulaColorRanges.
If set, continues editing cell with editable formula also when switching to another sheet and permits choosing cells from this sheet by dragging or click.
The formula in cell starts by this string. This string separates bounds in cell range like C5:D8. This string separates arguments in formula function.
It should be especially changed when changed DecimalSeparator to comma. This string separates intersection of cell ranges like C5:D8 A1:D4. Default is space. The absolute reference starts by this string, like A1:B2 (relative), $A$1:$B$2 (absolute), $A1:$B2 (absolute col, relative row), A$1:B$2 (relative col, absolute row). Cell edit formula (without the '='). It can be set in input data or it can be read by API Get(row,col+"EFormula");
Or it can be written to output data if set FormulaEditing=2.
Called on calculate editable formula in given cell. The value is the formula result to be placed to the cell.
Return null or value to not change it. Or return new cell value. Or return oldval to not set the value to cell.
The formula is actually calculated formula in string.
For errors see OnEditErrors API event. Returns cell name for formula, e.g. A1 or B3.
Returns the name according to the FormulaRelative and FormulaNames settings.
Returns cell value as string for editing. If the cell contains EFormula, it returns the EFormula starting by '='.
Sets the cell value like it was entered in cell editing, if the value starts by '=', it sets cell EFormula.
It starts editing, puts the value to the cell and finishes editing. So by default it is called in asynchronously in setTimeout.
To call it synchronously set timeout = 0, in this case it must not be called from TreeGrid event handler, especially not from OnEndEdit.
Sets 2 to cell attribute FormulaCanEdit in actual or focused cell or in all focused or selected cells or rows or columns.
Sets 0 to cell attribute FormulaCanEdit in actual or focused cell or in all focused or selected cells or rows or columns.
Sets or default value 1 to cell attribute FormulaCanEdit in actual or focused cell or in all focused or selected cells or rows or columns.

Suggest list (auto complete)

Editable formulas have their own suggest list definition. The definition is similar to standard cell Suggest list, the attributes have just the "Formula" prefix.
The cell can have defined different standard and editable formula suggest lists. For more information see the cell Suggest list.
A list o values to suggest for editable formula, when the edited value starts by '='. Format is the same as Suggest.
Or it can be set to number to generate the suggest list for all available functions.
1 - generates and shows only function names, 2 - generates function name plus open brace, 4 - shows function names with braces, 6 = 2+4. Various settings for FormulaSuggest menu for editable formula. Format is the same as SuggestType. Delay before the suggest formula menu is shown. If next key is pressed within this time, the menu is not shown for the previous text. Minimal count of characters in text to show the suggest formula menu for the text. Sets regex for all characters that separate the formula function names. Has the same format as SuggestSeparator
By default it is set to all operator characters.

Defined names

The defined names can be used in editable formulas as a reference of value, cell, cell range or any other formula part.
Defines one name. Can be used only in root tag <Names>.
Name of the defined name. It can contain only letters and digits and underscore and dot. In formula it is used case insensitive.
If there are two names with the same Name, the global one is accessible with "!" prefix in the formulas.
The value of the defined name. It can contain anything that can be placed to editable formula. It replaces the Name in editable formulas as is.
Used in import / export to xlsx. Global names are available for all sheets, not local names only the one sheet.
It is permitted to define local and global names with the same Name.
Defines new local name for actual / focused / selected cells / rows / columns.
Lets a user to enter the name's Name.
Defines new Global name for actual / focused / selected cells / rows / columns.
Lets a user to enter the name's Name.
Shows a list of all defined names in grid and lets a user to enter new Value for chosen name.
If entered empty string, the name is deleted.
Shows a list of all defined names in grid and lets a user to delete chosen name.
Changes attributes (Name,Value,Global) of name NewName to parameters in name OldName.
If NewName is null, deletes OldName. If OldName is null, adds the NewName.
If set undo, adds the action to the undo buffer.
If set recalc, recalculates all editable formulas.

Actions for choosing cells

Can be called only during editing formula to let a user to select range of cells by dragging. If there is already some cell ranges in edit on cursor position, places the selected range after them. Can be called only during editing formula to let a user to select range of cells by dragging. If there is already some cell ranges in edit on cursor position, replaces the one range by the selected range. Can be called only during editing formula to let a user to select range of cells by dragging. If there is already some cell ranges in edit on cursor position, replaces all adjacent cell ranges by the selected range. Can be called only during editing formula to let a user to select range of rows by dragging. If there is already some cell ranges in edit on cursor position, places the selected range after them. Can be called only during editing formula to let a user to select range of rows by dragging. If there is already some cell ranges in edit on cursor position, replaces the one range by the selected range. Can be called only during editing formula to let a user to select range of rows by dragging. If there is already some cell ranges in edit on cursor position, replaces all adjacent cell ranges by the selected range. Can be called only during editing formula to let a user to select range of columns by dragging. If there is already some cell ranges in edit on cursor position, places the selected range after them. Can be called only during editing formula to let a user to select range of columns by dragging. If there is already some cell ranges in edit on cursor position, replaces the one range by the selected range. Can be called only during editing formula to let a user to select range of columns by dragging. If there is already some cell ranges in edit on cursor position, replaces all adjacent cell ranges by the selected range. Can be called only during editing formula to let a user to select one cell by clicking. If there is already some cell ranges in edit on cursor position, places the selected cell after them. Can be called only during editing formula to let a user to select one cell by clicking. If there is already some cell ranges in edit on cursor position, replaces the one range by the selected cell. Can be called only during editing formula to let a user to select one cell by clicking. If there is already some cell ranges in edit on cursor position, replaces all adjacent cell ranges by the selected cell. Can be called only during editing formula to let a user to select one row by clicking. If there is already some cell ranges in edit on cursor position, places the selected cell after them. Can be called only during editing formula to let a user to select one row by clicking. If there is already some cell ranges in edit on cursor position, replaces the one range by the selected cell. Can be called only during editing formula to let a user to select one row by clicking. If there is already some cell ranges in edit on cursor position, replaces all adjacent cell ranges by the selected cell. Can be called only during editing formula to let a user to select one column by clicking. If there is already some cell ranges in edit on cursor position, places the selected cell after them. Can be called only during editing formula to let a user to select one column by clicking. If there is already some cell ranges in edit on cursor position, replaces the one range by the selected cell. Can be called only during editing formula to let a user to select one column by clicking. If there is already some cell ranges in edit on cursor position, replaces all adjacent cell ranges by the selected cell. Switches between absolute and relative cell or cell range reference under cursor (adds or removes the $) during editing formula.
Switches between absolute and relative cell or cell range reference under cursor (adds or removes the $) during editing formula.
Switches 4 states 1) absolute cell, 2) absolute row, 3) absolute column, 4) relative cell. Set the cell or cell range reference under cursor as absolute (adds the $) during editing formula.
Set the cell or cell range reference under cursor as relative (removes the $) during editing formula.
Called on start choosing cell range or cell during editing formula. Called to clear or change the actual range on cursor position.
It is called before any formula processing, so it allows to use the Choose actions to any custom processing the chosen cell range.
row, col is the dragged or clicked cell. The edited cell can be got as grid.ERow, grid.ECol.
selection array as [start,end] is actual cursor position in the input. The input is HTML <input> tag being edited.
replace is requested action, 0 - insert, 1 - replace, 2 - replace all.
Modify the input.value by the requested action and return new selection/cursor position as [start,end].
Or return true to cancel the action, or return null to continue standard action. Called on every change during dragging by ChooseCells/Rows/Cols action or once in ChooseCell/Row/Col action to set the selected range or cell to the edited input.
range is selected cells range as [row1,col1,row2,col2]. row1, col1 is always top left corner. For ChooseCell there is row1==row2 and col1==col2.
selection array as [start,end] is actual cursor position in the input. The input is HTML <input> tag being edited.
text is the selected range as text that will be placed to the cursor position into input.
Return new text to place to cursor position or return true to not modify the input.

Functions in editable formulas

Conditional functions

Returns one from the two values according to the condition. Returns the first value that's accompanied test condition is true.
Returns the first value that's accompanied test condition is true.
Compares a number of supplied values to a supplied test expression and returns a result corresponding to the first value that matches the test expression.
A default value can be supplied, to be returned if none of the supplied values match the test expression. Returns the index's value from the arguments, for index = 1 returns the first value (the second argument). Returns value_if_error if the value is NaN otherwise returns the value. Returns value_if_error if the value is NaN otherwise returns the value.
It is the same as iferror because TreeGrid does not differ between the error sources.

Lookup functions

Returns index (from 1) of value in range.
type - what to do if the value is not found: -1 returns index of the biggest smaller value, 0 returns error, 1 returns index of the smallest bigger value.
Looks for value in range and returns corresponding value from results range.
If the value is not found, returns the last value from the results.
Looks for value in the first column of range and returns corresponding value from range in col_index column (from 1).
closest - what to do, if the value is not found: 0 - returns error, 1 returns the biggest smaller value.
Looks for value in the first row of range and returns corresponding value from range in row_index row (from 1).
closest - what to do, if the value is not found: 0 - returns error, 1 returns the biggest smaller value.

Cell reference functions

Returns cell given by row_num and col_num from the range. Indexes are from 1.
If the row_num is 0 or empty, returns all cells in the column from the range.
If the col_num is 0 or empty, returns all cells in the row from the range.
If the col_num is 0 or empty and the range contains only one row, the row_num is taken as column index.
Returns a range of cells shifted by shift_rows down and shift_col right from the range top left corner.
The returned range contains rows rows and cols columns. If the rows or cols is not set, the size of the range is used.
Returns column number (from 1) of the first cell in the range. If range is null, returns column number of the current cell.
Returns count of columns in given range.
Returns row number (from 1) of the first cell in the range. If range is null, returns row number of the current cell.
Returns count of rows in given range.
Returns a cell reference in string from given row and column (from 1), e.g. "A6" or "$F$12".
abs_type = 1 - absolute ($A$1), 2 - absolute row, relative col (A$1), 3 - relative row, absolute col ($A1), 4 - relative (A1).
a1 = 0 - RxCx type, e.g. "R3C6" or "R[-2]C[4]", 1 - default - A1 type, e.g. "A6" or "$F$12".

Logical functions

Summary function. Logical AND for all values in input parameters. Returns 0 if any of the input parameter is 0, empty or NaN; otherwise returns 1.
Summary function. Logical OR for all values in input parameters. Returns 0 if all the input parameter are 0, empty or NaN; otherwise returns 1.
Summary function. Logical XOR for all values in input parameters. Returns 0 if count of the input parameter that is not 0, empty and NaN is even; otherwise returns 1.
Returns 1 if the value is 0, empty or NaN otherwise returns 0.
Compares values. Returns 1 if value1==value2 otherwise returns 0. Tests whether a number is greater than a supplied threshold value. Returns value1 >= value ? 1 : 0. Returns 1. For compatibility.
Returns 0. For compatibility.

Informational functions

Returns 1 if the cell is empty - its value is empty string and has not any formula set. Returns 1 if the value is NaN. Returns 1 if the value is NaN. TreeGrid does not differ between error and N/A. Returns 1 if the value is NaN. TreeGrid does not differ between error and N/A. Returns 1 if the value is 0 or 1. TreeGrid does not differ between types, it returns 1 for 1 or 0 in string too. Returns 1 if the value can be converted to number. TreeGrid does not differ between types, it returns 1 for number in string too. Returns 1 if the value is string and cannot be converted to number. TreeGrid does not differ between types, it returns 0 for number in string. Returns 1 if the value is not string or it can be converted to number. TreeGrid does not differ between types, it returns 1 for number in string. Returns 1 if the value is the cell or range, not simple value. Returns 1 if the value is the cell or range and the (first) cell contains formula. Returns 1 if the value can be converted to even number. Returns 1 if the value can be converted to odd number. Returns value type.
Returns 1 for number, or value that can be converted to number, except 0 and 1.
Returns 2 for string that cannot be converted to number.
Returns 4 for value 0 and 1.
Returns 16 for error value.
Returns formula in text of the given cell.

Mathematical functions

Returns absolute value of the parameter. Returns -1 for negative number, 1 for positive and 0 for zero. Returns logbasevalue. Returns logevalue. Returns log10value. Returns e power value. Returns value power index. Renamed from pow.
sqrt Returns value square root. For other radixes than 2 use power(x,1/y).
sqrtpi (new 13.0)Returns the square root of a supplied number multiplied by pi.
Returns the factorial of a given number as n *(n-1) * (n-2) * ... Returns the double factorial of a given number as n *(n-2) * (n-4) * ... Returns the Greatest Common Divisor of two or more supplied numbers. Returns the Least Common Multiple of two or more supplied numbers. Returns the number of combinations (without repetitions) for a given number of items. Returns the number of combinations (with repetitions) for a given number of items. Returns the number of permutations for a given number of items. Returns the number of permutations for a given number of items (with repetitions) that can be selected from the total items. Returns power(n,k) Returns the integer portion of a division between two supplied numbers: int(value/denom). Returns the remainder of a division between two supplied numbers: value/denom - int(value/denom). Returns bit AND as value1 & value2; Returns bit OR as value1 | value2; Returns bit XOR as value1 ^ value2; Shifts the value by given bits to left as value << shift. Shifts the value by given bits to right as value >> shift. Returns a random number between 0 and 1. It changes in every recalculation of the sheet!
Returns a random integer number between min and max. It changes in every recalculation of the sheet!

Trigonometry functions

Returns pi 3.1415... Converts radians to degrees. Converts degrees to radians. sin returns sine (-1 to 1) of value in radians.
sinh (new 13.0) returns hyperbolic sine of value.
cos returns cosine(-1 to 1) of value in radians.
cosh (new 13.0) returns hyperbolic cosine of value.
tan returns tangent of value in radians.
tanh (new 13.0) returns hyperbolic tangent of value.
asin returns arcsine in radians of value (-1 to 1).
asinh (new 13.0) returns hyperbolic arcsine of value.
acos returns arccosine in radians of value (-1 to 1).
acosh (new 13.0) returns hyperbolic arccosine of value.
atan returns arctangent in radians of value.
atanh (new 13.0) returns hyperbolic arctangent of value.
Returns arctangent of value2 / value1 in all four quadrants (atan2). Renamed from atan.
sec returns secant of value in radians.
sech returns hyperbolic secant of value.
csc returns cosecant of value in radians.
csch returns hyperbolic cosecant of value.
cot returns cotangent of value in radians.
coth returns hyperbolic cotangent of value.
acot returns cotangent of value in radians.
acoth returns hyperbolic arccotangent of value.

Rounding numbers

Returns rounded value to given number of decimal digits, e.g. digits = 2 means round to hundredths.
If digits is negative, rounds in the left side of decimal point, e.g. digits = -2 means round to hundreds.
If digits is decimal number, the decimal part can be .1 to round to odd, .2 to round to even, .5 to round to 5, .25 round to 25, etc., e.g. round(170,-1.5) = 150, round(170,-1.25) = 175, round(170,-0.1) = 171.
Examples: round(123.456) = 123; round(123.456,2) = 123.46; round(123.456,-2) = 100; round(-1.5) = -1; round(1.005,2) = 1.01; Like round, but rounds down. Examples: rounddown(123.456,2) = 123.45; rounddown(-123.456,0) = -124; Renamed from floor.
Like round, but rounds up. Examples: roundup(123.456,0) = 124; roundup(-123.456,2) = -123.45; Renamed from ceil.
Like round, but rounds down positive numbers and up negative. ceiling Rounds a supplied number away from zero, to the nearest multiple of a given number. Negative numbers towards to zero.
ceiling.math Like ceiling, but has third parameter mode, if set to 1, it rounds negative numbers away from zero.
ceiling.precise Rounds a number up, regardless of the sign of the number, to a multiple of significance.
floor Rounds a supplied number towards zero to the nearest multiple of a specified significance. Negative numbers away from zero.
floor.math Like floor, but has third parameter mode, if set to 1, it rounds negative numbers towards zero.
floor.precise Rounds a number up, regardless of the sign of the number, to a multiple of significance.
Rounds a supplied number up or down to the nearest multiple of a given number. Rounds a supplied number to the nearest integer.

Number conversions

Converts a text representation of a number in a specified base, into a decimal value. Converts a number into a supplied base (radix), and returns a text representation of the calculated value.
If set, it fills zeros from to left to be the string at least as long as the min_length.
Converts binary value in string to decimal integer. Converts octal value in string to decimal integer. Converts hexadecimal value in string to decimal integer. Converts decimal integer to binary value in string. Converts decimal integer to octal value in string. Converts decimal integer to hexadecimal value in string.
Converts binary value in string to octal value in string.
Converts binary value in string to hexadecimal value in string.
Converts octal value in string to binary value in string.
Converts octal value in string to hexadecimal value in string.
Converts hexadecimal value in string to binary value in string.
Converts hexadecimal value in string to octal value in string.

String functions

Returns formatted date or number in string. The format is date format or number format.
type specifies the value type: 1 for number, 2 for date, 0 to guess by format and value.
If not set format and type is 0 or 2, returns formatted date as "M/d/yyyy HH:mm:ss" if the value is not decimal. For type = 1 returns the number in string.
Converts a supplied number into text, using a currency format Rounds a supplied number to a specified number of decimal places, and then converts this into text.
If set no_commas does not separate thousands by group separator.
Converts value to string. It is not needed, the conversion is usually automatic.
Converts value to number. It is usually not required, the conversion is usually automatic.
Converts a text string into a numeric value. It is not needed, the conversion is usually automatic.
Converts a text string into a numeric value using specific separators.
Compares two strings and returns 1 if they are the same. The same as comparing by =.
Returns count of characters in the string.
char Returns the character that corresponds to a supplied numeric value.
unichar Like char.
code Returns the numeric code for the first character of a supplied string.
unicode Like code.
concat Joins together two or more text strings.
concatenate Like concat Joins together two or more text strings, separated by a delimiter. If set empty, places delimiter also after empty strings.
Creates new string by repeating given string.
Searches search string in string and returns its position (from 1) or 0. case insensitive.
position can be set to start search from this position (from 1) in the string.
Searches search string in string and returns its position (from 1) or 0. case sensitive.
position can be set to start search from this position (from 1) in the string.
Replaces old_text by new_text in string. case sensitive.
If not set instance replaces all instances, otherwise it replaces given one instance (from 1).
Replaces a part of the string by string replace.
Returns substring from string on given position pos (from 1) and given length count.
Returns substring from beginning of string with given length count.
Returns substring from end of string with given length count.
Converts the string to lowercase.
Converts the string to lowercase.
Converts all first characters in words uppercase and the others lowercase. Not localized yet.
Removes all non-printable characters from a supplied text string.
Removes all spaces from string. Remains only one space between words.

Date functions

All dates are long integers in milliseconds since 1/1/1970 in UTC (GMT) timezone.
It is possible do basic mathematical operations with the dates, like subtract them or adding to them.
For example: date("5/7/2000") + date(,5,3) means 9/9/2000; Returns new date from given parameters. If any of the parameter is missing, null or zero, it is used from 1/1/1970 00:00:00.
year is full year, month is 1-12, day is 1-31, hour is 0-23, minute is 0-59, second is 0-59.
If the input parameter exceeds its range, the given number of the units is added to the date. Returns new date from given date string. format can be set as input date format, especially to specify order of day, month, year.
Only numbers in the date string are acceptable, not any string like month names.
If not set format, accepted are three base English formats "M/d/yyyy", "d.M.yyyy", "yyyy-M-d". Returns new time (in date 1/1/1970) from given parameters, hour is 0-23, minute is 0-59, second is 0-59.
The same as date (null,null,null,hour,minute,second). Returns new time from given time string. The same as date (time). Converts a text string showing a date, to an integer that represents the date without time. Converts a text string showing a time, to an integer that represents the time only. Returns number of days between the dates (date1-date2). Ignores time part of the dates.
Returns a date that is the specified number of months before or after an initial supplied start date.
Returns a date that is the last day of the month which is a specified number of months before or after an initial supplied start date.
Returns actual date and time. It is actual date and time on the computer.
Note, the formulas in grid are recalculated only on some change in grid, not periodically. Returns actual date (time 00:00:00). It is actual date on the computer. Returns full year from the date. Returns month number (1-12) from the date. Returns day number (1-31) from the date Returns number of day of week from the date.
Returns 1 = Sunday, 2 = Monday or 1 = Monday, 7 = Sunday, depending on TreeGrid language settings (FirstWeekDay). Returns week number (1-54) from the date. The week numbering is controlled by Format FirstWeekYearDay attribute. Returns week number (1-53) from the date according to European ISO week numbering. Returns hours (0-23) from the date. If set half = 1, returns hours (0-11). Returns minutes (0-59) from the date Returns seconds (0-59) from the date.

Summary functions

All summary functions accept one or more arguments.
Any of the argument can be: cell range (like A5:B10), column range (like A:D), row range (like 3:5), one cell (like A5) or direct value (like 100).
All the summary functions iterate only values that can be converted to number. Other values are ignored. sum Sums all values in input parameters.
sumsq Sums all squares of values in its parameters.
sumproduct (new 13.0) Returns the sum of the products of corresponding values in two or more supplied arrays.
Sums all cells from sum_range (or from range if sum_range is not defined) where the range cell satisfies the criteria.
criteria can be simple number or string to compare the cell values to. The string can contain wildcards * zero or for more characters and ? for one character.
criteria can be simple compare formula: "=XXX","<>XXX",">XXX",">=XXX","<XXX","<=XXX", where XXX is a number or string.
Sums all cells from sum_range that satisfy multiple criteria. Parameters see sumif
sumx2my2 Returns the sum of the difference of squares of corresponding values in two supplied arrays.
sumx2py2 Returns the sum of the sum of squares of corresponding values in two supplied arrays.
sumxmy2 Returns the sum of squares of differences of corresponding values in two supplied arrays.
Returns the sum of a power series.
x is the input value to the power series. n is the first power to which x is to be raised. m is the step size that n is increased by, on each successive power of x. range is an array of coefficients that multiply each successive power of x. count Returns count of all cells in its parameters that contain number.
counta Returns count of all not empty cells in its parameters.
countblank Returns count of all empty cells in its parameters.
Counts all cells in range that satisfy criteria. Parameters see sumif
Counts all cells in range1 that satisfy multiple criteria. Parameters see sumif
max Returns maximum value from the cells in its parameters.
maxa (new 13.0) Returns maximum value from the cells in its parameters. Counts texts and empty as 0.
Calculates maximum from all cells from max_range that satisfy multiple criteria. Parameters see sumif
min Returns minimum value from the cells in its parameters.
mina (new 13.0) Returns minimum value from the cells in its parameters. Counts texts and empty as 0.
Calculates minimum from all cells from min_range that satisfy multiple criteria. Parameters see sumif
Returns given nth (from 1) maximum value from the cells in its parameters.
Returns given nth (from 1) minimum value from the cells in its parameters.
Returns position of the value in range like it was sorted descendant or ascendent (if asc is true).
The same as rank.
Similar to rank, but if there are more value in the range, it returns average rank, not the first one.
Returns the relative position of the value, within the range, as a percentage.
Rounds result to given number of decimal digits.
The same as percentrank. Similar to percentrank, but it is exclusive. Returns the k'th percentile (i.e. the value below which k% of the data values fall) for the range. The same as percentile. Similar to percentile, but it is exclusive. Returns the quartile (0-4) for the range.
The same as quartile. Similar to quartile, but it is exclusive. Multiplies all values in its parameters. Calculates multinominal of all values in its parameters as factorial of summary divided by multiply of factorials.
average Returns mean (average) value from the cells in its parameters.
averagea Returns mean (average) value from the cells in its parameters. Counts texts and empty as 0.
Calculates average of all cells from average_range (or from range if average_range is not defined) where the range cell satisfies the criteria. Parameters see sumif
Calculates average of all cells from average_range that satisfy multiple criteria. Parameters see sumif
Returns mean (average) values in the range. Ignores the percent of the biggest and smallest values. For example if range is 10 values length and percent is 0.4, it ignores two biggest and two smallest values.
Calculates geometric mean of all values in its parameters.
Calculates harmonic mean of all values in its parameters.
Returns statistical median (middle) value of all values in its parameters. For even number of values returns an average of the two medians.
mode Returns the Mode (the most frequently occurring value) of a list of supplied numbers.
mode.sngl Like mode.
Returns the average of the absolute deviations of values from their mean.
Returns the sum of the squares of the deviations of a set of values from their sample mean.
stdev Returns the standard deviation of a supplied set of values (which represent a sample of a population).
stdeva Like stdev, but counts all non values as 0.
stdevp Returns the standard deviation of a supplied set of values (which represent an entire population).
stdevpa Like stdevp, but counts all non values as 0.
stdev.s Like stdev.
stdev.p Like stdevp.
var Returns the variance of a supplied set of values (which represent a sample of a population).
vara Like var, but counts all non values as 0.
varp Returns the variance of a supplied set of values (which represent an entire population).
varpa Like varp, but counts all non values as 0.
var.s Like var.
var.p Like varp.
covar Returns population covariance (i.e. the average of the products of deviations for each pair within two supplied data sets).
covariance.s Returns sample covariance (i.e. the average of the products of deviations for each pair within two supplied data sets).
covariance.p Like covar.
forecast Predicts a future point on a linear trend line fitted to a supplied set of x- and y- values.
forecast.linear Like forecast.
Calculates the best fit regression line, through a supplied series of x- and y- values and returns the value at which this line intercepts the y-axis.
Returns the slope of the linear regression line through a supplied series of x- and y- values.
Returns the standard error of the predicted y-value for each x in the regression line for a set of supplied x- and y- values.
Applies given summary function to all values in its next parameters.
function = 1 average, 2 count, 3 counta, 4 min, 5 max, 6 product, 7 stdev, 8 stdevp, 9 sum, 10 var, 11 varp, 12 median, 13 mode.
Applies given summary function to all values in its next parameters.
option = 0 ignore other aggregate and subtotal results, 1 like 0 and also hidden and deleted rows not implemented, 2 like 0 and also ignore strings and errors, 3 like 1+2.
option = 4 - ignore nothing, 5 ignore hidden and deleted rows not implemented, 6 ignore strings and errors, 7 like 5+6.
function = 1 average, 2 count, 3 counta, 4 min, 5 max, 6 product, 7 stdev, 8 stdevp, 9 sum, 10 var, 11 varp, 12 median, 13 mode, 14 large, 15 small.
For function 14 and 15 the fourth parameter is the percent for the function.
Returns the matrix determinant of a supplied range.

Custom functions in editable formulas

Since 13.0 the JavaScript function name must be lower case!
Define the custom function as global JavaScript function and assign its name in <Lang><FormulaFunctions></Lang>.
In the custom function you will get the parameters normally in arguments array.
The one cell and cell ranges parameters are already converted to one dimension array of all the cell values.
The cell range is converted like [R1C1,R1C2,R1C3,...,R2C1,R2C2,R2C3,...].
Every the parameter array has set also properties Rows as array of rows, Cols as array of column names and Grid as calling grid.
Note, the constant string or number parameter is passed as is, without any conversion or properties.

Example, in script:

function mysum(){ // Name must be lower case
for(var i=0,sum=0;i<arguments.length;i++){
   var a = arguments[i]; if(typeof(a)!="object") a = [a];
   for(var j=0;j<a.length;j++) if(a[j]-0) sum += a[j]-0;
   }
return sum;
}

In XML:

<Grid>
   <Lang> <FormulaFunctions mysum="specsum"/> </Lang> <!-- mysum is used in data, specsum is displayed when editing  -->
   <Cfg FormulaEditing="1"/>
   <Cols> <C Name="A"/> <C Name="B"/> </Cols>
   <Body> <B>
      <I id="1" A="1" B="2"/> <I id="2" A="2" B="4"/> <I id="3" A="3" B="8"/> <I id="4" A="4" B="16"/>
      <I id="5" A="sum" B="=mysum(A1:B3,A4,-10)"/>
   </B> </Body>
</Grid>