Editable formulas

FastGrid documentation

Editable formulas entered by users directly into cells.
For JavaScript formulas set by developers into data see JavaScript formulas.
When reading cell value with editable formula set, the cell direct value and JavaScript formula are ignored.
The editable formulas can contain only the listed operators and functions, all other JavaScript code is restricted for safety reasons.
The editable formulas can be used to calculate only row cells, unlike JavaScript formulas they cannot calculate toolbar cells and cell attributes.
The editable formulas can be entered by users into cells by value starting by "=" like in Excel, if set FormulaEditing = 1.

Formula rules

Formula rules are similar to MS Excel.

Strings
Strings are written in quotes, there can be used both " or '. But for export to Excel must be used only ". 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.
If decimal separator is ',', the function arguments separator is switched from ',' to ';'.
Permitted is exponential notation like 1.35e10 or 1.35e+10 or -123e-5.
Hexadecimal or octal notation is not permitted.

Dates
Date constants are not accepted. Date 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 ^ and & have different meaning in MS Excel and JavaScript, by default the MS Excel meaning is used, can be changed by FormulaJavaScript attribute.
Priority of operators is the same as in JavaScript and cannot be changed. You can always use parenthesis ( ) to change the priority.

Constants
There are defined constants: true and false.

Cell references
In the formulas there can be addressed any the grid cell as Row Index / id + Col.Index / id like in MS Excel. E.g. { id:1, E:{C:"A1+B3"}}
Column name can be "_" as actual column, row name can be "0" as actual row, e.g. { id:2, E:{C:"_1+B0"}} adds up left and above cell of the current cell.
All formulas containing circular references return error as NaN. Summary function ignore the NaN values.

Cell range references
In the formulas there can be addressed any range of cells in grid using operator colon ":" as corner_cell:oposite_corner_cell or Column1:Column2 or Row1:Row2
For example { id:1, E: { D:"sum(A2:B4,1:3,C:D,E2:F1)+A10+100" } }
The range separator is :.
It is possible to get intersection of cell ranges by operator space " ", e.g. A:{E:"sum(A1:D5 A2:D8)"}.

External references
It is possible to refer also cells or cells ranges in other grid / sheet by "!" operator. The grid name is case insensitive.
If the grid name contains spaces, it must be quoted. For example { A: { E:'="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 JavaScript formula functions, although they can have the same name!
For example sum function in JavaScript usage and in editable usage.
In the function arguments it is possible to pass any valid expression including other function calls.
The arguments in the function call are separated by comma or by semicolon if decimal separator is comma.
For example { id:1, E: { D:"sum(A2:B4,1:3,sum(C:D,E2:F1 E1:F3))+A10*20" } }
See the Functions list

Localization
The numbers in formulas can use dot or comma as decimal separator, defined in DecimalSeparator and InputDecimalSeparators.
If decimal separator is comma, the function arguments are separated by semicolon.
All other operators are fixed and cannot be changed.
The function names are always in English.

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

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.
FastGrid does not differ between error and N/A.
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 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: fvschedule, npv, pv, received, disc, price, pricedics, pricemat, oddfprice, oddlprice, tbillprice, irr, mirr, amordegrc, amorlinc, db, ddb, sln, syd, vdb, dollarde, dollarfr, cumipmt, cumprinc, ispmt, 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

Formula attributes

The attributes for suggesting formulas are desribed at menu Suggest.
Defines cell editable formulas in the row.
Defined as an object with values assigned to column ids. For example: { id:1, E:{ C:"A1+B1", D:"sum(A1:C3)/B4" } }
It supports also Array and String definition by AIndex like A attribute, but these definitions have slower processing and are used rarely.
If the cell contains editable formula in E, its direct value and JavaScript formulas in F / Formula are ignored.
If defined in default row, the values are copied to the rows' E attributes on loading (only where the row E item has null value), so later changes in default row do not change the cell formulas.
In rare cases cell editable formulas can be defined also in columns or default columns.
These values are copied to the rows' E attributes on loading (only where the row E item has null value), so later changes of columns do not change the cell formulas.
If permits entering editable formula into cells.
If set and entered value starts by '=', the value is saved to cell editable formula.
If not set, the cells can still contain editable formulas, but these cells cannot be edited and editable formulas cannot be entered into other cells.
If restricts entering editable formula into the cell, row or column. The not editable formula will be also invisible to users.
If set, the cell can still contain editable formulas, but this cell cannot be edited. And editable formulas cannot be entered into the cell.
If not set and entered value starts by '=', the value is saved to cell editable formula.

0	edit, mark, lock	cell formula is editable and marked editable, but is further affected by Editing / FormulaEditing / Lock. Can be locked by SetNoEdit.
1	noedit, mark, locked 0	cell formula is not editable and marked as not editable. Used as locked option 0 and can be unlocked by SetNoEdit.
2	noedit, mark	cell formula is not editable and marked as not editable.
3	noedit	cell formula is not editable and not marked as not editable.
4	readonly, mark	cell formula is read only and marked as read only.
-1	edit, mark, unlocked 0	cell formula is always editable and marked editable and not affected by Editing / FormulaEditing / Lock. Used as unlocked option 0 and can be locked by SetNoEdit.
-2	edit, mark	cell formula is always editable and marked editable and not affected by Editing / FormulaEditing / Lock.
-3	edit	cell formula is always editable and not marked editable and not affected by Editing / FormulaEditing / Lock.

Cells with not editable formulas are not marked now.
If all the editable formulas are displayed in their cells instead of their results.
If set and the entered formula is incorrect, it tries to add ")" to its end to correct it.
If permitted localized formula names, if defined for actual Language. If the language defines the FormulaFunctions tag, it must define all the formula names to use.
0 - no, always used only English formula names.
1 - yes, uses only localized formula names. not implemented, works as 2.
2 - yes, permits both English and localized formula names, but for suggests only localized names.
3 - yes, permits both English and localized formula names and also suggests both English and localized names if differ.
If edited cell formula colors cell ranges in different colors in the formula text and also in grid. Comma separated list or array of HTML colors to color the cell ranges in edited formula, if set FormulaColorRanges.
Comma separated list or array of HTML colors to color the cell ranges in grid, if set FormulaColorRanges.
If permitted specific JavaScript operators in editable formulas.
0 - no, using operators like "&&" or "|" will produce incorrect formulas. Also the "+" will not concatenate strings.
1 - yes, but ExcelVB operators "^" and "&" will be preferred.
2 - yes and the operators "^" and "&" will be preferred to ExcelVB.
3 - yes and the special ExcelVB operators will be restricted and produce incorrect formulas.
If set, the row or column cells are removed from aggregate calculations by both JavaScript and editable formulas.
By default it is set to 1 in all special rows and columns and to 0 in all data rows.
It is faster but less universal and safe than Get.
Returns cell JavaScript formula or editable formula. For edit = 0/null returns JavaScript formula, for edit = 1 return editable formula.
row can be null to read column JavaScript Formula, col can be null to read row JavaScript Formula, only if not set edit.
row, col can be also Toolbar cell, but only for JavaScript formula if not set edit.
Called to get JavaScript formula of any cell, row, column, toolbar and toolbar cell. Called to get editable formula of any cell. It is called from GetFormula with the same row, col and edit parameters.
formula is actual formula to be returned. Return formula or new formula.
Returning null is the same as returning formula. To return null value return global constant FGrid.Null.
Low level API event called many times, especially during calculations.
It is faster but less universal and safe than Set.
Sets formula to the cell JavaScript formula or editable formula.
For changes = null sets JavaScript formula, for changes >= 0 returns editable formula.
Returns true if the formula was changed, false if the formula is the same as the cell formula (no change was done) and null for error and incorrect formula.

changes controls how the cell is marked as changed if setting editable formula:
0 - simple set - fast, does not mark or clear changes, does not store or change original value, does not test if the value is the same.
1 - set and mark - marks changes, stores original value before the first change, tests if the value is the same. If the formula is the same as original value, clears the marked changes.
2 - set, mark and restore - like 2, but if formula is null restores original value and clears the marked changes.
3 - set and clear - clears marked changes and clears original value, the new cell value is taken as original unchanged value. Returns false if the value is the same as actual value and no original value is stored.
4 - set and test - like 0 but tests if the value is the same.

changes=1/2/3 is forced to 4 if not set first bit of CellChanges.

If changing JavaScript formula by changes = null, it tests if the value is the same, but does not mark the changes.
row, col can be also Toolbar cell, but only for JavaScript formula if not set changes.
Called before changing JavaScript formula of any cell, row, column, toolbar and toolbar cell and editable formula of any cell. It is called from SetFormula with the same row, col and changes parameters.
formula is formula string to be set. Return formula or new formula to change it.
Returning null is the same as returning formula. To return null formula return global constant FGrid.Null.
To cancel setting the formula return global constant FGrid.Cancel.
Called after changed JavaScript formula of any cell, row, column, toolbar and toolbar cell and editable formula of any cell. It is called from SetFormula with the same row, col and changes parameters.
formula is the formula that has been set.
It is called only if the formula was really changed, so it is not called after every OnSetFormula call.
Calculates formula like it is in the given cell. Does not cache the results. For edit = 1 caculates editable formula, for 0 calculates JavaScript formula.
Sets FormulaLocal to value and updates formulas settings.
If value is null, does not set nothing, just returns true or false.
Returns false if there are no localized functions in actual Language.
Adds custom formula function func to FastGrid globally under name. Use type = 1 or 3 to add Editable formula function.
If func is null and type&2, removes the function name, if exists.
For type&1 = 0 - adds to JavaScript formula functions, = 1 - adds to Editable formula functions.
For type&2 = 0 - if name exists returns false, = 1 if name exists, overwrites it.
localname is function name used in Editable formulas as localized name; if not set, name is used.
suggest is function text for suggest; if not set, localname or name is used.
Returns true for success or false if func is not function or name is not set or name exists and type&2 is not set.

Custom Editable formula functions
The func is added as method of internal calculation object and can use this keyword with:
this.Row - actual row object where it is calculated.
this.Col - actual column object where it is calculated.
this.Grid - actual grid object where it is calculated.
this.func(...) - any predefined or custom function in Editable formulas.
If the custom function is called with cell range reference, the parameter is array with cell values in the range. The array has also attributes Rows as array of rows in the range, Cols as array of colums in the range and Grid as source grid.

This example defines custom function sumsqrt as summary of square roots of all arguments (scalars, cell raferences, cell range references):
FGrid.AddFunction(function(){
   for(var i=0,o=0;i<arguments.length;i++) {
      var A = arguments[i]; if(!A||typeof(A)!="object") A = [A];
      for(var j=0;j<A.length;j++) { var a = A[j]; a = Math.sqrt(a); if(a) o += a; }
      }
   return o;
   },"sumsqrt",1,null,"sumsqrt - summary of square roots");

This example defines custom function avg as average all arguments (scalars, cell raferences, cell range references):
FGrid.AddFunction(function(){
   return this.sum.apply(this,arguments) / this.count.apply(this,arguments);
   },"avg",1,null,"avg - average of all arguments");
Chooses cells by mouse dragging during editing formula as cells reference. type = 0 cells range, 1 - whole rows range, 2 - whole columns range.
If set absolute, adds "$" to the reference to be absolute.

Defined names usable in editable formulas

The defined names are similar to Excel names, the name can be defined for globally for all sheets in Book or for particular sheet.
Name can be referred in formula by its name (case insensitive). Name defined in other sheet can be referred by the sheet id plus "!" as prefix (e.g. "='Sheet 1'!MyName").
If there is defined both global name and local name in actual sheet, not prefixed reference refers local name. The global name can be referred by "[0]!" prefix.
The name value can contain any string or cell range or formula to calculate.
If the name contains cell or cell range, it can be focused by SetFocus.

Global dialog to manage defined names can be shown by Grid.ShowDialog(null,null,'Names').
Names defined for this grid. If the grid is sheet inside Book, these names are accessible only in this sheet, not in the other sheets.
The names can be used in editable formulas or focused if contain cell reference.
The Names are defined as JSON object as { id1:"value1", id2:"value2", .... } or { id1:{ Name:"name1", Value:"value1", Text:"description1", Info:"comment1", MenuText:"custommenu1" }, ... }.
The id of the name should be lowercase, the Name is the original id, but can be case sensitive.
Names defined for this grid. If the grid is sheet inside Book, these names are defined for all the sheets. If more sheets define their GlobalNames, the GlobalNames are joined and all accessible in all sheets.
The definition is the same as Names.
Returns defined names according to the type:
1. bit &1 - 1 - returns only names containing references that can be focused.
2. bit &2 - 2 - returns name objects with attributes Name, Value, Sheet, Info, 0 returns only strings as name Name.
3. bit &4 - 4 - returns names from all sheets, 0 - returns names only in this sheet and global names.
4. bit &8 - 8 - sorts the names case insensitive.
Changes given names in array OldNames to NewNames, changes the name OldNames to the name in NewNames on the same index.
The name object should have define attributes: Name, Value, Sheet (grid id or null for global), Info (comment).
If the name in OldNames is null, the NewNames name is added.
If the name in NewNames is nill, the OldNames name is deleted.
If the name changes its Name or Sheet it updates all formulas in all sheets that referred the old Name to refer the new Name. changes is used when updating formulas, see SetFormula.
Returns true if something changes.
Shows dialog to add defined name for actually focused cells.
Global dialog to manage defined names can be shown by Grid.ShowDialog(null,null,'Names').

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 FastGrid 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. FastGrid does not differ between error and N/A. Returns 1 if the value is NaN. FastGrid does not differ between error and N/A. Returns 1 if the value is 0 or 1. FastGrid 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. FastGrid 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. FastGrid 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. FastGrid 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 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 returns hyperbolic sine of value.
cos returns cosine(-1 to 1) of value in radians.
cosh returns hyperbolic cosine of value.
tan returns tangent of value in radians.
tanh returns hyperbolic tangent of value.
asin returns arcsine in radians of value (-1 to 1).
asinh returns hyperbolic arcsine of value.
acos returns arccosine in radians of value (-1 to 1).
acosh returns hyperbolic arccosine of value.
atan returns arctangent in radians of value.
atanh 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; Like round, but rounds up. Examples: roundup(123.456,0) = 124; roundup(-123.456,2) = -123.45; 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.
Or if set ExcelDates, all dates are floating point numbers as count of days since 1/1/1900.
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 or, if set ExcelDates, from 1/1/1900 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 or, if set ExcelDates, in date 12/31/1899) 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 FastGrid 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 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 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 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 The same as 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.

Financial functions

Returns the net present value for a schedule of cash flows that is not necessarily periodic.
rate - the discount rate to apply to the cash flows.
values - a series of cash flows that corresponds to a schedule of payments in dates.
dates - a schedule of payment dates that corresponds to the cash flow payments.
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.
values - a series of cash flows that corresponds to a schedule of payments in dates.
dates - a schedule of payment dates that corresponds to the cash flow payments.
guess - an optional number that you guess is close to the result of xirr.