New to Kendo UI for Angular? Start a free 30-day trial

# 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. |

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 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`

.

## Array Formulas

Similar to Excel, the 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.