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 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 |
| 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 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 |
| 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 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 |
| 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 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 supports array formulas which return a matrix of values. You have to enter them in the same way as in Excel—by pre-selecting the target range and pressing Ctrl+Shift+Enter to save the formula.