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 
ADDRESS  Returns a cell address (reference) as a text 
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 onetailed probability of the chisquared distribution 
CHISQ.INV  Returns the cumulative beta probability density function 
CHISQ.INV.RT  Returns the inverse of the onetailed probability of the chisquared 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 360day year 
DECIMAL  Converts a text representation of a number in a given base into a decimal number 
DEGREES  Converts radians to degrees 
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 casesensitive 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 
HYPERLINK  Creates a hyperlink involving an evaluated expression 
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 base10 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 xth sample percentile of values in a range 
PERCENTILE.EXC  Returns the kth percentile of values in a range, where k is in the range 0..1, exclusive 
PERCENTILE.INC  Returns the kth 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 
RADIANS  Converts degrees to radians 
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 casesensitive) 
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 nth 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 yvalue 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 0length text value 
T.DIST  Returns the Percentage Points (probability) for the Student tdistribution 
T.DIST.2T  Returns the Percentage Points (probability) for the Student tdistribution 
T.DIST.RT  Returns the Student's tdistribution 
T.INV  Returns the tvalue of the Student's tdistribution as a function of the probability and the degrees of freedom 
T.INV.2T  Returns the inverse of the Student's tdistribution 
T.TEST  Returns the probability associated with a Student's ttest 
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 preselect 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.
See Also
Other articles on Kendo UI Spreadsheet: