# Angular Spreadsheet Built-in Formulas and Functions The following table provides a list of the formulas and functions the Spreadsheet supports. | Function | Description | | ---------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | ABS | Returns the absolute (nonnegative) value of a number. | | ACOS | Returns the principal value of the arccosine of a number 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 in radians. | | ACOTH | Returns the hyperbolic arccotangent of a number. | | ADDRESS | Returns a cell address (reference) as 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 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 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 to 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 product 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 year, month, and day values. | | DATEVALUE | Returns the date converting it into 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 the 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 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 meets a given criterion. | | SUMIFS | Sums the values of cells in a range that meets 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](https://unicode.org/standard/standard.html). | | UNICODE | Returns the [Unicode](https://unicode.org/standard/standard.html) 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 the fractional part) between two dates. | For more information on how to create formulas and what their syntax is, see the list of [`Excel functions`](https://support.microsoft.com/en-us/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb?correlationid=343eb30b-8175-4479-be19-1310dd12334f\&ui=en-us\&rs=en-us\&ad=us). ## Array Formulas Similar to Excel, the Spreadsheet supports [`array formulas`](https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7?ui=en-us\&rs=en-us\&ad=us) 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. ## Suggested Links * [API Reference of the Spreadsheet](https://www.telerik.com/kendo-angular-ui/components/spreadsheet/api) * [API Reference of the `SpreadsheetComponent`](https://www.telerik.com/kendo-angular-ui/components/spreadsheet/api/spreadsheetcomponent) * [Spreadsheet Action Bar](https://www.telerik.com/kendo-angular-ui/components/spreadsheet/action-bar) * [Prevent Formula Input](https://www.telerik.com/kendo-angular-ui/components/knowledge-base/spreadsheet-prevent-formula-input)