# List of Formulas and Functions

The following table provides a list of the formulas and functions the Spreadsheet supports.

Formulas and functions Description
ABS Returns the absolute (nonnegative) value of a number
ACOS Returns the principal value of the arccosine of a number. The angle is returned in radians.
ACOSH Returns the principal value of the inverse hyperbolic cosine of a number
ACOT Returns the principal value of the arccotangent of a number. The angle is returned in radians.
ACOTH Returns the hyperbolic arccotangent of a number
AGGREGATE Returns an aggregate of a list or database
ARABIC Converts Roman numbers to Arabic as numbers
AREAS Returns the number of areas in a reference
ASIN Returns the principal value of the arcsine of a number. The angle is returned in radians.
ASINH Returns the principal value of the inverse hyperbolic sine of a number
ATAN Returns the principal value of the arctangent of a number. The angle is returned in radians.
ATAN2 Returns the principal value of the arctangent from x- and y- coordinates in radians
ATANH Returns the principal value of the inverse hyperbolic tangent of a number
AVEDEV Calculates the average of the absolute deviations of listed values
AVERAGE Returns the average of a set of numbers
AVERAGEA Returns the average of values, including numbers, text, and logical values
AVERAGEIF Returns the average of all cells in a range based on a given criteria
AVERAGEIFS Returns the average of all cells in a range based on multiple criteria
BASE Converts a number into a text representation with the given base
BETA.DIST Returns the beta cumulative distribution function
BETA.INV Returns the inverse of the cumulative distribution function for a specified beta distribution
BETADIST Returns the value of the probability density function or the cumulative distribution function for the beta distribution
BINOM.DIST Returns the individual term binomial distribution probability
BINOM.DIST.RANGE Returns the probability of a trial result using a binomial distribution
BINOM.INV Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
BINOMDIST Returns the binomial distribution probability
CEILING Rounds a number to the nearest integer or to the nearest multiple of significance
CEILING.MATH Rounds a number up, to the nearest integer or to the nearest multiple of significance
CEILING.PRECISE Rounds a number the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up.
CHAR Return character represented by a given number
CHISQ.DIST Returns the cumulative beta probability density function
CHISQ.DIST.RT Returns the one-tailed probability of the chi-squared distribution
CHISQ.INV Returns the cumulative beta probability density function
CHISQ.INV.RT Returns the inverse of the one-tailed probability of the chi-squared distribution
CHISQ.TEST Returns the test for independence
CHOOSE Uses an index to return a value from a list of values
CLEAN Removes all nonprintable characters from a text
CODE Return a numeric value corresponding to the first character in a text string
COLUMN Returns the column number(s) of a reference
COLUMNS Returns the number of columns in a given range
COMBIN Returns the number of combinations for a given number of objects
COMBINA Returns the number of combinations with repetitions for a given number of objects
CONCATENATE Joins a number of text strings into one text string
CONFIDENCE.NORM Returns the confidence interval for a population mean
CONFIDENCE.T Returns the confidence interval for a population mean, using a Student's t distribution
COS Returns the cosine of a number. The angle is returned in radians.
COSH Returns the hyperbolic cosine of a number
COT Returns the cotangent of an angle, specified in radians
COTH Returns the hyperbolic cotangent of a number
COUNT Counts the number of numbers in a list of arguments
COUNTA Counts the number of values in a list of arguments
COUNTBLANK Counts the number of blank cells in a range
COUNTIF Counts the number of cells in a range that meet a criteria
COUNTIFS Counts the number of cells in a range that meet multiple criteria
COVAR Calculates the covariance between two cell ranges
COVARIANCE.P Returns covariance, the average of the products of paired deviations
COVARIANCE.S Returns the sample covariance, the average of the products deviations for each data point pair in two data sets
CRITBINOM Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
CSC Returns the cosecant of an angle, specified in radians
CSCH Returns the hyperbolic cosecant of an angle, specified in radians
DATE Returns a date value constructed from a year, month, and day values
DATEVALUE Returns the date converting it in the form of text to a serial number
DAY Returns the day by converting it from a serial number
DAYS Returns the number of days between two dates
DAYS360 Returns the number of days between two dates using the 360-day year
DECIMAL Converts a text representation of a number in a given base into a decimal number
DOLLAR Converts a number to text, using the `\$` currency format
EDATE Returns the serial number of the date that is the indicated number of months before or after the start date
EOMONTH Returns the serial number of the last day of the month before or after a specified number of months
ERF Returns the error function
ERFC Returns the complementary error function
EVEN Rounds a number up to the nearest even integer
EXACT Reports if two text values are equal using a case-sensitive comparison
EXP Returns `e` raised to the power of a given number
EXPON.DIST Returns the exponential distribution
F.DIST Returns the `F` probability distribution
F.DIST.RT Returns the `F` probability distribution
F.INV Returns the inverse of the `F` probability distribution
F.INV.RT Returns the inverse of the `F` probability distribution
F.TEST Returns the result of an `F`-test
FACT Return factorial of a number
FACTDOUBLE Returns the double factorial of a number
FALSE Returns logical value `False`
FIND Returns the starting position of a given text
FISHER Returns the `Fisher` transformation
FISHERINV Returns the inverse of the `Fisher` transformation
FIXED Rounds the number to a specified number of decimals and formats the result as a text
FLOOR Rounds a number down to the nearest multiple of the second parameter
FLOOR.MATH Rounds a number down, to the nearest integer or to the nearest multiple of significance
FLOOR.PRECISE Rounds a number down to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded down.
FORECAST Assumes a future value based on existing x- and y- values
FORMULATEXT Returns the formula at the given reference as text
FREQUENCY Returns a frequency distribution as a vertical array
GAMMA Return `Gamma` function value
GAMMA.DIST Returns the `Gamma` distribution
GAMMA.INV Returns the inverse of the `Gamma` cumulative distribution
GAMMALN Returns the natural logarithm of the `Gamma` function
GAUSS Returns 0.5 less than the standard normal cumulative distribution
GCD Returns the greatest common divisor (GCD)
GEOMEAN Returns the geometric mean of a sequence
HARMEAN Returns the harmonic mean of a sequence
HLOOKUP Looks for a matching value in the first row of a given table, and returns the value of the indicated row
HOUR Converts a serial number to an hour
IF Returns one of two values, depending on a condition
IFERROR Returns a specified value if a formula evaluates to an error; otherwise, returns the result of the formula
INDEX Returns a value or a reference to a value from within a table or range
INDIRECT Returns a reference indicated by a text value
INT Rounds a number down to the nearest integer
INTERCEPT Returns the intercept of the linear regression line for the given data
ISBLANK Returns `True` if the referenced cell is blank; else returns `False`
ISERR Returns `True` if the value is any error except `#N/A`; else returns `False`
ISERROR Returns `True` if the value is any error; else returns `False`
ISEVEN Returns `True` if the value is even; else returns `False`
ISLOGICAL Returns `True` if the value is logical; else returns `False`
ISNA Returns `True` if the value is the `#N/A` error; else returns `False`
ISNONTEXT Returns `True` if the value is not text; else returns `False`
ISNUMBER Returns `True` if the value is a number; else returns `False`
ISO.CEILING Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance
ISODD Returns `True` if the value is odd; else returns `False`
ISOWEEKNUM Returns the ISO week number of the year for a given date
ISREF Returns `True` if the value is a reference; else returns `False`
ISTEXT Returns `True` if the value is text; else returns `False`
KURT Returns the kurtosis (“peakedness”) of a data set
LARGE Finds the nth largest value in a list
LCM Returns the least common multiple
LEFT Returns a selected number of text characters from the left
LEN Returns the number of characters from a given text
LINEST Returns the parameters of a (simple or multiple) linear regression equation for the given data and, optionally, statistics on this regression
LN Returns the natural logarithm of a number
LOG Returns the logarithm of a number to a specified base
LOG10 Returns the base-10 logarithm of a number
LOGEST Returns the parameters of an exponential regression equation for the given data obtained by linearizing this intrinsically linear response function and returns, optionally, statistics on this regression
LOGNORM.DIST Returns the cumulative lognormal distribution
LOGNORM.INV Returns the inverse of the lognormal cumulative distribution
LOWER Converts text to lowercase
MATCH Finds an item in a range of cells, and returns its relative position (starting from 1)
MAX Returns the maximum value in a set of numbers
MDETERM Returns the determinant of a matrix
MEDIAN Returns the median (middle) value in a list of numbers
MID Returns a specific number of characters from a text string, starting at a specified position
MIN Returns the minimum value in a set of numbers
MINUTE Converts a serial number into a minute
MINVERSE Returns the inverse of a matrix
MMULT Returns the matrix output of two arrays
MOD Returns the remainder when one number is divided by another number
MODE.MULT Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data
MODE.SNGL Returns the most common value in a data set
MONTH Converts a serial number to a month
MROUND Rounds the number to the desired multiple
MULTINOMIAL Returns the multinomial for a given set of values
MUNIT Creates a unit matrix of a specified dimension
N Returns the number of a value
NA Returns the error value `#N/A`
NEGBINOM.DIST Returns the negative binomial distribution
NEGBINOMDIST Returns the negative binomial distribution
NETWORKDAYS Returns the number of whole workdays between two dates
NORM.DIST Returns the normal cumulative distribution
NORM.INV Returns the inverse of the normal cumulative distribution
NORM.S.DIST Returns the standard normal cumulative distribution
NORM.S.INV Returns the inverse of the standard normal cumulative distribution
NOT Reverses the logic of its argument
NOW Returns the serial number of the current date and time
ODD Rounds a number up to the nearest odd integer, where "up" means "away from 0"
OFFSET Modifies the position and dimension of a reference
PEARSON Returns the `Pearson` correlation coefficient of two data sets
PERCENTILE Calculates the x-th sample percentile of values in a range
PERCENTILE.EXC Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive
PERCENTILE.INC Returns the k-th percentile of values in a range
PERCENTRANK Returns the percentage rank of a value in a sample
PERCENTRANK.EXC Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set
PERCENTRANK.INC Returns the percentage rank of a value in a data set
PHI Returns the value of the density function for a standard normal distribution
PI Returns the approximate value of pi
POISSON.DIST Returns the `Poisson` distribution
POWER Returns the result of a number raised to the power of another number
PROB Returns the probability that values in a range are between two limits
PRODUCT Multiplies the set of numbers, including all numbers inside ranges
PROPER Capitalizes the first letter in each word of a text value
QUARTILE Returns the quartile of a data set
QUARTILE.EXC Returns the quartile of the data set, based on percentile values from 0..1, exclusive
QUARTILE.INC Returns the quartile of a data set
QUOTIENT Returns the integer portion of a division
RAND Returns a random number between 0 (inclusive) and 1 (exclusive)
RANDBETWEEN Returns a random number between specified values
RANK Returns the rank of a number in a list of numbers
RANK.AVG Returns the rank of a number in a list of numbers
RANK.EQ Returns the rank of a number in a list of numbers
REPLACE Replaces characters within text
REPT Repeats text a specified number of times
RIGHT Returns the rightmost characters from a text value
ROMAN Converts Arabic numbers to Roman as text
ROUNDDOWN Rounds a number down, towards zero, to the number of digits specified by `digits`
ROUNDUP Rounds a number up, away from 0 (zero), to the number of digits specified by `digits`
ROW Returns the row number(s) of a reference
ROWS Returns the number of rows in a reference
RSQ Returns the square of the `Pearson` product moment correlation coefficient
SEARCH Finds a text value within another text value (not case-sensitive)
SEC Returns the secant of an angle specified in radians
SECH Returns the hyperbolic secant of a given angle specified in radians
SECOND Converts a serial number to a second. This function presumes that leap seconds never exist.
SERIESSUM Returns the sum of a power series based on the formula
SIGN Returns the sign of a number
SIN Returns the sine of an angle specified in radians
SINH Returns the hyperbolic sine of a number
SLOPE Calculates the slope of the linear regression line
SMALL Finds the n-th smallest value in a data set
SQRT Returns a positive square root of a number
SQRTPI Returns the square root of a number multiplied by pi
STDEV.P Calculates the standard deviation based on the entire population
STDEV.S Estimates the standard deviation based on a sample
STEYX Returns the standard error of the predicted y-value for each x in the regression
SUBSTITUTE Substitutes new text for old text string
SUBTOTAL Evaluates a function on a range
SUM Sums (adds) the set of numbers, including all numbers in a range
SUMIF Sums the values of cells in a range that meet a criteria
SUMIFS Sums the values of cells in a range that meet multiple criteria
SUMPRODUCT Returns the sum of the products of corresponding array elements
SUMSQ Sums (adds) the set of squares of numbers, including all numbers in a range
SUMX2MY2 Returns the sum of the difference between the squares of corresponding values in two arrays
SUMX2PY2 Returns the sum of squares of corresponding values in two arrays
SUMXMY2 Returns the sum of squares of corresponding values in two arrays
T Converts its arguments to text; else returns a 0-length text value
T.DIST Returns the Percentage Points (probability) for the Student t-distribution
T.DIST.2T Returns the Percentage Points (probability) for the Student t-distribution
T.DIST.RT Returns the Student's t-distribution
T.INV Returns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom
T.INV.2T Returns the inverse of the Student's t-distribution
T.TEST Returns the probability associated with a Student's t-test
TAN Returns the tangent of a number in radians
TANH Returns the hyperbolic tangent of a number
TEXT Formats a number and converts it to text
TIME Constructs a time value from hours, minutes, and seconds
TIMEVALUE Returns the serial number of a particular time
TODAY Returns the serial number of today's date
TRANSPOSE Returns the transpose of an array
TRIM Removes spaces from text; replaces all internal multiple spaces with a single space
TRIMMEAN Returns the mean of the interior of a data set, ignoring a proportion of high and low values
TRUE Returns the logical value `True`
UNICHAR Returns the character represented by the given numeric value according to the Unicode Standard
UNICODE Returns the Unicode code point that corresponds to the first character of a text value
UPPER Converts text to uppercase
VALUE Converts a text argument to a number
VAR.P Calculates variance based on the entire population
VAR.S Estimates variance based on a sample
VLOOKUP Looks for a matching value in a table or a range by row
WEEKDAY Converts a serial number to a day of the week
WEEKNUM Determines the week number of the year for a given date
WORKDAY Returns the date serial number which is a specified number of work days before or after an input date
YEAR Converts a serial number to a year
YEARFRAC Extracts the number of years (including fractional part) between two dates

For more information on how to create formulas and what their syntax is, see the list of Excel functions.

## Array Formulas

Similar to Excel, the Kendo UI Spreadsheet widget supports array formulas, which return a matrix of values.

### Differences from Excel

In Excel, to get all the values from an array formula, you pre-select the target range, press `F2` to type the formula, and press `Ctrl`+`Shift`+`Enter` to save it. Excel then locks the cells that contain the result, so that they cannot be edited, deleted, or merged.

In the Spreadsheet, if a formula returns an array, the values are distributed across adjacent cells. For example, if you type in `A1`: `=2*D1:E2`, then the `A1`, `B1`, `A2`, and `B2` cells receive the doubles of the values from `D1`, `E1`, `D2`, and `E2` respectively. In Excel, such a formula places the double of `D1` in `A1`—it would only act as an array formula if the steps outlined above are taken when the formula is created.

The Spreadsheet does not lock the result cells neither, which might lead to surprising behavior. You are able to type into the result cells, but the values do not appear to have been saved because the formula distributes the values to them again. The result cells are also editable and can be merged or deleted—an array formula will just attempt to fill whatever cell possible.