Working with Functions

Built-in Functions

SpreadSheet can work with the Excel functions. For more details on these functions, please see the documentation for Microsoft Excel.

Below you will find the full list of functions that are supported by SpreadSheet.

Math functions

ABS(num) - returns the absolute value of a number. The absolute value of a number is the number without its sign.
ACOS(num) - returns the arccosine of a number
ACOSH(num) - returns the inverse hyperbolic cosine of a number
ACOT(num) - returns the arccotangent of a number
ACOTH(num) - returns the hyperbolic arccotangent of a number
ADD(num1, num2) - returns the sum of two numbers
ARABIC(num) - converts a Roman number to Arabic, as a number
ASIN(num) - returns the arcsine of a number
ASINH(num) - returns the inverse hyperbolic sine of a number
ATAN(num) - returns the arctangent of a number
ATAN2(num1, num2) - returns the arctangent from x- and y-coordinates
ATANH(num) - returns the inverse hyperbolic tangent of a number
AVEDEV() - returns the average of the absolute deviations of data points from their mean
AVERAGE() - returns the average (arithmetic mean) of the arguments;
- if a range or a cell reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
AVERAGEA() - returns the average of its arguments, including numbers, text, and logical values
BASE(num, radix, minLength) - converts a number into a text representation with the given radix (base)
BINOMDIST(number_s, trials, probability_s, cumulative) - returns the individual term binomial distribution probability
BINOM.INV(trials, probability_s, alpha) - returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
BINOM.DIST.RANGE(trials, probability_s, number_s1, number_s2) - returns the probability of a trial result using a binomial distribution
BINOM.DIST(number_s, trials, probability_s, cumulative) - returns the individual term binomial distribution probability
BITAND(num1, num2) - returns a 'Bitwise And' of two numbers
BITLSHIFT(num, amount) - returns a value number shifted left by shift_amount bits
BITOR(num1, num2) - returns a bitwise OR of 2 numbers
BITRSHIFT(num, amount) - returns a value number shifted right by shift_amount bits
BITXOR(num1, num2) - returns a bitwise 'Exclusive Or' of two numbers
CEILING(num, significance, mode) - rounds a number to the nearest integer or to the nearest multiple of significance
COMBIN(num, number_chosen) - returns the number of combinations for a given number of objects
COMBINA(num, number_chosen) - returns the number of combinations with repetitions for a given number of items
COMPLEX(real, i, suffix) - converts real and imaginary coefficients into a complex number
CORREL(arr1, arr2) - returns the correlation coefficient between two data sets
COS(num) - returns the cosine of a number
COSH(num) - returns the hyperbolic cosine of a number
COT(num) - returns the hyperbolic cosine of a number
COTH(num) - returns the cotangent of an 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 are not empty in a range, zero is excluded
COUNTBLANK() - counts empty cells in a specified range of cells;
- cells with zero values are not counted
COVAR(arr1, arr2) - returns covariance, the average of the products of paired deviations
COVARIANCE.P(arr1, arr2) - returns covariance, the average of the products of paired deviations
COVARIANCE.S(arr1, arr2) - returns the sample covariance, the average of the products deviations for each data point pair in two data sets
CSC(num) - returns the cosecant of an angle
CSCH(num) - returns the hyperbolic cosecant of an angle
DEC2BIN(num, places) - converts a decimal number to binary
DEC2HEX(num, places) - converts a decimal number to hexadecimal
DEC2OCT(num, places) - converts a decimal number to octal
DECIMAL(num, radix) - converts a text representation of a number in a given base into a decimal number
DEGREES(num) - converts radians to degrees
DELTA(num1, num2) - tests whether two values are equal
DEVSQ() - returns the sum of squares of deviations
DIVIDE(num1, num2) - returns one number divided by another
EQ(v, v2) - returns TRUE if two specified values are equal and FALSE otherwise
ERF(num, upper) - returns the error function
ERFC(num, upper) - returns the complementary error function
EVEN() - returns number rounded up to the nearest even integer
EXP(num) - returns e raised to the power of a given number
FACT(num) - returns the factorial of a number
FACTDOUBLE(num) - returns the double factorial of a number
FISHER(num) - returns the Fisher transformation
FISHERINV(num) - returns the inverse of the Fisher transformation
FLOOR(num, significance) - rounds a number down, toward zero
GAMMA(num) - returns the Gamma function value
GCD() - returns the greatest common divisor
GEOMEAN() - returns the geometric mean
GESTEP(num, step) - tests whether a number is greater than a threshold value
GT(num1, num2) - returns TRUE if the first argument is strictly greater than the second, and FALSE otherwise
GTE(num1, num2) - returns TRUE if the first argument is greater than or equal to the second, and FALSE otherwise
HARMEAN() - returns the harmonic mean
HEX2BIN(num, places) - converts a hexadecimal number to binary
HEX2DEC(num, places) - converts a hexadecimal number to decimal
HEX2OCT(num, places) - converts a hexadecimal number to octal
IMABS(inumber) - returns the absolute value (modulus) of a complex number
IMAGINARY(inumber) - returns the imaginary coefficient of a complex number
IMCONJUGATE(inumber) - returns the complex conjugate of a complex number
IMCOS(inumber) - returns the cosine of a complex number
IMCOSH(inumber) - returns the hyperbolic cosine of a complex number
IMCOT(inumber) - returns the cotangent of a complex number
IMCSC(inumber) - returns the cosecant of a complex number
IMCSCH(inumber) - returns the hyperbolic cosecant of a complex number
IMDIV(inumber1, inumber2) - returns the quotient of two complex numbers
IMEXP(inumber) - returns the exponential of a complex number
IMLN(inumber) - returns the natural logarithm of a complex number
IMPOWER(inumber, pow) - returns a complex number raised to an integer power
IMPRODUCT() - returns the product of complex numbers
IMREAL(inumber) - returns the real coefficient of a complex number
IMSEC(inumber) - returns the secant of a complex number
IMSECH(inumber) - returns the hyperbolic secant of a complex number
IMSIN(inumber) - returns the sine of a complex number
IMSINH(inumber) - returns the hyperbolic sine of a complex number
IMSQRT(inumber) - returns the square root of a complex number
IMSUB(inumber1, inumber2) - returns the difference between two complex numbers
IMSUM() - returns the sum of complex numbers
IMTAN(inumber) - returns the tangent of a complex number
INT(num) - rounds a number down to the nearest integer
LARGE(arr, value) - returns the k-th largest value in a data set
LN(num) - returns the natural logarithm of a number
LOG(num, base) - returns the logarithm of a number to a specified base
LOG10(num) - returns the base-10 logarithm of a number
LT(num1, num2) - returns TRUE if the first argument is strictly less than the second, and FALSE otherwise
LTE(num1, num2) - returns TRUE if the first argument is less than or equal to the second, and FALSE otherwise
MAX() - returns the largest value in a set of values;
- empty cells, logical values, or text in the array or reference are ignored. If the arguments contain no numbers, MAX returns 0 (zero).
MEDIAN() - returns the median of the given numbers
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(num1, num2) - returns the difference of two numbers
MOD(num, divisor) - returns the remainder from division
MROUND(num, factor) - returns a number rounded to the desired multiple
MULTINOMIAL() - returns the multinomial of a set of numbers
MULTIPLY(num1, num2) - returns the product of two numbers
NE(v, v2) - returns TRUE if two specified values are not equal and FALSE otherwise
OCT2BIN(num, places) - converts an octal number to binary
OCT2DEC(num, places) - converts an octal number to decimal
OCT2HEX(num, places) - converts an octal number to hexadecimal
ODD() - returns number rounded up to the nearest odd integer
PERCENTILE(arr, k_value, mode) - returns the k-th percentile of values in a range
PERCENTILE.INC(arr, k_value, mode) - returns the percentage rank of a value in a data set
PERCENTILE.EXC(arr, k_value) - returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set
PERMUT(num, number_chosen) - returns the number of permutations for a given number of objects
PI - returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits
POW(base, exponent) - returns the result of a number raised to a power
POWER() - returns the result of a number raised to a 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.
QUARTILE(arr, q, mode) - returns the quartile of a data set
QUARTILE.INC(arr, q, mode) - returns the quartile of a data set
QUARTILE.EXC(arr, q) - returns the quartile of the data set, based on percentile values from 0..1, exclusive
QUOTIENT(num, div) - returns the integer portion of a division. Use this function when you want to discard the remainder of a division;
- works with real numbers.
RADIANS(num) - converts degrees to radians
RAND() - returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.
RANDBETWEEN(bottom, top) - returns a random number between the numbers you specify
ROMAN(num) - converts an arabic numeral to roman, as text
ROUND(num, digits) - rounds a number to a specified number of digits
ROUNDDOWN(num, digits) - rounds a number down to a specified number of digits
ROUNDUP(num, digits) - rounds a number up to a specified number of digits
SEC(num) - returns the secant of an angle
SECH(num) - returns the hyperbolic secant of an angle
SIGN(num) - returns the sign of a number
SIN(num) - returns the sine of the given angle
SINH(num) - returns the hyperbolic sine of a number
SMALL(arr, value) - returns the k-th smallest value in a data set
SQRT(num) - returns a positive square root
SQRTPI(num) - returns the square root of (number * pi)
STDEV(num1,[num2], ...) - estimates standard deviation based on a sample
STDEV.S(num1,[num2], ...) - estimates standard deviation based on a sample
STDEV.P(num1,[num2], ...) - calculates standard deviation based on the entire population
STDEVA(v1, [v2], ...) - estimates standard deviation based on a sample, including numbers, text, and logical values
STDEVP(num1,[num2], ...) - calculates standard deviation based on the entire population given as arguments. 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(v1, [v2], ...) - calculates standard deviation based on the entire population, including numbers, text, and logical values
STEYX(arr1, arr2) - returns the standard error of the predicted y-value for each x in the regression
SUBTOTAL(function_code) - returns a subtotal in a list or database
SUM() - adds values;
- empty cells, logical values like TRUE, or text are ignored
SUMPRODUCT() - multiplies corresponding components in the given arrays, and returns the sum of those 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(array_x, array_y) - returns the sum of the difference of squares of corresponding values in two arrays
SUMX2PY2(array_x, array_y) - returns the sum of the sum of squares of corresponding values in two arrays
SUMXMY2(array_x, array_y) - returns the sum of squares of differences of corresponding values in two arrays
TAN(num) - returns the tangent of a number
TANH(num) - returns the hyperbolic tangent of a number
TRUNC(num) - truncates a number to an integer by removing the fractional part of the number
VAR(num1,[num2], ...) - estimates variance based on a sample
VAR.S(num1,[num2], ...) - estimates variance based on a sample
VAR.P(num1,[num2], ...) - calculates variance based on the entire population
VARA(v1, [v2], ...) - estimates variance based on a sample, including numbers, text, and logical values
VARP(num1,[num2], ...) - calculates variance based on the entire population
- empty cells, logical values, text, or error values in the array or reference are ignored
VARPA(v1, [v2], ...) - calculates variance based on the entire population, including numbers, text, and logical values
WEIBULL(x, alpha, beta, cumulative) - calculates variance based on the entire population, including numbers, text, and logical values
WEIBULL.DIST(x, alpha, beta, cumulative) - returns the Weibull distribution

String functions

ARRAYTOTEXT(arr, format) - returns an array of text values from any specified range
CHAR(num) - returns the character specified by the code number
CLEAN(text) - removes all nonprintable characters from text
CODE(text) - returns a numeric code for the first character in a text string
CONCATENATE(A1,B2, C2:D4) - joins several text items into one text item
DOLLAR(num, decimals) - converts a number to text, using the $ (dollar) currency format
EXACT(text1, text2) - checks to see if two text values are identical
FIND(text, text2, pos) - finds one text value within another (case-sensitive)
FIXED(number, decimals, comas) - formats a number as text with a fixed number of decimals
JOIN(separator) - concatenates the elements of one or more one-dimensional arrays using a specified separator
LEFT(text, count) - returns the first character or characters (leftmost) in a text string, based on the number of characters you specify
LEN(text) - returns the number of characters in a text string
LOWER(text) - converts all uppercase letters in a text string to lowercase
MID(text, start, count) - returns a specific number of characters from a text string starting at the position you specify
NUMBERVALUE(text, decimals, group_separator) - converts text to number in a locale-independent manner
PROPER(text) - capitalizes the first letter in each word of a text value
REGEXEXTRACT(text, regexp) - extracts the first matching substrings according to a regular expression
REGEXMATCH(text, regexp) - whether a piece of text matches a regular expression
REGEXREPLACE(text, regexp, replacement) - replaces part of a text string with a different text string using regular expressions
REPLACE(old_text, start_num, num_chars, new_text) - replaces characters within text
REPT(text, number) - repeats text a given number of times
RIGHT(text, count) - returns the last character or characters (rightmost) in a text string, based on the number of characters you specify
SEARCH(text, within, position) - finds one text value within another (not case-sensitive)
SUBSTITUTE(text, old, new_text, occurrence) - substitutes new text for old text in a text string
T(val) - converts its arguments to text
TRIM(text) - removes spaces from text
UPPER(text) - converts text to uppercase

Date functions

DATE(year, month, day) - creates a valid date from individual year, month, and day components
DATEDIF(start, end, unit) - returns the difference between two date values in years, months, or days
DATEVALUE(string) - converts a date in the form of text to a serial number
DAY(date) - returns the day of the month as a number between 1 to 31 from a given date
DAYS(date, date2) - returns the number of days between two dates
DAYS360(date, date2, method) - calculates the number of days between two dates based on a 360-day year
EDATE(date, month) - returns the serial number of the date that is the indicated number of months before or after the start date
EOMONTH(date, month) - returns the serial number of the last day of the month before or after a specified number of months
HOUR(date) - returns an hour as a number between 0 to 23 from a given date
ISOWEEKNUM(date) - returns the number of the ISO week number of the year for a given date
MINUTE(date) - returns a minute as a number between 0 to 59 from a given date
MONTH(date) - extracts the month from a given date as number between 1 to 12
NETWORKDAYS(date1, date2, holidays) - returns the number of whole workdays between two dates
NETWORKDAYS.INTL(date1, date2, weekend, holidays) - returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days
NOW() - returns the current date and time, updated continuously when a worksheet is changed or opened
SECOND(date) - returns a second as a number between 0 to 59 from a given date
TIME(hour, minute, second) - allows to create a time with individual hour, minute, and second components
TIMEVALUE(text) - converts a time in the form of text to a serial number
WEEKDAY(date, type) - converts a serial number to a day of the week
WEEKNUM(date, type) - converts a serial number to a number representing where the week falls numerically with a year
WORKDAY(date1, days, holidays) - returns the serial number of the date before or after a specified number of workdays
WORKDAY.INTL(date1, days, weekend, holidays) - returns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days
YEAR(date) - returns the year component of a date as a 4-digit number
YEARFRAC(date1, date2, basis) - returns the year fraction representing the number of whole days between start_date and end_date

Other functions

ACCRINT(issue, _first, settlement, rate, par, frequency, basis) - returns the accrued interest for a security that pays periodic interest
AND() - returns TRUE if all of its arguments are TRUE
CHOOSE(num) - chooses a value from a list of values
DB(cost, salvage, life, period, month) - returns the depreciation of an asset for a specified period by using the fixed-declining balance method
DDB(cost, salvage, life, period, factor) - returns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify
DOLLARDE(dollar, fraction) - converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number
DOLLARFR(dollar, fraction) - converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction
EFFECT(rate, periods) - returns the effective annual interest rate
FALSE() - returns the logical value FALSE
FV(rate, periods, payment, value, type) - returns the future value of an investment
FVSCHEDULE(principal, schedule) - returns the future value of an initial principal after applying a series of compound interest rates
HLOOKUP(v, a, row, mode) - looks in the top row of an array and returns the value of the indicated cell
HYPERLINK (url, text) - creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet. It is possible to add links to the cells, ranges or named ranges into HYPERLINK (via #) (e.g. HYPERLINK("#A9:E15","Table 1"))
IF() (smth is true, then do smth, otherwise do smth else) - specifies a logical test to perform
(e.g. IF(A1 < 10, "Yes" , "No"))
IMAGE (url) - displays an image the URL of which is set as a parameter of the function
INDEX(a, row, column) - uses an index to choose a value from a reference or array
IPMT(rate, period, periods, present, future, type) - returns the interest payment for an investment for a given period
IRR(values, guess) - returns the internal rate of return for a series of cash flows
ISBINARY(val) - returns TRUE if the value is binary
ISBLANK(val) - returns TRUE if the value is blank
ISEVEN(num) - returns TRUE if the number is even
ISNONTEXT(v) - returns TRUE if the value is not text
ISNUMBER(num) - returns TRUE if the value is a number
ISODD(num) - returns TRUE if the number is odd
ISPMT(rate, period, periods, value) - calculates the interest paid during a specific period of an investment
ISTEXT(v) - returns TRUE if the value is text
LOOKUP(v, a, b) - looks up values in a vector or array
MATCH(v, a, mode) - looks up values in a reference or array
N(num) - returns a value converted to a number
NOMINAL(rate, periods) - returns the annual nominal interest rate
NOT(value) - reverses the logic of its argument
NPER(rate, payment, present, future, type) - returns the number of periods for an investment
NPV() - returns the net present value of an investment based on a series of periodic cash flows and a discount rate
OR() - returns TRUE if any argument is TRUE
PDURATION(rate, present, future) - returns the number of periods required by an investment to reach a specified value
PMT(rate, periods, present, future, type) - returns the periodic payment for an annuity
PPMT(rate, period, periods, present, future, type) - returns the payment on the principal for an investment for a given period
PV(rate, periods, payment, future, type) - returns the present value of an investment
SPARKLINE (range, type, color1, color2) - draws a sparkline according to the specified parameters
SYD(cost, salvage, life, per) - returns the sum-of-years' digits depreciation of an asset for a specified period
TBILLPRICE(settlement, maturity, discount) - returns the price per $100 face value for a Treasury bill
TBILLYIELD(settlement, maturity, pr) - returns the yield for a Treasury bill
TRUE() - returns the logical value TRUE
VLOOKUP(v, a, column, mode) - looks in the first column of an array and moves across the row to return the value of a cell
XLOOKUP(v, a, b, def, mode, search) - searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
XMATCH(v, a, mode, search) - returns the relative position of an item in an array or range of cells.

Creating a Custom Function

If you need a function that isn't listed above, you can create your own function and use it in the spreadsheet.

To add a new function, use the registerMathMethod method. You should pass two parameters to it:

the name of the function. It should be defined in uppercase
the function handler. It takes all the necessary parameters and contains the function's logic. The handler receives either of the following notions as a parameter:
- a single value (a reference to a cell)
- an array of values (a reference to a range of cells)

For example, let's create a function "FIRST" that will return the value of the first cell in a range. So, we should add our function to the list as follows:

$$("ss").registerMathMethod("FIRST", function(range){
    return range[0];
});

And we can use the new method as:

=FIRST(H3:H5);

In the above example we have passed one parameter to the handler, the cell range. The "FIRST" function returns the value of the first cell.

If you have not checked yet, be sure to visit site of our main product Webix popular javascript framework and page of javascript spreadsheet library product.