News in version
TreeGrid Gantt chart SpreadSheet Examples Documentation Download Development Licensing Prices References Contacts Buy Try it free
Documentation
Search in documentation
TreeGrid versions compatibility

Changes log (txt file) Using custom CSS from 13.3 to 14.0 Upgrading from 9.3 to 10.0 Upgrading from 5.9 to 6.0

Using in JavaScript frameworks

Angular React Vue Other frameworks NodeJS server

Creating grid

Simple examples Basic information Creating grid Deleting grid Reloading grid Rendering grid Accessing grid by API

Data & communication
Data communication with server

Communication types AJAX communication AJAX SOAP envelope Submit communication Directly included data Data from JavaScript Cross domain / local load by JSONP Sessions (AJAX) Server response (AJAX) Synchronous communication (AJAX) Caching (AJAX) API (AJAX)

Download data

Settings for data download Layout XML structure Data XML structure

Upload data

Settings for data upload API for upload XML structure sent to server

Changing data from server

XML structure of download changes XML structure of request for a cell Synchronizing data with server

Input / output data formats

Complete list of tags Internal XML format Short XML format Extra short XML format DTD XML format JSON format

Cells
Cell basics

Cell type Cell format Dynamic format Dynamic type Cell value Reading / writing attributes by API Cell HTML Cell default Range or more values in one cell Cell with link URL Cell hint Cell tooltip / title Cell popup menu

Cell editing and changing values

Cell editability Dynamic editing Locking Defaults list Suggest list (auto complete) Tags input Changing cell value Mass cell change Clearing cells Editing cells Controlling <input> tag by JavaScript Input validation and restrictions Side checkbox Cell selecting

Calculations - cell formulas

Basics Formulas Mathematical functions Aggregate functions Special functions for actions Custom functions

Calculations - editable cell formulas

Basics Suggest list (auto complete) Defined names Actions for choosing cells Conditional functions Lookup functions Cell reference functions Logical functions Informational functions Mathematical functions Trigonometry functions Rounding numbers Number conversions String functions Date functions Summary functions Custom functions

Cell side buttons

Introduction Right side Button Left side Icon

Cell spanning

Column span Row span Dynamic spanning

Cell style and color

Basic grid style Sizing and scaling - responsive design Dynamic cell style attributes Cell CSS class Cell background color Dynamic cell border Cell HTML style Row color alteration Cell mouse cursor

Editable cell images

Insert image Edit image

Cell types
Automatic type - Auto String - Text, Lines & Pass
Number - Int & Float

Format Localization

Date and time - Date

Format Calendar component Dates dialog Localization

List & combo - Enum & Radio

Introduction Definition Related lists Enum specific Radio specific

Checkbox - Bool
Action button - Button

Introduction Basic clickable button Switch button Radio / tab button Menu button Combo switch button Combo radio / tab button Special Space rows with buttons

Panel with more buttons - Panel

Definition Standard fast panel Custom panel

HTML and special types

Html type EHtml type (editable) Icon type Abs type List type Editable Link type Editable Img type DropCols type Upload File type

Columns

Column basics Column index Column visibility Column visibility menu Column selecting Column tree Auto column tree Column position and moving Column adding and copying Column deleting Column width Column API

Rows
Row basics

Rows by functionality Rows by position Row name Row id Row index

Default rows

Description Example of default rows Example of changing default row Attributes

Row tree

Tree attributes Actions & API for expand / collapse

Row identification

Setting row id attribute Row id attribute in tree Row id created from cell values API for row ids

Row visibility
Row adding and copying

Five ways of adding rows to grid Adding and copying restrictions Adding new empty rows Copying existing rows

Row deleting Row moving and dragging Row selecting Row height Row API Space rows
Features
Sorting rows

Sort settings Controlling sort position Comparing strings Sorting actions Sorting API

Grouping rows to tree

Group settings Creating groups Comparing strings Created group rows <D Group='1'/> User interface to choose grouping Grouping actions and API

Filtering rows

Filter settings Comparing strings User interface to choose filter Filter actions and API

Searching in rows and cells

Search settings User interface for search Search actions and API

Printing grid

Print settings Choosing items to print Page size Print API

Print / export to PDF

Introduction Client side settings Printing options Client side API Server side API Data sent from client to server

Export to Excel or CSV

Introduction and export types Basic settings Styling export XLSX export Gantt export CSV export Old XLS / XHTML export Export API Communication with server Client export Server export

Import from Excel

Basic settings Sheets manipulation

Copy & paste rows via clipboard

Copy & paste permissions Copying to clipboard Pasting from clipboard

Master - detail grids

Introduction External master - detail grids Nested master - detail grids Synchronizing grids Other attributes for master - detail

Pivot tables

Pivot attributes & API Pivot formulas

External objects (like Adobe Flash) Undo & Redo
Gantt and bar chart
Gantt objects

Gantt objects list Display settings Edit settings

Main bar

Definition of main bar and plans Main bar as Task Edit settings Main bar content and side html Tip on mouse hover Vertical position and height Style specific attributes API to manipulate Main bars Actions Side text (deprecated) Real Flow (deprecated)

Run bar

GanttRun Definition Extended definition Run bar as Task Edit settings Save format Selecting Run boxes Run box content and side html Tip on mouse hover Box identification Vertical position and height Style specific attributes Overlaid (Error) boxes Containers for more boxes Dragging - moving and resizing API to manipulate Run boxes Actions Run special formulas

Summary task

Main for Main Main for Run Editable Main Editable Run

Gantt icons - Flag & Point

Flag - icon with text Point - math points

Gantt display objects

Header - column captions Cell and column Background Vertical movable Line Mark & Progress line

Gantt zoom

Zoom options Chart size limits Zoom selection Paging in Gantt chart

Dependency, constraints & schedule

Introduction Data units Defining dependencies Dependency colors and shapes Changing dependencies Correcting dependencies Scheduling algorithm Project date constraints Task date constraints Critical path - Slack (Float)

Gantt calendars

Global base calendar Local calendar Calendar list Calendars dialog Other settings

Gantt resources

Resources list Resources assign Resources filter Resources calculations Availability chart Resource usage chart Generated resource usage chart

Gantt popup menu Gantt API
Line and XY points charts

Charts in grid cells Chart JSON definition Base chart settings Basic attributes Size Axis and caption Individual chart lines Data sources Visual settings API for standalone usage

Paging in large grids
Paging root rows

Paging types and attributes Auto adding root pages API for paging

Pager components

Side pager Side pager type Pages Side pager type Gantt Side pager type Custom Pager with navigation buttons & edit Pager as list of page indexes

Paging in tree

ChildPaging - load / render on expand MaxChildren - limit children count ChildParts - load / render on scroll

Paging columns

Paging types and attributes Auto adding column pages API for column paging

Server paging

Server paging for root rows Server communication in root paging Root paging in very large tables Server paging in tree Server communication in tree paging XML Request for Data in root paging XML Download Data in root paging XML Request for root Page / children XML Download root Page / children API for server paging

TreeGrid DLL/SO for server paging

Introduction Compatibility with TreeGrid control Using TreeGrid server DLL/SO ASP.NET C# ASP.NET VB PHP JSP Java TreeGrid server concepts Function reference Calculations

JSON menus and dialogs
JSON menu description JSON menu definition example
Menu settings

Base attributes Visual settings Key navigation Behavior Size and scroll

Menu item settings

Base attributes Clickable item Inactive caption Collapsible sub level Popup sub menu Columns Bool item Enum item Edit item

Custom menu in JavaScript

Show custom menu Custom menu position Custom menu advanced settings Custom menu JavaScript events Custom menu JavaScript methods

Calendar dialog Custom calendar & JavaScript events Custom dialog in JavaScript
Global grid settings
Grid size and scroll

Default behavior Maximize grid height and width Update size according to the content Let a user to control the grid size Widths of individual sections Other scrolling attributes and API

Media rules - responsive design
Languages

Language & regional setup (Text.xml) Translate texts dynamically Change language

Grid cursor - Focus & hover

Focused cell and row Focused cell range Move and copy focused cells Filling cell values by dragging Tabulator navigation Key navigation Cursor look - focus & hover

Selecting rows, cells and columns

Selecting base Selecting rows Selecting cells Selecting columns

Global settings

Status messages Configuration menus Configuration menu - options Configuration menu - columns Configuration menu - print / PDF Configuration menu - export Default toolbar Useful API function Help file

Animations

Animations base Row animations Column animations Cell animations Animations for undo / redo Animations for server side changes Dialog animations

Grid configuration in cookies
Mouse & key events & actions

List of event handler types TreeGrid mouse events Mouse event names Key and mouse button prefixes Touch event names Event targets Assigning event actions / callbacks Event action parameters Action name suffix Calling actions from JavaScript Creating custom actions Focused vs. Actual cell TreeGrid key events JavaScript API events

Mouse API event TreeGrid files
Debugging and testing

Debug window Automated testing

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

new 12.0 <Cfg> int

FormulaEditing

[0]
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.
new 12.0 upd 15.0 <Cfg> int

FormulaRelative

[0]
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".
1The 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.
new 12.0 <Cfg> bool

FormulaLocal

[0]
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.
new 12.0 <Cfg> int

FormulaResults

[15]
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
new 15.1 <Cfg> bool

FormulaPlusNumbers

[0]
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.
new 12.0 upd 15.0 <C><I><cell> int

FormulaCanEdit

[1]
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.
new 12.0 <C><I> bool

FormulaCanUse

[1/0]
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.
new 12.0 chg 13.0 <Cfg> int

FormulaNames

[0]
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.
new 12.0 upd 13.0 <Cfg> int

FormulaType

[0]
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.
new 12.0 upd 13.3 <Cfg> int

FormulaCircular

[1]
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.
new 13.0 <Cfg> int

FormulaMaxIterations

[1000]
Maximum calculated cells when testing one formula for circular dependencies.
new 15.0 <Cfg> int

FormulaTimeout

[10000]
Maximal time that can be spent by recalculating sheet, in milliseconds.
new 12.0 <Cfg> int

FormulaChanges

[0]
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.
new 12.1 <Cfg> bool

FormulaAddParenthesis

[0]
If set, tries to add ending parenthesis to incorrect formula.
new 13.0 <Cfg> bool

FormulaTip

[1]
If cell formula is shown as cell tip.
new 15.0 <Cfg> bool

FormulaShow

[0] Saved to cookies, to not save it set FormulaShowLap='1'
If all the editable formulas are displayed in their cells instead of their results.
new 16.3 <Cfg> bool

FormulaColorRanges

[0]
If edited cell formula colors cell ranges in different colors in the formula text and also in grid.
new 16.3 <Cfg> string[]

FormulaEditColors

["#F00,#0F0,#00F,#800,#880,#008,#F0F,#FC0"]
Comma separated list of HTML colors to color the cell ranges in edited formula, if set FormulaColorRanges.
new 16.3 <Cfg> string[]

FormulaRangeColors

["#F88,#8F8,#88F,#C88,#CC4,#44C,#F8F,#FC8"]
Comma separated list of HTML colors to color the cell ranges in grid, if set FormulaColorRanges.
new 16.5 <Cfg> bool

FormulaEditSheets

[0]
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.
new 12.0 <Format> string

FormulaPrefix

[=]
The formula in cell starts by this string.
new 12.0 <Format> string

FormulaRangeSeparator

[:]
This string separates bounds in cell range like C5:D8.
new 12.0 <Format> string

FormulaValueSeparator

[,]
This string separates arguments in formula function.
It should be especially changed when changed DecimalSeparator to comma.
new 13.0 <Format> string

FormulaIntersectionSeparator

[ ]
This string separates intersection of cell ranges like C5:D8 A1:D4. Default is space.
new 12.0 <Format> string

FormulaAbsolute

[$]
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).
new 12.0 <cell> string

EFormula

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.
new 12.0 API event type

OnFormula

(TGrid grid, TRow row, string col, type value, type oldvalue, string formula, object errors)
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.
new 12.0 API method string

GetCellName

(TRow row, string col)
Returns cell name for formula, e.g. A1 or B3.
Returns the name according to the FormulaRelative and FormulaNames settings.
new 13.0 API event string

GetStringEdit

(TRow row, string col)
Returns cell value as string for editing. If the cell contains EFormula, it returns the EFormula starting by '='.
new 13.0 API event void

SetStringEdit

(TRow row, string col, string value, bool timeout = 1)
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.
new 15.0 <Actions>

ShowFormula ...FSARCWO

Not attached to any event
Sets 2 to cell attribute FormulaCanEdit in actual or focused cell or in all focused or selected cells or rows or columns.
new 15.0 <Actions>

HideFormula ...FSARCWO

Not attached to any event
Sets 0 to cell attribute FormulaCanEdit in actual or focused cell or in all focused or selected cells or rows or columns.
new 15.0 <Actions>

DefaultFormula ...FSARCWO

Not attached to any event
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.
new 12.0 <C> <cell> string[*]

FormulaSuggest

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.
new 12.0 <C> <cell> string[]

FormulaSuggestType

["BeforeSeparator"]
Various settings for FormulaSuggest menu for editable formula. Format is the same as SuggestType.
new 12.0 <C> <cell> int

FormulaSuggestDelay

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.
new 12.0 <C> <cell> int

FormulaSuggestMin

Minimal count of characters in text to show the suggest formula menu for the text.
new 12.0 <C> <cell> string

FormulaSuggestSeparator

"[+\-*\/!%&|^<>=?:()\[\]{}"',~]+"
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.
new 15.0 <Names>

N

Defines one name. Can be used only in root tag <Names>.
new 15.0 <N> string

Name

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.
new 15.0 <N> string

Value

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.
new 15.0 <N> bool

Global

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.
new 15.0 <Actions>

DefineName ...FSARCWO

Not attached to any event
Defines new local name for actual / focused / selected cells / rows / columns.
Lets a user to enter the name's Name.
new 15.0 <Actions>

DefineGlobalName ...FSARCWO

Not attached to any event
Defines new Global name for actual / focused / selected cells / rows / columns.
Lets a user to enter the name's Name.
new 15.0 <Actions>

EditName

Not attached to any event
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.
new 15.0 <Actions>

DeleteName

Not attached to any event
Shows a list of all defined names in grid and lets a user to delete chosen name.
new 15.0 API method void

ChangeName

(object OldName, object NewName, bool undo, bool recalc)
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

new 12.0 <Actions>

ChooseCellsInsert

Attached to OnCtrlDragRow, can be attached only to On...Drag... events
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.
new 12.0 <Actions>

ChooseCellsReplace

Not attached to any event, can be attached only to On...Drag... events
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.
new 12.0 <Actions>

ChooseCellsReplaceAll

Attached to OnDragRow, can be attached only to On...Drag... events
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.
new 12.0 <Actions>

ChooseRowsInsert

Not attached to any event, can be attached only to On...Drag... events
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.
new 12.0 <Actions>

ChooseRowsReplace

Not attached to any event, can be attached only to On...Drag... events
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.
new 12.0 <Actions>

ChooseRowsReplaceAll

Not attached to any event, can be attached only to On...Drag... events
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.
new 12.0 <Actions>

ChooseColsInsert

Not attached to any event, can be attached only to On...Drag... events
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.
new 12.0 <Actions>

ChooseColsReplace

Not attached to any event, can be attached only to On...Drag... events
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.
new 12.0 <Actions>

ChooseColsReplaceAll

Not attached to any event, can be attached only to On...Drag... events
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.
new 12.0 <Actions>

ChooseCellInsert

Attached to OnCtrlClickCell
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.
new 12.0 <Actions>

ChooseCellReplace

Not attached to any event
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.
new 12.0 <Actions>

ChooseCellReplaceAll

Attached to OnShiftClickCell
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.
new 12.0 <Actions>

ChooseRowInsert

Not attached to any event
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.
new 12.0 <Actions>

ChooseRowReplace

Not attached to any event
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.
new 12.0 <Actions>

ChooseRowReplaceAll

Not attached to any event
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.
new 12.0 <Actions>

ChooseColInsert

Not attached to any event
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.
new 12.0 <Actions>

ChooseColReplace

Not attached to any event
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.
new 12.0 <Actions>

ChooseColReplaceAll

Not attached to any event
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.
new 12.0 <Actions>

SwitchCellAbsolute

Not attached to any event
Switches between absolute and relative cell or cell range reference under cursor (adds or removes the $) during editing formula.
new 12.0 <Actions>

SwitchRowColAbsolute

Attached to OnF4Edit
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.
new 12.0 <Actions>

SetCellAbsolute

Not attached to any event
Set the cell or cell range reference under cursor as absolute (adds the $) during editing formula.
new 12.0 <Actions>

SetCellRelative

Not attached to any event
Set the cell or cell range reference under cursor as relative (removes the $) during editing formula.
new 12.0 API event int []

OnClearChooseCells

(TGrid grid, TRow row, string col, int [] selection, object input, int replace)
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.
new 12.0 API event int []

OnSetChooseCells

(TGrid grid, object [] range, int [] selection, object input, string text)
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

new 13.0 formula type

if

(bool condition, type value_if_true, type value_if_false)
Returns one from the two values according to the condition.
new 13.0 formula type

ifs

(bool test1, type value1, bool test2, type value2, ...)
Returns the first value that's accompanied test condition is true.
new 13.0 formula type

switch

(type expression, type compare1, type result1, type compare2, type result2, ..., type default)
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.
new 13.0 formula type

choose

(int index, type value1, type value2, ...)
Returns the index's value from the arguments, for index = 1 returns the first value (the second argument).
new 13.0 formula type

iferror

(type value, type value_if_error)
Returns value_if_error if the value is NaN otherwise returns the value.
new 13.0 formula type

ifna

(type value, type value_if_error)
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

new 13.0 formula int

match

(type value, range range, int type)
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.
new 13.0 formula int

lookup

(type value, range range, range results)
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.
new 13.0 formula int

vlookup

(type value, range range, int col_index, bool closest)
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.
new 13.0 formula int

hlookup

(type value, range range, int row_index, bool closest)
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

new 13.0 formula range

index

(range range, int row_num, int col_num)
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.
new 13.0 formula range

offset

(range range, int shift_rows, int shift_cols, int rows, int cols)
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.
new 13.0 formula int

column

(range range)
Returns column number (from 1) of the first cell in the range. If range is null, returns column number of the current cell.
new 13.0 formula int

columns

(range range)
Returns count of columns in given range.
new 13.0 formula int

row

(range range)
Returns row number (from 1) of the first cell in the range. If range is null, returns row number of the current cell.
new 13.0 formula int

rows

(range range)
Returns count of rows in given range.
new 13.0 formula string

address

(int row_num, int col_num, int abs_type = 1, bool a1 = 1)
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

new 13.0 formula bool

and

(range range1, range range2, ...)
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.
new 13.0 formula bool

or

(range range1, range range2, ...)
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.
new 13.0 formula bool

xor

(range range1, range range2, ...)
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.
new 13.0 formula bool

not

(type value)
Returns 1 if the value is 0, empty or NaN otherwise returns 0.
new 13.0 formula bool

delta

(type value1, type value2 = 0)
Compares values. Returns 1 if value1==value2 otherwise returns 0.
new 13.0 formula bool

gestep

(type value1, type value2 = 0)
Tests whether a number is greater than a supplied threshold value. Returns value1 >= value ? 1 : 0.
new 13.0 formula bool

true

( )
Returns 1. For compatibility.
new 13.0 formula bool

false

( )
Returns 0. For compatibility.

Informational functions

new 13.0 formula bool

isblank

(type value)
Returns 1 if the cell is empty - its value is empty string and has not any formula set.
new 13.0 formula bool

iserror

(type value)
Returns 1 if the value is NaN.
new 13.0 formula bool

iserr

(type value)
Returns 1 if the value is NaN. TreeGrid does not differ between error and N/A.
new 13.0 formula bool

isna

(type value)
Returns 1 if the value is NaN. TreeGrid does not differ between error and N/A.
new 13.0 formula bool

islogical

(type value)
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.
new 13.0 formula bool

isnumber

(type value)
Returns 1 if the value can be converted to number. TreeGrid does not differ between types, it returns 1 for number in string too.
new 13.0 formula bool

istext

(type value)
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.
new 13.0 formula bool

isnontext

(type value)
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.
new 13.0 formula bool

isref

(type value)
Returns 1 if the value is the cell or range, not simple value.
new 13.0 formula bool

isformula

(type value)
Returns 1 if the value is the cell or range and the (first) cell contains formula.
new 13.0 formula bool

iseven

(type value)
Returns 1 if the value can be converted to even number.
new 13.0 formula bool

isodd

(type value)
Returns 1 if the value can be converted to odd number.
new 13.0 formula int

type

(type value)
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.
new 13.0 formula string

formulatext

(type cell)
Returns formula in text of the given cell.

Mathematical functions

new 12.0 formula float

abs

(float value)
Returns absolute value of the parameter.
new 13.0 formula int

sign

(float value)
Returns -1 for negative number, 1 for positive and 0 for zero.
new 12.0 formula float

log

(float value, float base = e)
Returns logbasevalue.
new 13.0 formula float

ln

(float value)
Returns logevalue.
new 13.0 formula float

log10

(float value)
Returns log10value.
new 12.0 formula float

exp

(float value)
Returns e power value.
new 12.0 renamed 13.0 formula float

power

(float value, float index)
Returns value power index. Renamed from pow.
new 12.0 upd 13.0 formula float

sqrt...

(float value)
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.
new 13.0 formula int

fact

(int value)
Returns the factorial of a given number as n *(n-1) * (n-2) * ...
new 13.0 formula int

factdouble

(int value)
Returns the double factorial of a given number as n *(n-2) * (n-4) * ...
new 13.0 formula int

gcd

(int value1, int value2, ...)
Returns the Greatest Common Divisor of two or more supplied numbers.
new 13.0 formula int

lcm

(int value1, int value2, ...)
Returns the Least Common Multiple of two or more supplied numbers.
new 13.0 formula int

combin

(int n, int k)
Returns the number of combinations (without repetitions) for a given number of items.
new 13.0 formula int

combina

(int n, int k)
Returns the number of combinations (with repetitions) for a given number of items.
new 13.0 formula int

permut

(int n, int k)
Returns the number of permutations for a given number of items.
new 13.0 formula int

permutationa

(int n, int k)
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)
new 13.0 formula int

quotient

(float value, float denom)
Returns the integer portion of a division between two supplied numbers: int(value/denom).
new 13.0 formula float

mod

(float value, float denom)
Returns the remainder of a division between two supplied numbers: value/denom - int(value/denom).
new 13.0 formula int

bitand

(int value1, int value2)
Returns bit AND as value1 & value2;
new 13.0 formula int

bitor

(int value1, int value2)
Returns bit OR as value1 | value2;
new 13.0 formula int

bitxor

(int value1, int value2)
Returns bit XOR as value1 ^ value2;
new 13.0 formula int

bitlshift

(int value, int shift)
Shifts the value by given bits to left as value << shift.
new 13.0 formula int

bitrshift

(int value, int shift)
Shifts the value by given bits to right as value >> shift.
new 13.0 formula float

rand

( )
Returns a random number between 0 and 1. It changes in every recalculation of the sheet!
new 13.0 formula float

randbetween

(int min, int max)
Returns a random integer number between min and max. It changes in every recalculation of the sheet!

Trigonometry functions

new 13.0 formula float

pi

( )
Returns pi 3.1415...
new 13.0 formula float

degrees

(float value)
Converts radians to degrees.
new 13.0 formula float

radians

(float value)
Converts degrees to radians.
new 12.0 upd 13.0 formula float

sin...

(float value)
sin returns sine (-1 to 1) of value in radians.
sinh (new 13.0) returns hyperbolic sine of value.
new 12.0 upd 13.0 formula float

cos...

(float value)
cos returns cosine(-1 to 1) of value in radians.
cosh (new 13.0) returns hyperbolic cosine of value.
new 12.0 upd 13.0 formula float

tan...

(float value)
tan returns tangent of value in radians.
tanh (new 13.0) returns hyperbolic tangent of value.
new 12.0 upd 13.0 formula float

asin...

(float value)
asin returns arcsine in radians of value (-1 to 1).
asinh (new 13.0) returns hyperbolic arcsine of value.
new 12.0 upd 13.0 formula float

acos...

(float value)
acos returns arccosine in radians of value (-1 to 1).
acosh (new 13.0) returns hyperbolic arccosine of value.
new 13.0 formula float

atan...

(float value)
atan returns arctangent in radians of value.
atanh (new 13.0) returns hyperbolic arctangent of value.
new 12.0 renamed 13.0 formula float

atan2

(float value1, float value2 = null)
Returns arctangent of value2 / value1 in all four quadrants (atan2). Renamed from atan.
new 13.0 formula float

sec...

(float value)
sec returns secant of value in radians.
sech returns hyperbolic secant of value.
new 13.0 formula float

csc...

(float value)
csc returns cosecant of value in radians.
csch returns hyperbolic cosecant of value.
new 13.0 formula float

cot...

(float value)
cot returns cotangent of value in radians.
coth returns hyperbolic cotangent of value.
new 13.0 formula float

acot...

(float value)
acot returns cotangent of value in radians.
acoth returns hyperbolic arccotangent of value.

Rounding numbers

new 12.0 formula float

round

(float value, int digits = 0)
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;
new 12.0 renamed 13.0 formula float

rounddown

(float value, int digits = 0)
Like round, but rounds down. Examples: rounddown(123.456,2) = 123.45; rounddown(-123.456,0) = -124; Renamed from floor.
new 12.0 renamed 13.0 formula float

roundup

(float value, int digits = 0)
Like round, but rounds up. Examples: roundup(123.456,0) = 124; roundup(-123.456,2) = -123.45; Renamed from ceil.
new 13.0 formula float

trunc

(float value, int digits = 0)
Like round, but rounds down positive numbers and up negative.
new 13.0 formula float

ceiling...

(float value, int multiple = 0)
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.
new 13.0 formula float

floor...

(float value, int multiple = 0)
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.
new 13.0 formula float

mround

(float value, int multiple = 0)
Rounds a supplied number up or down to the nearest multiple of a given number.
new 13.0 formula int

int

(float value)
Rounds a supplied number to the nearest integer.

Number conversions

new 13.0 formula int

decimal

(string text, int radix)
Converts a text representation of a number in a specified base, into a decimal value.
new 13.0 formula string

base

(int number, int radix, int min_length)
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.
new 13.0 formula int

bin2dec

(string text)
Converts binary value in string to decimal integer.
new 13.0 formula int

oct2dec

(string text)
Converts octal value in string to decimal integer.
new 13.0 formula int

hex2dec

(string text)
Converts hexadecimal value in string to decimal integer.
new 13.0 formula string

dec2bin

(int number)
Converts decimal integer to binary value in string.
new 13.0 formula string

dec2oct

(int number)
Converts decimal integer to octal value in string.
new 13.0 formula string

dec2hex

(int number)
Converts decimal integer to hexadecimal value in string.
new 13.0 formula string

bin2oct

(string text)
Converts binary value in string to octal value in string.
new 13.0 formula string

bin2hex

(string text)
Converts binary value in string to hexadecimal value in string.
new 13.0 formula string

oct2bin

(string text)
Converts octal value in string to binary value in string.
new 13.0 formula string

oct2hex

(string text)
Converts octal value in string to hexadecimal value in string.
new 13.0 formula string

hex2bin

(string text)
Converts hexadecimal value in string to binary value in string.
new 13.0 formula string

hex2oct

(string text)
Converts hexadecimal value in string to octal value in string.

String functions

new 12.0 formula string

text

(float value, string format = null, int type = 0)
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.
new 13.0 formula string

dollar

(float number)
Converts a supplied number into text, using a currency format
new 13.0 formula string

fixed

(float number, int digits, bool no_commas)
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.
new 13.0 formula string

t

(object value)
Converts value to string. It is not needed, the conversion is usually automatic.
new 13.0 formula float

n

(string text)
Converts value to number. It is usually not required, the conversion is usually automatic.
new 13.0 formula float

value

(string string)
Converts a text string into a numeric value. It is not needed, the conversion is usually automatic.
new 13.0 formula float

numbervalue

(string string, string decimal_separator, string group_separator)
Converts a text string into a numeric value using specific separators.
new 13.0 formula bool

exact

(string string1, string string2)
Compares two strings and returns 1 if they are the same. The same as comparing by =.
new 13.0 formula int

len

(string string)
Returns count of characters in the string.
new 13.0 formula string

char...

(int code)
char Returns the character that corresponds to a supplied numeric value.
unichar Like char.
new 13.0 formula int

code...

(string character)
code Returns the numeric code for the first character of a supplied string.
unicode Like code.
new 13.0 formula string

concat...

(string string1, string string2, ...)
concat Joins together two or more text strings.
concatenate Like concat
new 13.0 formula string

textjoin

(string delimiter, bool empty, string string1, string string2, ...)
Joins together two or more text strings, separated by a delimiter. If set empty, places delimiter also after empty strings.
new 13.0 formula string

rept

(string string, int count)
Creates new string by repeating given string.
new 13.0 formula string

search

(string search, string string, int position)
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.
new 13.0 formula string

find

(string search, string string, int position)
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.
new 13.0 formula string

substitute

(string string, string old_text, string new_text, int instance)
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).
new 13.0 formula string

replace

(string string, int position, int count, string replace)
Replaces a part of the string by string replace.
new 13.0 formula string

mid

(string string, int position, int count)
Returns substring from string on given position pos (from 1) and given length count.
new 13.0 formula string

left

(string string, int count)
Returns substring from beginning of string with given length count.
new 13.0 formula string

right

(string string, int count)
Returns substring from end of string with given length count.
new 13.0 formula string

lower

(string string)
Converts the string to lowercase.
new 13.0 formula string

upper

(string string)
Converts the string to lowercase.
new 13.0 formula string

proper

(string string)
Converts all first characters in words uppercase and the others lowercase. Not localized yet.
new 13.0 formula string

clean

(string string)
Removes all non-printable characters from a supplied text string.
new 13.0 formula string

trim

(string 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;
new 12.0 formula int

date

(int year, int month, int day, int hour, int minute, int second)
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.
new 12.0 formula int

date

(string date, string format = null)
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".
new 12.0 formula int

time

(int hour, int minute, int second)
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).
new 12.0 formula int

time

(string time)
Returns new time from given time string. The same as date (time).
new 13.0 formula int

datevalue

(string date)
Converts a text string showing a date, to an integer that represents the date without time.
new 13.0 formula int

timevalue

(string date)
Converts a text string showing a time, to an integer that represents the time only.
new 13.0 formula int

days

(int date1, int date2)
Returns number of days between the dates (date1-date2). Ignores time part of the dates.
new 13.0 formula int

edate

(int date, int months)
Returns a date that is the specified number of months before or after an initial supplied start date.
new 13.0 formula int

eomonth

(int date, int months)
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.
new 12.0 formula int

now

( )
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.
new 12.0 formula int

today

( )
Returns actual date (time 00:00:00). It is actual date on the computer.
new 12.0 formula int

year

(int date)
Returns full year from the date.
new 12.0 formula int

month

(int date)
Returns month number (1-12) from the date.
new 12.0 formula int

day

(int date)
Returns day number (1-31) from the date
new 12.0 formula int

weekday

(int 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).
new 12.0 formula int

weeknum

(int date)
Returns week number (1-54) from the date. The week numbering is controlled by Format FirstWeekYearDay attribute.
new 13.0 formula int

isoweeknum

(int date)
Returns week number (1-53) from the date according to European ISO week numbering.
new 12.0 formula int

hour

(int date, bool half = 0)
Returns hours (0-23) from the date. If set half = 1, returns hours (0-11).
new 12.0 formula int

minute

(int date)
Returns minutes (0-59) from the date
new 12.0 formula int

second

(int 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.
new 12.0 upd 13.0 formula float

sum...

(range range1, range range2, ...)
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.
new 13.0 formula float

sumif

(range range, string criteria, range sum_range)
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.
new 13.0 formula float

sumifs

(range sum_range, range range1, string criteria1, range range2, string criteria2 ,...)
Sums all cells from sum_range that satisfy multiple criteria. Parameters see sumif
new 13.0 formula float

sumxy...

(range range_x, range range_y)
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.
new 13.0 formula float

seriessum

(float x, int n, m, range range)
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.
new 12.0 formula int

count...

(range range1, range range2, ...)
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.
new 13.0 formula int

countif

(range range, string criteria)
Counts all cells in range that satisfy criteria. Parameters see sumif
new 13.0 formula int

countifs

(range range1, string criteria1, range range2, string criteria2, ...)
Counts all cells in range1 that satisfy multiple criteria. Parameters see sumif
new 12.0 upd 13.0 formula float

max...

(range range1, range range2, ...)
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.
new 13.0 formula float

maxifs

(range max_range, range range1, string criteria1, range range2, string criteria2,...)
Calculates maximum from all cells from max_range that satisfy multiple criteria. Parameters see sumif
new 12.0 upd 13.0 formula float

min...

(range range1, range range2, ...)
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.
new 13.0 formula float

minifs

(range min_range, range range1, string criteria1, range range2, string criteria2, ...)
Calculates minimum from all cells from min_range that satisfy multiple criteria. Parameters see sumif
new 13.0 formula float

large

(range range, int n)
Returns given nth (from 1) maximum value from the cells in its parameters.
new 13.0 formula float

small

(range range, int n)
Returns given nth (from 1) minimum value from the cells in its parameters.
new 13.0 formula int

rank

(float value, range range, bool asc)
Returns position of the value in range like it was sorted descendant or ascendent (if asc is true).
new 13.0 formula int

rank.eq

(float value, range range, bool asc)
The same as rank.
new 13.0 formula float

rank.avg

(float value, range range, bool asc)
Similar to rank, but if there are more value in the range, it returns average rank, not the first one.
new 13.0 formula float

percentrank

(range range, float value, int digits = 3)
Returns the relative position of the value, within the range, as a percentage.
Rounds result to given number of decimal digits.
new 13.0 formula float

percentrank.inc

(range range, float value, int digits = 3)
The same as percentrank.
new 13.0 formula float

percentrank.exc

(range range, float value, int digits = 3)
Similar to percentrank, but it is exclusive.
new 13.0 formula float

percentile

(range range, float k)
Returns the k'th percentile (i.e. the value below which k% of the data values fall) for the range.
new 13.0 formula float

percentile.inc

(range range, float k)
The same as percentile.
new 13.0 formula float

percentile.exc

(range range, float k)
Similar to percentile, but it is exclusive.
new 13.0 formula float

quartile

(range range, int quartile)
Returns the quartile (0-4) for the range.
new 13.0 formula float

quartile.inc

(range range, int quartile)
The same as quartile.
new 13.0 formula float

quartile.exc

(range range, int quartile)
Similar to quartile, but it is exclusive.
new 12.0 formula float

product

(range range1, range range2, ...)
Multiplies all values in its parameters.
new 13.0 formula float

multinominal

(range range1, range range2, ...)
Calculates multinominal of all values in its parameters as factorial of summary divided by multiply of factorials.
new 13.0 formula float

average...

(range range1, range range2, ...)
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.
new 13.0 formula float

averageif

(range range, string criteria,range average_range)
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
new 13.0 formula float

averageifs

(range average_range, range range1, string criteria1, range range2, string criteria2, ...)
Calculates average of all cells from average_range that satisfy multiple criteria. Parameters see sumif
new 13.0 formula float

trimmean

(range range, float percent)
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.
new 13.0 formula float

geomean

(range range1, range range2, ...)
Calculates geometric mean of all values in its parameters.
new 13.0 formula float

harmean

(range range1, range range2, ...)
Calculates harmonic mean of all values in its parameters.
new 13.0 formula float

median

(range range1, range range2, ...)
Returns statistical median (middle) value of all values in its parameters. For even number of values returns an average of the two medians.
new 13.0 formula float

mode...

(range range1, range range2, ...)
mode Returns the Mode (the most frequently occurring value) of a list of supplied numbers.
mode.sngl Like mode.
new 13.0 formula float

avedev

(range range1, range range2, ...)
Returns the average of the absolute deviations of values from their mean.
new 13.0 formula float

devsq

(range range1, range range2, ...)
Returns the sum of the squares of the deviations of a set of values from their sample mean.
new 13.0 formula float

stdev...

(range range1, range range2, ...)
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.
new 13.0 formula float

var...

(range range1, range range2, ...)
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.
new 13.0 formula float

covar...

(range range1, range range2, ...)
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.
new 13.0 formula float

forecast...

(float x, range range_y, range range_x)
forecast Predicts a future point on a linear trend line fitted to a supplied set of x- and y- values.
forecast.linear Like forecast.
new 13.0 formula float

intercept

(range range_y, range range_x)
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.
new 13.0 formula float

slope

(range range_y, range range_x)
Returns the slope of the linear regression line through a supplied series of x- and y- values.
new 13.0 formula float

stexy

(range range_y, range range_x)
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.
new 13.0 formula float

subtotal

(int function, range range1, range range2, ...)
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.
new 13.0 formula float

aggregate

(int function, int option, range range1, range range2, ...)
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.
new 13.0 formula float

mdeterm

(range range)
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>