Work with functions
Starting from v4.0, the package of DHTMLX Spreadsheet includes a set of predefined formulas that can be used for different types of calculations of strings and numbers. The formulas are compatible with Excel and Google Sheets.
Functions
Here's a list of all the available functions with detailed descriptions.
Boolean operators
You can compare two values via using logical expressions that in any given case will only return either TRUE or FALSE.
Operator  Example  Description 

=  =A1=B1  Returns TRUE if the value in cell A1 is equal to the value in cell B1; otherwise, FALSE. 
<>  =A1<>B1  Returns TRUE if the value in cell A1 is not equal to the value in cell B1; otherwise, FALSE. 
>  =A1>B1  Returns TRUE if the value in cell A1 is greater than the value in cell B1; otherwise, FALSE. 
<  =A1<B1  Returns TRUE if the value in cell A1 is less than the value in cell B1; otherwise, FALSE. 
>=  =A1>=B1  Returns TRUE if the value in cell A1 is greater than or equal to the value in cell B1; otherwise, FALSE. 
<=  =A1<=B1  Returns TRUE if the value in cell A1 is less than or equal to the value in cell B1; otherwise, FALSE. 
Check the example in our snippet tool.
Date functions
Function  Formula  Description 
DATE  =DATE(year,month,day)  Combines three separate values (year, month, and day) and returns a date. 
DATEDIF  =DATEDIF(start_date,end_date,unit)  Returns the number of days, months, or years between two dates. The unit argument is used to define which type of information you want returned. 
DATEVALUE  =DATEVALUE(date_text)  Converts a date that is stored as text to a serial number. 
DAY  =DAY(date)  Returns the day of the month as a number between 1 to 31 from a specified date. 
DAYS  =DAYS(end_date, start_date)  Returns the number of days between two dates. 
DAYS360  =DAYS360(start_date,end_date,[method]])  Returns the number of days between 2 dates, based on a 360day year (twelve 30days months). 
EDATE  =EDATE(start_date, months)  Returns the date on the same date of the month, n months in the past or future. 
EOMONTH  =EOMONTH(start_date, months)  Returns the date for the last day of the month, n months before or after the specified start date. 
ISOWEEKNUM  =ISOWEEKNUM(date)  Returns the number of the ISO week number of the year for the specified date. 
MONTH  =MONTH(date)  Returns the month of the year of the specified date. 
NETWORKDAYS  =NETWORKDAYS(start_date, end_date, [holidays])  Returns the number of whole working days between two dates. Working days exclude weekends and any dates specified in holidays. 
NETWORKDAYSINTL  =NETWORKDAYSINTL(start_date, end_date, [weekend], [holidays])  Returns the number of whole working days between two dates. The optional weekend parameter is used to specify which days of the week are considered weekends. Weekend days and holidays are not considered as workdays. 
NOW  =NOW()  Returns the current date. 
WEEKDAY  =WEEKDAY(date,[return_type])  Returns the day of the week for the specified date. The return_type argument is used to define which day of the week is considered the first day. 
WEEKNUM  =WEEKNUM(date,[return_type])  Returns the week number for the specified date. The return_type argument is used to define which day of the week is considered the first day. 
WORKDAY  =WORKDAY(start_date, days, [holidays])  Returns the date of the nearest working day n days in the future or past. Working days exclude weekends and any dates specified in holidays. 
WORKDAYINTL  =WORKDAYINTL(start_date, days, [weekend], [holidays])  Returns the date of the nearest working day n days in the future or past. The optional weekend parameter is used to specify which days of the week are considered weekends. Weekend days and holidays are not considered as workdays. 
YEAR  =YEAR(date)  Returns the year of the specified date. 
YEARFRAC  =YEARFRAC(start_date, end_date, [basis])  Returns the year of the specified date. The optional basis argument is used to define the type of day count basis. 
Check the example in our snippet tool.
Financial functions
Function  Formula  Description 
ACCRINT  =ACCRINT(issue, id, sd, rate, par, frequency, [basis], [calc_method]), where:
 Returns the accrued interest for a security that pays periodic interest. 
DB  =DB(cost, salvage, life, period, [month]), where:
 Calculates the depreciation of an asset for a specified period using the fixeddeclining balance method. 
DDB  =DDB(cost, salvage, life, period, [factor]), where:
 Calculates the depreciation of an asset for a specified period using the doubledeclining balance method or another method you specify. 
DOLLARDE  =DOLLARDE(fractional_dollar, fraction)  Converts a dollar price specified as an integer part and a fraction part into a dollar price displayed as a decimal number. 
DOLLARFR  =DOLLARFR(decimal_dollar, fraction)  Converts a decimal number into fractional dollar number. 
EFFECT  =EFFECT(nominal_rate, npery) nominal_rate must be >= 0, npery must be > 1.  Returns the effective annual interest rate on the base of the nominal annual interest rate and the number of compounding periods per year you specify. Works with numeric values. 
FV  =FV(rate, nper, pmt, [pv], [type]), where:
 Calculates the future value of an investment. 
FVSCHEDULE  =FVSCHEDULE(principal, schedule), where:
 Returns the future value of an initial principal (=present value) after applying a series of compound interest rates. 
IPMT  =IPMT(rate, per, nper, pv, [fv], [type]), where:
 Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. 
IRR  =IRR(values, [guess]), where:
 Returns the internal rate of return (IRR) for a series of cash flows that occur at regular intervals. 
ISPMT  =ISPMT(rate, per, nper, pv), where:
 Calculates the interest paid (or received) for the specified period of a loan (or investment) with even principal payments. 
NOMINAL  =NOMINAL(effect_rate, npery), effect_rate must be >= 0, npery must be > 1.  Returns the nominal annual interest rate on the base of the effective rate and the number of compounding periods per year you specify. 
NPER  =NPER(rate,pmt,pv,[fv],[type]), where:
 Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. 
NPV  =NPV(rate,value1,[value2],...), where:
 Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values). 
PDURATION  =PDURATION(rate, pv, fv), where:
All arguments must be positive values.  Returns the number of periods required by an investment to reach a specified value. 
PMT  =PMT(rate, nper, pv, [fv], [type]), where:
 Calculates the monthly payment for a loan based on constant payments and a constant interest rate. 
PPMT  =PPMT(rate, per, nper, pv, [fv], [type]), where:
 Returns the payment on the principal for a specified period for an investment based on periodic, constant payments and a constant interest rate. 
PV  =PV(rate, nper, pmt, [fv], [type]), where:
 Returns the present value of a loan or an investment, based on a constant interest rate. 
Check the example in our snippet tool.
Information functions
Function  Formula  Description 
ISBINARY  =ISBINARY(value)  Returns TRUE if the value is binary; otherwise, returns FALSE. 
ISBLANK  =ISBLANK(A1)  Returns TRUE if a cell is empty; otherwise, returns FALSE. 
ISEVEN  =ISEVEN(number)  Returns TRUE if a number is even, or FALSE if number is odd. Works with integer numbers. 
ISNONTEXT  =ISNONTEXT(value)  Returns TRUE if a cell contains any value except text; otherwise, returns FALSE. 
ISNUMBER  =ISNUMBER(value)  Returns TRUE if a cell contains a number; otherwise, returns FALSE. 
ISODD  =ISODD(number)  Returns TRUE if a number is odd, or FALSE if number is even. Works with integer numbers. 
ISTEXT  =ISTEXT(value)  Returns TRUE if a value is text; otherwise, returns FALSE. 
N  =N(value)  Returns a value converted to a number. 
Check the example in our snippet tool.
Math functions
ABS  Returns the absolute value of a number. The absolute value of a number is always positive. 
ACOS  Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is number. The number must be from 1 to 1, inclusive. The returned angle is given in radians in the range 0 (zero) to pi. 
ACOSH  Returns the inverse hyperbolic cosine of a number. The number must be greater than or equal to 1. 
ACOT  Returns the principal value of the arccotangent, or inverse cotangent, of a number. The returned angle is given in radians in the range 0 (zero) to pi. 
ACOTH  Returns the inverse hyperbolic cotangent of a number. The absolute value of the number must be greater than 1. 
ADD  Returns the sum of two values. Empty cells, logical values like TRUE, or text are ignored. 
ARABIC  Converts a Roman numeral to an Arabic numeral. 
ASIN  Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is number. The number must be from 1 to 1, inclusive. The returned angle is given in radians in the range pi/2 to pi/2. 
ASINH  Returns the inverse hyperbolic sine of a number. The inverse hyperbolic sine is the value whose hyperbolic sine is number. Works with real numbers. 
ATAN  Returns the arctangent, or inverse tangent, of a number. The arctangent is the angle whose tangent is number. The returned angle is given in radians in the range pi/2 to pi/2. Works with the tangent of the angle you want. 
ATAN2  Returns the arctangent of (x,y) coordinates. The arctangent is returned in radians between pi and pi, excluding pi. 
ATANH  Returns the inverse hyperbolic tangent of a number. Number must be between 1 and 1 (excluding 1 and 1). Works with real numbers. 
AVERAGE  Calculates the average (arithmetic mean) of a group of numbers. The function ignores logical values, empty cells and cells that contain text. However, cells with the value zero are included. 
BASE  Converts a number into the supplied base (radix). The number should be an integer and greater than or equal to 0 and less than 2^53. The base radix is what we want to convert the number into. It must be an integer from 2 to 36, inclusive. 
CEILING  Returns a number rounded up to the nearest integer or to the nearest multiple of the specified significance. 
COMBIN  Returns the number of combinations for two given integer numbers: the number of items (number) and the number of items in each combination (number_chosen):

COMBINA  Returns the number of combinations for two given integer numbers and includes repetitions. The numbers are: the number of items (number) and the number of items in each combination (number_chosen):

COS  Returns the cosine of an angle specified in radians. 
COSH  Returns the hyperbolic cosine of a real number. 
CSC  Returns the cosecant of an angle specified in radians. 
CSCH  Return the hyperbolic cosecant of an angle specified in radians. 
COT  Returns the cotangent of the an angle specified in radians. 
COTH  Returns the hyperbolic cotangent of a hyperbolic angle. 
COUNT  Counts the number of cells that contain numbers, and counts numbers within the list of arguments. Empty cells, logical values, text, or error values in the array or reference are not counted. 
COUNTA  Counts the number of cells that contain numbers, text, logical values, error values, and empty text (""); cells with zero values are excluded. The function does not count empty cells. 
COUNTBLANK  Returns the number of empty cells from a specified range. Cells with zero values are not counted. 
DECIMAL  Converts a text representation of a number in a given base (radix) into a decimal number. The base radix must be an integer from 2 to 36, inclusive. 
DEGREES  Converts radians into degrees. 
DIVIDE  Returns the result of dividing one number by another. 
EQ  Returns TRUE if the first argument is equal to the second; otherwise FALSE. 
EVEN  Returns a number rounded up to the nearest even integer. 
FACT  Returns the factorial of a number. The number must be from 1 to n. If number is not an integer, it is truncated. 
FACTDOUBLE  Returns the double factorial of a number. The number must be from 1 to n. If number is not an integer, it is truncated. 
FLOOR  Rounds number down, toward zero, to the nearest multiple of the specified significance. The significant must be from 1 to n. If the sign of number is positive, a value is rounded down and adjusted toward zero. If the sign of number is negative, a value is rounded down and adjusted away from zero. 
GCD  Returns the greatest common divisor of two or more integers. The function takes from 1 to 255 numeric values which are expected to be integers. If any value is not an integer, it is truncated. 
GT  Returns TRUE if the first argument is greater than the second; otherwise FALSE. 
GTE  Returns TRUE if the first argument is greater than or equal to the second; otherwise FALSE. 
INT  Returns a number rounded down to the nearest integer. 
LN  Returns the natural logarithm of a positive real number. 
LOG  Returns the logarithm of a positive real number to the base you specify. If base is omitted, it is assumed to be 10. 
LOG10  Returns the base10 logarithm of a positive real number. 
LT  Returns TRUE if the first argument is less than the second; otherwise FALSE. 
LTE  Returns TRUE if the first argument is less than or equal to the second; otherwise FALSE. 
MAX  Returns the largest value in a set of values. The function ignores empty cells, the logical values TRUE and FALSE, and text values. If the arguments contain no numbers, MAX returns 0 (zero). 
MIN  Returns the smallest number in a set of values. Empty cells, logical values, or text in the array or reference are ignored. If the arguments contain no numbers, MIN returns 0 (zero). 
MINUS  Returns the difference of two numbers. 
MOD  Returns the remainder after number is divided by divisor. The result has the same sign as divisor. 
MROUND  Returns a number rounded to the nearest multiple of the specified significance. The values of number and multiple must have the same sign. 
MULTINOMIAL  Returns the ratio of the factorial of a sum of values to the product of factorials. The function takes from 1 to 255 numeric values which must be greater than or equal to 0. 
MULTIPLY  Returns the result of multiplying two numbers. 
NE  Returns TRUE if the first argument is not equal to the second; otherwise FALSE. 
ODD  Returns a number rounded up to the nearest odd integer. 
PI  Returns the number 3.14159265358979 (the mathematical constant pi, accurate to 15 digits). 
POW  Returns the result of a number raised to a given power. Works with real numbers. 
POWER  Returns the result of a number raised to a given power. Works with real numbers. 
PRODUCT  Multiplies all the numbers given as arguments and returns the product. Only numbers in the array or reference are multiplied. Empty cells, logical values, and text in the array or reference are ignored. 
QUOTIENT  Returns the result of integer division without the remainder. Works with real numbers. 
RADIANS  Converts degrees to radians. 
RAND  Returns a random number that is greater than or equal to 0 and less than 1. Returns a new random number each time your spreadsheet recalculates. 
RANDBETWEEN  Returns a random number between two specified numbers. Returns a new random number each time your spreadsheet recalculates. 
ROMAN  Converts an arabic numeral to roman. 
ROUND  Returns a number rounded to a specified number of digits. 
ROUNDDOWN  Returns a number rounded down to a specified number of digits. 
ROUNDUP  Returns a number rounded up to a specified number of digits. 
SEC  Returns the secant of an angle specified in radians. Works with numeric values. 
SECH  Returns the hyperbolic secant of an angle specified in radians. Works with numeric values. 
SIN  Returns the sine of an angle specified in radians. 
SINH  Returns the hyperbolic sine of a real number. 
SQRT  Returns a positive square root of a number. 
SQRTPI  Returns the square root of a number multiplied by pi. The number must be greater than or equal to 0. 
STDEV  Calculates standard deviation based on data that represents a sample of population. The standard deviation is a measure of how widely values are dispersed from the average value (the mean). Empty cells, logical values, text, or error values in the array or reference are ignored. 
STDEVA  Calculates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean). Empty cells, logical values, text, or error values in the array or reference are ignored. 
STDEVP  Calculates standard deviation based on the entire population of numbers. The standard deviation is a measure of how widely values are dispersed from the average value (the mean). Empty cells, logical values, text, or error values in the array or reference are ignored. 
STDEVPA  Calculates standard deviation based on the entire population given as arguments, including text (evaluate as 0) and logical values (evaluate as 1 for TRUE, and as 0 for FALSE). The standard deviation is a measure of how widely values are dispersed from the average value (the mean). If an argument is an array or reference, only values in that array or reference are used. Empty cells and text values in the array or reference are ignored. Error values cause errors. 
SUBTOTAL  Returns a subtotal in a list or database. 
SUM  Returns the sum of supplied values. Empty cells, logical values like TRUE, or text are ignored. 
SUMPRODUCT  Multiplies range of cells or arrays and returns the sum of products. For valid products only numbers are multiplied. Empty cells, logical values, and text are ignored. Treats array entries that are not numeric as if they were zeros. 
SUMSQ  Returns the sum of the squares of the arguments. Empty cells, logical values, text, or error values in the array or reference are ignored. 
SUMX2MY2  Returns the sum of the difference of squares of corresponding values in two arrays. The arguments should be either numbers or names, arrays, or references that contain numbers. Empty cells, logical values, text, or error values in the array or reference are ignored. Zero values are included. 
SUMX2PY2  Returns the sum of the sum of squares of corresponding values in two arrays. The arguments should be either numbers or names, arrays, or references that contain numbers. Empty cells, logical values, text, or error values in the array or reference are ignored. Zero values are included. 
SUMXMY2  Returns the sum of squares of differences of corresponding values in two arrays. The arguments should be either numbers or names, arrays, or references that contain numbers. Empty cells, logical values, text, or error values in the array or reference are ignored. Zero values are included. 
TAN  Returns the tangent of an angle specified in radians. 
TANH  Returns the hyperbolic tangent of a real number. 
TRUNC  Truncates a number to an integer by removing the fractional part of the number. 
VAR  Returns the variance based on a sample. Empty cells, logical values, text, or error values in the array or reference are ignored. 
VARA  Returns the variance based on a sample of the population, including text (evaluate as 0) and logical values (evaluate as 1 for TRUE, and as 0 for FALSE). Empty cells and text values in the array or reference are ignored. 
VARP  Returns the variance of a population based on an entire population of numbers. Empty cells, logical values, text, or error values in the array or reference are ignored. 
VARPA  Returns the variance of a population based on an entire population, including text (evaluate as 0) and logical values (evaluate as 1 for TRUE, and as 0 for FALSE). Empty cells and text values in the array or reference are ignored. 
Check the example in our snippet tool.
Regex functions
Function  Formula  Description 
REGEXREPLACE  =REGEXREPLACE(text, regular_expression, replacement)  Replaces a part of a text string with a different text string using regular expressions. 
REGEXMATCH  =REGEXMATCH(text, regular_expression)  Returns TRUE if a text string matches the pattern in the regular expression and FALSE if it doesn't. 
REGEXEXTRACT  =REGEXEXTRACT(text, regular_expression)  Returns the part of the string that matches the pattern in the regular expression. 
Check the example in our snippet tool.
String functions
Function  Formula  Description 
CHAR  =CHAR(number)  Returns the character (from the character set used by your computer) specified by a number. Number must be between 1 and 255. 
CLEAN  =CLEAN(text)  Removes characters, which are not printed when you use the print option, from the text. 
CODE  =CODE(text)  Returns a numeric code for the first character in a text string. 
CONCATENATE  =CONCATENATE(A1,"",B2:C3)  Joins two or more text strings into one string. 
DOLLAR  =DOLLAR(number, decimals)  Converts a number to text using the currency format, based on the number of digits to the right/left of the decimal point you specify (by default, 2). 
EXACT  =EXACT(text1, text2)  Compares two strings and returns TRUE if they are exactly the same; otherwise, returns FALSE. 
FIND  =FIND(find_text, within_text, [start_num])  Returns the position (as a number) of one text string inside another, starting at the position you specify (by default, 1). 
FIXED  =FIXED(number, [decimals], [no_commas])  Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and converts the result to text. If no_commas is set to 1, the returned text won't include commas. 
JOIN  =JOIN(separator, value1, value2,...)  Concatenates values using a specified separator. 
LEFT  =LEFT(text, count)  Returns the first character or characters in a text string, based on the number of characters you specify. 
LEN  =LEN(text)  Returns the length of the specified string. 
LOWER  =LOWER(text)  Converts all letters in the specified string to lowercase. 
MID  =MID(text, start, count)  Returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify. 
NUMBERVALUE  =NUMBERVALUE (text, [decimal_separator], [group_separator])  Converts a number in text format to numeric value, using specified decimal and group separators. 
PROPER  =PROPER(text)  Sets the first character in each word to uppercase and converts all other characters to lowercase. 
REPT  =REPT(text, number_times)  Repeats text a specified number of times. 
RIGHT  =RIGHT(text, count)  Returns the last character or characters (rightmost) in a text string, based on the number of characters you specify. 
SEARCH  =SEARCH(find_text, within_text, [start_num])  Returns the position (as a number) of the first character of find_text inside within_text, starting at the position you specify (by default, 1). 
SUBSTITUTE  =SUBSTITUTE(text, old_text, new_text, [instance_num])  Replaces old text with new text in a text string. If you specify instance_num, only that instance of old_text is replaced; otherwise, all instances are replaced. 
T  =T(value)  returns text when given a text value and an empty string ("") for numbers, dates, and the logical TRUE/FALSE values. 
TRIM  =TRIM(text)  Removes all spaces from text except for single spaces between words. 
UPPER  =UPPER(text)  Converts text to uppercase. 
Check the example in our snippet tool.
Other functions
Function  Example  Description 
AND  =AND(logical1, [logical2], ...)  Returns either TRUE or FALSE depending on whether multiple conditions are met or not. 
CHOOSE  =CHOOSE(index_num, value1, [value2], ...)  Returns a value from the list of value arguments based on a position or index you specify. 
FALSE  =FALSE()  Returns the logical value FALSE. 
IF  =IF(condition, [value_if_true], [value_if_false])  Returns one value if a condition is TRUE and another value if it's FALSE. 
NOT  =NOT(logical)  Returns the opposite of a given logical or Boolean value. 
OR  =OR(logical1, [logical2], ...)  Returns TRUE if at least one of the logical expressions is TRUE; otherwise, FALSE. 
TRUE  =TRUE()  Returns the logical value TRUE. 
Check the example in our snippet tool.
Getting cell formula
Starting with v4.1, you can get the formula applied to a cell via the getFormula() method. The method takes the id of the cell as a parameter:
var formula = spreadsheet.getFormula("B2");
// > "ABS(C2)"