Compatibility functions have been replaced with new functions that may
provide improved accuracy and whose names better reflect their usage.
These functions are still available for compatibility reasons. However,
if backward compatibility is not mandatory, you should consider using
the new functions from now on, because they offer more accuracy and
better functionality.
| Mnemonics | Accepted Parameters (Operand Types) | Example | Explanation |
| BETADIST | Accepts three obligatory parameters: The value at which the BETADIST function performs evaluation (X) and two parameters of the calculated distribution (Alpha and Beta).Optionally, you can pass the lower and upper bounds of the interval as the A and B parameters, respectively. | =BETADIST(X, Alpha, Beta, [A], [B]) | Returns the beta cumulative distribution function.If any specified parameter is not numeric, the BETADIST function returns the #VALUE! error value. If either the Alpha or Beta parameter is not positive, BETADIST returns the #NUM! error value. If X < A, X > B, or A = B, BETADIST returns the #NUM! error value. If you omit the A and B parameter values, BETADIST uses the standard cumulative beta distribution, so that A = 0 and B = 1.BETADIST is a deprecated function. For information about its new version, refer to the BETA.DIST function description. |
| BETAINV | Accepts three obligatory parameters:The value at which the BETAINV function performs evaluation; and two parameters of the calculated distribution (Alpha and Beta).As with the BETADIST function, you can also pass the lower and upper bounds of the interval as the A and B parameters, respectively. | =BETAINV(Probability, Alpha, Beta, [A],[B]) | Returns the inverse of the cumulative distribution function for a specified beta distribution.If any parameter is nonnumeric, the BETAINV function returns the #VALUE! error code. If alpha or beta are not positive, BETAINV returns the #NUM! error code. If Probability < 0 or Probability > 1, BETAINV returns the #NUM! error code. If you omit the A and B parameter values, BETAINV uses the standard cumulative beta distribution, so that A = 0 and B = 1.BETAINV is a deprecated function. For information about its new version, refer to the BETA.INV function description. |
| BINOMDIST | Accepts four obligatory parameters: The number of successes in trials (NumberS), the number of independent trials (Trials), the probability of success on each trial (ProbabilityS), and a logical value that determines the form of the function (Cumulative). | =BINOMDIST(NumberS, Trials, ProbabilityS, Cumulative) | Returns the individual term binominal distribution probability.The NumberS and Trials parameter values are truncated to integers. If the NumberS, Trials, or ProbabilityS parameter value is not numeric, the BINOMDIST function returns the #VALUE! error code. If NumberS < 0 or NumberS > trials, BINOMDIST returns the #NUM! error code. If ProbabilityS < 0 or ProbabilityS > 1, BINOMDIST returns the #NUM! error code.BINOMDIST is a deprecated function. For information about its new version, refer to the BINOM.DIST function description. |
| CHIDIST | Accepts two obligatory parameters: the value at which you want to evaluate the distribution (X) and the number of degrees of freedom (DegFreedom). | =CHIDIST(X, DegFreedom) | Returns the one-tailed probability of the chi-squared distribution.If either parameter is not numeric, the CHIDIST function returns the #VALUE! error code. If X is negative, CHIDIST returns the #NUM! error code. If the DegFreedom parameter is not an integer, it is truncated.CHIDIST is a deprecated function. For information about its new versions, refer to the CHISQ.DIST and CHISQ.DIST.RT function descriptions. |
| CHIINV | Accepts two obligatory parameters: a probability associated with the chi-squared distribution (Probability) and the number of degrees of freedom (DegFreedom). | =CHIINV(Probability, DegFreedom) | Returns the inverse of the one-tailed probability of the chi-squared distribution.If either parameter is not numeric, the CHIINV function returns the #VALUE! error code. If Probability < 0 or Probability > 1, CHIINV returns the #NUM! error code. If the DegFreedom is not an integer, it is truncated. If DegFreedom < 1, CHIINV returns the #NUM! error value.CHIINV is a deprecated function. For information about its new versions, refer to the CHISQ.INV and CHISQ.INV.RT function descriptions. |
| COVAR | Accepts two obligatory parameters: the first and second cell ranges of integers (Array1 and Array2). | =COVAR(Array1,Array2) | Returns covariance, the average of the products of deviations for each data point pair in two data sets.The parameters must either be numbers or be names, arrays, or references that contain numbers. If the array or reference parameter contains text, logical values, or empty cells, those values are ignored; however, cells with zero values are taken into account.If Array1 and Array2 have different numbers of data points, COVAR returns the #N/A error value.If either Array1 or Array2 is empty, the COVAR function returns the #DIV/0! error code.COVAR is a deprecated function. For information about its new versions, refer to the COVARIANCE.P and COVARIANCE.S function descriptions. |
| EXPONDIST | Accepts three obligatory parameters: the value of the function (X), the Lambda parameter, and the logical value that specifies which form of the exponential function to provide (Cumulative). | =EXPONDIST(X, Lambda, Cumulative) | Returns the exponential distribution.If cumulative is TRUE, EXPONDIST returns the cumulative distribution function; otherwise – returns the probability density function. If the X or Lambda parameter is not numeric, the EXPONDIST function returns the #VALUE! error code.If the X parameter is negative, EXPONDIST returns the #NUM! error code.If the Lambda parameter is not positive, EXPONDIST also returns the #NUM! error value.EXPONDIST is a deprecated function. For information about its new version, refer to the EXPON.DIST function description. |
| GAMMADIST | Accepts four obligatory parameters: the value at which you want to evaluate the distribution (X), two parameters of the distribution (Alpha and Beta), and the cumulative logical value (Cumulative). | =GAMMADIST(X, Alpha, Beta, Cumulative) | Returns the gamma distribution. You can use this function to study variables that may have a skewed distribution. The gamma distribution is commonly used in queuing analysis.If the Beta parameter is 1, the GAMMADIST function returns the standard gamma distribution.If the X, Alpha, or Beta parameter is not numeric, GAMMADIST returns the #VALUE! error code.If the X parameter is negative, GAMMADIST returns the #NUM! error code.If either the Alpha or Beta parameter is not positive, the GAMMADIST function returns the #NUM! error code.GAMMADIST is a deprecated function. For information about its new version, refer to the GAMMA.DIST function description. |
| GAMMAINV | Accepts three obligatory parameters: the probability associated with the gamma distribution (Probability), and two parameters of the distribution (Alpha and Beta). | =GAMMAINV(Probability, Alpha, Beta) | Returns the inverse of the gamma cumulative distribution. You can use this function to study a variable whose distribution may be skewed.If the Beta parameter is 1, the GAMMAINV function returns the standard gamma distribution.If text is passed as any of the parameters, GAMMAINV returns the #VALUE! error code.If Probability < 0 or Probability > 1, the GAMMAINV function returns the #NUM! error code.If either the Alpha or Beta parameter is not positive, the GAMMAINV function returns the #NUM! error code.GAMMAINV is a deprecated function. For information about its new version, refer to the GAMMA.INV function description. |
| HYPGEOMDIST | Accepts four obligatory parameters: the number of successes in the sample (SampleS), the size of the sample (SampleSize), the number of successes in the population (PopulationS), and the population size (PopulationSize). | =HYPGEOMDIST(SampleS, SampleSize, PopulationS, PopulationSize) | Returns hypergeometric distribution. The HYPGEOMDIST function returns the probability of a given number of sample successes, given the sample size, population successes, and population size.All parameters are truncated to integers.If any parameter is non-numeric, the HYPGEOMDIST function returns the #VALUE! error value.If the SampleS parameter is negative or SampleS is greater than the lesser of SampleSize or PopulationSize, the HYPGEOMDIST function returns the #NUM! error value.If SampleS is less than the larger of 0 or (SampleSize – PopulationSize + PopulationS), the HYPGEOMDIST function returns the #NUM! error value.If the SampleSize parameter is not positive or SampleSize > PopulationSize, the HYPGEOMDIST function returns the #NUM! error value.If PopulationS is not positive or PopulationS > PopulationSize, HYPGEOMDIST returns the #NUM! error value.If the PopulationSize parameter is not positive, HYPGEOMDIST returns the #NUM! error value. |
| NORMDIST | Four obligatory parameters: the value for which you want the distribution, the arithmetic mean of the distribution, the standard deviation of the distribution, and the logical value that specifies the form of the function.If cumulative = True, NORMDIST returns the cumulative distribution function. Otherwise, NORMDIST returns the standard normal distribution, NORMSDIST. | =NORMDIST(X, Mean, StandardDev, Cumulative) | Returns the normal cumulative distribution. This function has a very wide range of applications in statistics, including hypothesis testing.If mean or standard_dev is non-numeric, NORMDIST returns the #VALUE! error value.If standard_dev is not positive, NORMDIST returns the #NUM! error value.If mean = 0, standard_dev = 1, and cumulative = True, NORMDIST returns the standard normal distribution, NORMSDIST. |
| NORMSDIST | The value for which you want the distribution is passed as the z parameter. | =NORMSDIST(z) | Returns the inverse of the normal cumulative distribution. The distribution has a mean of 0 (zero) and a standard deviation of one. Use this function in place of standard normal curve areas.If z is non-numeric, NORMSDIST returns the #VALUE! error value. |
| NORMSINV | The probability value corresponding to the normal distribution is passed as the probability parameter. | =NORMSINV(probability) | Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one.Given a value for probability, NORMSINV seeks that value z that NORMSDIST(z) = probability. Thus, precision of NORMSINV depends on precision of NORMSDIST. NORMSINV uses an iterative search algorithm. If the search is not converged after 100 iterations, the function returns the #N/A error value.If probability is non-numeric, NORMSINV returns the #VALUE! error value.If probability is not positive or exceeds 1, NORMSINV returns the #NUM! error value. |
| PERCENTILE | Two obligatory parameters: the array or range of data that defines relative standing and the percentile value in the range 0..1, inclusive. | =PERCENTILE(array, k) | Returns the k-th percentile of values in a range. You can use this function to establish a threshold of acceptance. For instance, you can decide to examine candidates who score above the 90th percentile.If k is non-numeric, PERCENTILE returns the #VALUE! error value.If k < 0 or k > 1, PERCENTILE returns the #NUM! error value.If k is not a multiple of 1/(n – 1), PERCENTILE interpolates to determine the value at the k-th percentile. |
| POISSON | Three obligatory parameters: the number of events, the expected numeric value, and the logical value that specifies the form of the probability distribution returned.If cumulative is True, POISSON returns the cumulative Poisson probability that the number of random events occurring will be between zero and x inclusive. Otherwise, the POISSON function returns Poisson mass function that the number of events occurring will be exactly x. | =POISSON(x, mean, cumulative) | Returns the Poisson distribution. A common application of the Poisson distribution is predicting the number of events over a specific time, such as the number of cars arriving at a toll plaza in 1 minute.If x is not an integer, it is truncated.If x or mean is non-numeric, POISSON returns the #VALUE! error value.If x is negative, POISSON returns the #NUM! error value.If mean is negative, POISSON returns the #NUM! error value. |
| QUARTILE | Two obligatory parameters: the array of cell range of numeric values for which you want the quartile value, and the quart value that indicates which value to return.quart = 0 corresponds to the minimum value;quart = 1 corresponds to the first quartile (25th percentile);quart = 2 corresponds to the median value (50th percentile);quart = 3 corresponds to the third quartile (75th percentile);quart = 4 corresponds to the maximum value. | =QUARTILE(array, quart) | Returns the quartile of a data set. Quartiles are often used in sales and survey data to divide populations into groups. For instance, you can use QUARTILE to find the top 25 percent of incomes in a population.If array is empty, QUARTILE returns the #NUM! error value.If quart is not an integer, it is truncated.If quart is negative or if quart > 4, QUARTILE returns the #NUM! error value.`MIN, MEDIAN, and MAX return the same value as QUARTILE when quart is equal to 0 (zero), 2, and 4, respectively. |
| RANK | Three parameters: the number whose rank you want to find, an array of, or a reference to, a list of numbers. Non-numeric values in ref are ignored. As an option, you can specify the sorting order as the order parameter. | =RANK(number, ref, [order]) | Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list (i.e. if you were to sort the list, the rank of the number would be its position).If order is 0 (zero) or omitted, the ExpressSpreadSheet ranks number as if ref were a list sorted in descending order.If order is any nonzero value, the ExpressSpreadSheet ranks number as if ref were a list sorted in ascending order. |
| STDEV | The one obligatory parameter: the first number corresponding to a sample of a population. As an option, you can specify from 2 to 255 parameters corresponding to a sample of a population. It is also possible to use a single array or a reference to an array instead of parameters separated by commas. | =STDEV(number1, [number2], …) | Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).STDEV assumes that its parameters are a sample of the population. If your data represents the entire population, then compute the standard deviation using STDEVP.STDEV calculates the standard deviation by using the “n – 1” method.Parameters can either be numbers or names, arrays, or references that contain numbers. |
| STDEVP | The one obligatory parameter: the first number corresponding to a sample of a population. As an option, you can specify from 2 to 255 parameters corresponding to a sample of a population. It is also possible to use a single array or a reference to an array instead of parameters separated by commas. | =STDEVP(number1, [number2], …) | Calculates standard deviation based on the entire population. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).STDEVP assumes that its parameters are the entire population. If your data represents a sample of the population, then compute the standard deviation using STDEV.STDEVP calculates the standard deviation by using the “n” method.Parameters can either be numbers or names, arrays, or references that contain numbers |
| TDIST | Three obligatory parameters: the numeric value at which to evaluate the distribution, the number of degrees of freedom, and the number of distribution tails to return. If Tails = 1, TDIST returns the one-tailed distribution. If Tails = 2, TDIST returns two-tailed distribution. | =TDIST(x, deg_freedom, tails) | Returns the Percentage Points (probability) for the Student t-distribution where a numeric value (x) is calculated value of t for which the Percentage Points are to be calculated. The t-distribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the t-distribution.If any parameter is non-numeric, TDIST returns the #VALUE! error value.If Deg_freedom < 1, TDIST returns the #NUM! error value.The Deg_freedom and Tails parameters are truncated to integers.If Tails is any value other than 1 or 2, TDIST returns the #NUM! error value.If x < 0, then TDIST returns the #NUM! error value.If Tails = 1, TDIST is calculated as TDIST = P(X > x), where X is a random variable that follows the t-distribution. If Tails = 2, TDIST is calculated as TDIST = P(|X| > x) = P(X > x or X < -x).Since the negative x values are not allowed, to use the TDIST function with x < 0, note that TDIST(-x,df,1) = 1 – TDIST(x,df,1) = P(X > -x) and TDIST(-x,df,2) = TDIST(x,df,2)=P(|X| > x). |
| TINV | Two obligatory parameters: the probability associated with the two-tailed Student's t-distribution and the number of degrees of freedom with which to characterize the distribution. | =TINV(probability, deg_freedom) | Returns the inverse of the Student's t-distribution.If either parameter is non-numeric, TINV returns the #VALUE! error value.If probability <= 0 or if probability> 1, TINV returns the #NUM! error value.If deg_freedom < 1, TINV returns the #NUM! error value.TINV returns that value t, such that P(|X| > 1) = probability where X is a random variable that follows the t-distribution and P(|X| > t) = P(X < -t or X > t). |
| VAR | The first number parameter corresponding to a sample of a population is obligatory. Optionally, you can specify from to 2 to 255 number parameters corresponding to a sample of a population. | =VAR(number1, [number2],…) | Estimates variance based on a sample.VAR assumes that its parameters are a sample of the population. If your data represents the entire population, then calculate the variance by using VARP instead.Parameters can either be numbers or names, arrays, or references that contain numbers.Logical values, and text representations of numbers that you type directly into the list of parameters are counted.If an parameter is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.Parameters that are error values or text cannot be translated into numbers cause errors. |
| VARP | The first number parameter corresponding to a sample of a population is obligatory. Optionally, you can specify from to 2 to 255 number parameters corresponding to a population. | =VARP(number1, [number2],…) | Calculates variance based on the entire population.VARP assumes that its parameters are the entire population. If your data represents a sample of the population, then compute the variance by using VAR instead.Parameters can either be numbers or names, arrays, or references that contain numbers.Logical values, and text representations of numbers that you type directly into the list of parameters are counted.If an parameter is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.Parameters that are error values or text that cannot be translated into numbers cause errors. |
| WEIBULL | Four obligatory parameters: the value at which to evaluate the function, the alpha and beta parameters to the distribution, and the cumulative logical value that determines the form of the function. | =WEIBULL(x, alpha, beta, cumulative) | Returns the Weibull distribution. Use this distribution in reliability analysis, such as calculating a device's mean time to failure.If x, alpha, or beta is non-numeric, WEIBULL returns the #VALUE! error value.If x is negative, WEIBULL returns the #NUM! error value.If alpha or beta is not positive, WEIBULL returns the #NUM! error value. |
Arithmetic operators allow you to perform basic arithmetic operations
(i.e., addition, subtraction, multiplication, division, and exponential
functions).
| Mnemonics | Operand Type | Example | Explanation |
| + | Two parameters | = 5 + B1 | Adds the first parameter's value to the value of the second parameter. |
| - | Two parameters | = A5 - B1 | Subtracts the second parameter's value from the value of the first one. |
| * | Two parameters | = A2 * A3 | Multiplies the first parameter's value by the value of the second parameter. |
| / | Two parameters | = A1 / PI() | Divides the first parameter's value by the value of the second parameter. |
| ^ | Two parameters | = B1 ^ A2 | Raises the first parameter's value to the power determined by the second parameter's value. |
Logical operators perform comparison operations and return a logical
value (TRUE or FALSE) as a result.
| Mnemonics | Operand Type | Example | Explanation |
| = | Two parameters of logical types | = A1=A2 | The equality operation. Returns TRUE if the first parameter is equal to the second one. Otherwise, returns FALSE. |
| <> | Two parameters of logical types | = A1<>B2 | The inequality operation. Returns TRUE if values within the list are not equal. Otherwise, returns FALSE. |
| < | Two parameters of logical types | = A1 | The “Less than” operation. Returns TRUE if the first parameter is less than the second one. Otherwise, returns FALSE. |
| > | Two parameters of logical types | = A1>100 | The “Greater than” operation. Returns TRUE if the first parameter is greater than the second one. Otherwise, returns FALSE. |
| Two parameters of logical types | = A2 | The “Less than or equal to” operation. Returns TRUE if the first parameter is less than or equal to the second one. Otherwise, returns FALSE. | |
| >= | Two parameters of logical types | = A2>=1.57 | The “Greater than or equal to” operation. Returns TRUE if the first parameter is greater than or equal to the second one. Otherwise, returns FALSE. |
Logical functions include Boolean operators, conditional tests and
functions.
| Mnemonics | Operand Type | Example | Explanation |
| AND | A list of logical parameters | =AND(1 | Logical AND operation.Returns TRUE if all values within the list are TRUE; returns FALSE if one or more values within the list evaluates to FALSE.One common use for the AND function is to expand the usefulness of other functions that perform logical tests. For example, the IF function performs a logical test and then returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. |
| FALSE | No parameters required | =FALSE() | Returns the logical value FALSE.You can also type the word FALSE directly into a worksheet cell or as a part of a formula expression. |
| IF | Three parameters: the logical expression, the value to return if the expression succeeds, the value to return if the expression fails. | =IF(A10 | Returns the second parameter if the logical expression evaluates to TRUE and the third parameter otherwise. |
| IFERROR | Two parameters: the parameter that is checked for an error, and the value displayed in an error occurs. | =IFERROR(value, value_if_error) | Returns the value you specify if a formula evaluates to an error. Otherwise, returns the formula calculation result. The IFERROR function is particularly useful for spreadsheet error handling.The formula evaluates the following error types: #N/A, #VALUE, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!. |
| IFNA | Two parameters: the parameter that is checked for the #N/A error value, and the value returned if the error occurs. | =IFNA(value, value_if_na) | Returns the value you specify if the formula returns the #N/A error value. Otherwise, returns the result of the formula. |
| NOT | One parameter of logical type | =NOT(1>D4) | Logical NOT operation.Reverses the value of its parameter. |
| OR | A list of logical parameters | =OR(A1>=10, A1 | Logical OR operation.Returns TRUE if any parameter is TRUE; returns FALSE if all values within the list evaluate to FALSE. |
| TRUE | No parameters required. | =TRUE() | Returns the logical value TRUE. |
| XOR | The first parameter is mandatory, subsequent logical values are optional. If the specified range contains no logical values, the XOR function returns the #VALUE! error code. | =XOR(logical1, logical2, …) | Returns a logical exclusive OR of all specified parameters. The XOR function allows you to test from 1 to 254 conditions.The result of XOR is TRUE when the number of TRUE inputs is odd and FALSE when the number of TRUE inputs is even. |
A set of reference and lookup functions.
| Mnemonics | Operand Type | Example | Explanation |
| ADDRESS | Three parameters: the numeric value that specifies the row number to use in the cell reference, the numeric value that specifies the column number to use in the cell reference. Optionally, you can add a numeric value that specifies the type of reference to return. | =ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) | Returns a reference as text to a single cell in a worksheet.You can use the ADDRESS function to obtain the address of a cell in a worksheet, given specified row and column numbers. For instance, ADDRESS(2, 3) returns $C$2. As another example, ADDRESS(77, 300) returns $KN$77. You can use other functions, such as the ROW and COLUMN functions, to provide the row and column number parameters to the ADDRESS function. |
| AREAS | The reference parameter is mandatory. It is a reference to a cell or cell range and can refer to multiple areas. If you want to specify several references as a single parameter, include extra sets of parentheses so the ExpressSpreadSheet formula engine will not interpret the comma as a field separator. | =AREAS(reference) | Returns the number of areas in a reference. An area is a range of continuous cells or a single cell.For instance, the AREAS(B2:D4) expression returns 1. As another example, AREAS((B2:D4,E5,F6:I9)) returns 3. |
| CHOOSE | Two mandatory parameters. The index_num parameter specifies which value parameter is selected. The index_num value must be within the range of 1 to 254, or a formula expression or cell reference returning a value within the same range.The value1 parameter is also mandatory while subsequent parameters are optional. You can specify up to 254 value parameters from which the CHOOSE function selects a value or an action to perform based on the index_num parameter value. The actual parameters can be numbers, cell references, defined names, formulas, functions, or text. | =CHOOSE(index_num, value1, [value2], …) | Returns a value from the list of value parameters. You can use the CHOOSE function to select one of the specified values based on the index number.If index_num is 1, CHOOSE returns value1. If the index value is 2, the CHOOSE function returns value 2, and so on.If the index_num value is less than 1 or greater than the number of the last provided value, the CHOOSE function returns the #VALUE! error value.If the index_num parameter value is a fraction, it is truncated to the lowest integer before evaluation.The value parameters can be cell range references as well as single values. |
| COLUMN | The reference parameter is optional. If it is omitted or refers to a range of cells, and if the COLUMN function is entered as a horizontal array formula, the COLUMN function returns the column numbers of reference as a horizontal array. | =COLUMN([reference]) | Returns the column number of a reference. For instance, the formula =COLUMN(D10) returns 4 since column D is the fourth column.If the reference parameter is a range of cells, and if the COLUMN function is not entered as a horizontal array formula, the COLUMN function returns the number of the leftmost column.If the reference parameter is omitted, it is assumed to be the reference of the cell in which the COLUMN function appears.The reference parameter cannot refer to multiple areas. |
| COLUMNS | The array parameter is mandatory. It specifies an array or array formula, or a reference to a range of cells for which you want the number of columns. | =COLUMNS(array) | Returns the number of columns in a reference. |
| FORMULATEXT | The reference parameter is mandatory; it specifies a reference to a cell or range of cells. | =FORMULATEXT(reference) | Returns the formula at the given reference as text.The reference parameter can be to another worksheet or workbook.If the reference parameter is to another workbook that is not open, FORMULATEXT returns the #N/A error value.If the reference parameter is to an entire row or column, or to a range or defined name containing more than one cell, FORMULATEXT returns the value in the upper leftmost cell or the row, column, or range.In the following cases, FORMULATEXT returns the #N/A error value:The cell used as the reference parameter does not contain a formula;The formula in the cell is longer than 8192 characters;The formula cannot be displayed in the worksheet; for example, due to worksheet protection;An external workbook that contains the formula is not currently open.Invalid data types used as inputs will produce a #VALUE error value. |
| HLOOKUP | Three mandatory parameters: the value to be found in the first row of the table, the table of information in which data is looked up, and the row number in table_array which the matching value will be returned.You can optionally add a logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. | =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) | Looks in the top row of an array and returns the value of the initiated cell. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.If HLOOKUP can't find lookup_value, the range_lookup is TRUE, it uses the largest value that is less than lookup_value.If lookup_value is smaller than the smallest value in the first row of table_array, HLOOKUP returns the #N/A error value.If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character. |
| INDEX | Array form:Two mandatory parameters.The array parameter specifies a cell range or an array constant. If the specified array contains only one row or column, the corresponding row_num or column_num parameter is optional. If the array has more than one row and more than one column, and only the row_num or column_num parameter is provided, the INDEX function returns an array of the entire row or column in array.The mandatory row_num parameter specifies the row in the array from which the INDEX function should return a value. If the row_num parameter is omitted, the column_num parameter is required instead.The optional column_num parameter specifies the column in the array from which the INDEX function should return a value. If the column_num parameter is omitted, the row_num parameter is required instead. Reference form:Two mandatory parameters.The reference parameter specifies a reference to one or more cell ranges. If you are entering a nonadjacent cell range for the reference, enclose the reference in parentheses. If each area in the reference parameter contains only one row or column, the row_num or column_num parameter becomes optional, respectively.The mandatory row_num parameter specifies the number of the row in the reference from which the INDEX function returns a reference.The optional column_num parameter specifies the number of the column in the reference from which the INDEX function returns a reference.The optional area_num parameter specifies a range in the reference from which the INDEX function returns the intersection of row_num and column_num. The first selected area is numbered 1, the second is 2, and so on. If the area_num parameter is omitted, the INDEX function uses 1 as the area value. | Array form:=INDEX(array, row_num, [column_num]) Reference form:=INDEX(reference, row_num, [column_num], [area_num]) | Returns a value or the reference to a value from within a table or cell range. There are two forms of the INDEX function: the array form and the reference form. Array form:Returns the value of an element in a table or an array, selected by the row and column number indexes. Use the array from if the first parameter of the INDEX function is an array constant.If both the row_num and column_num parameters are used, the INDEX function returns the value in the cell at the intersection of row_num and column_num.If you set row_num or column_num to 0 (zero), the INDEX function returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula in a horizontal range of cells for a row, and in a vertical range of cells for a column. In order to enter the INDEX function as an array formula, press the Ctrl+Shift+Enter key combination.The row_num and column_num parameter values must point to a cell within the array. Otherwise, the INDEX function returns the #REF! error value. Reference form:Returns the reference of the cell at the intersection of a particular row and column. If the reference consists of nonadjacent cell ranges, you can pick a particular range. After the reference and area_num values have been used to select a particular range, the row_num and column_num values are used to select a particular cell. The row_num value set to 1 corresponds to the first row in the range, the column_num value set to 1 corresponds to the first column, and so on. The reference returned by the INDEX function is the intersection of the row and column whose numbers are specified as the row_num and column_num parameters.If you set the row_num or column_num parameter to 0 (zero), the INDEX function returns the reference for the entire column or row, respectively.The row_num, column_num, and area_num parameter values must point to a cell within the reference. Otherwise, the INDEX function returns the #REF! error value. If the row_num and column_num parameters are omitted, the INDEX function returns the area in the reference specified by the area_num parameter.The result of the INDEX function is a reference and can be used as a reference in other formula expressions. |
| INDIRECT | One mandatory parameter: the reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string.If the a1 parameter is set to TRUE or omitted, the ref_text value is interpreted as an A1-style reference.If the a1 parameter is set to FALSE, the ref_text value is interpreted as an R1C1-style reference. | =INDIRECT(ref_text, a1) | Returns the reference specified by a text string. References are immediately evaluated to display their contents.Use the INDIRECT function when you need to change the reference to a cell within a formula without changing the formula itself.If the ref_text parameter contains no valid cell reference, the INDIRECT function returns the #REF! error value. |
| LOOKUP | Vector form:Two obligatory parameters: the value that LOOKUP searches for in the first vector, and the range that contains only one row or one column.Additionally, you can specify a range that contains only one row or column (result_vector).Array form:Two obligatory parameters: the value that LOOKUP searches for in the array and the range of cells that contains text, numbers, or logical values that you want to compare with lookup_value. | Vector form:=LOOKUP(lookup_value, lookup_vector, [result_vector]) Array form:=LOOKUP(lookup_value, array) | Looks up values in a vector or array. The LOOKUP function returns a value either form a one-row or one-column range or form an array. The LOOKUP function has two syntax forms: the vector form and the array form.Vector form:A vector is a range of only one row or one column. LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range. Use the LOOKUP function when you want to specify the range that contains the values that you want to match.If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns the #N/A error value.Array form:The array form of LOOKUP looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array. Use this form of LOOKUP when the values that you want to match are in the first row or column of the array. Use the other form of LOOKUP when you want to specify the location of the column or row.In general, it is best to use the HLOOKUP or VLOOKUP function instead of the array form of LOOKUP. This form of LOOKUP is provided for compatibility with other spreadsheet programs. |
| MATCH | Two obligatory parameters: the value that you want to match in lookup_array, and the range of cells being searched.Additionally, you can specify the match_type which can be the number –1, 0, or 1. The match_type specifies how the ExpressSpreadSheet matches lookup_value with values in lookup_array. The default value for this parameter is 1.If match_type is 1 or omitted, MATCH finds the largest value that is less than or equal to lookup_value.If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value.If match_type is –1, MATCH finds the smallest value that is greater than or equal to lookup_value. | =MATCH(lookup_value, lookup_array, [match_type]) | Looks up values in a reference or array. The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range.Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.MATCH returns the position of the matched value within lookup_array, not the value itself. For instance, MATCH(“b”,{“a”,”b”,”c”},0) returns 2 which is the relative position of “b” within the array {“a”,”b”,”c”}.MATCH does not distinguish between uppercase and lowercase letters when matching text values.If MATCH is unsuccessful in finding a match, it returns the #N/A error value.If match_type is 0, and lookup_value is a text string, you can use the wildcard characters – the question mark (?) and asterisk (*) – in the lookup_value parameter. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character. |
| OFFSET | Accepts three obligatory parameters: the reference from which you want to base the offset, the number of rows, up or down that you want the upper-left cell to refer to, and the number of columns to the left or right that you want the upper-left cell of the result to refer to.Additionally, you can specify the height and width of the returned reference. Both the height and width must be positive. | =OFFSET(reference, rows, cols, [height], [width]) | Returns a reference to a range that is the specified number of rows and columns from a cell or cell range. The returned reference can be a single cell or cell range. You can specify the number of returned rows and/or columns.If the offset reference is outside the worksheet's boundaries, the OFFSET function returns the #REF! error value.If the height or width parameter is omitted, the returned cell range has the same height or width as the specified reference.The OFFSET function does not actually move any cell or change the selection while returning a reference. You can use OFFSET with any function expecting a reference parameter. For example, the formula expression SUM(OFFSET(C2,1,2,3,1)) calculates the total value of a range that is three rows in height and one column width that is 1 row below and 2 columns to the right of the C2 cell. |
| ROW | The reference parameter is optional.If reference is omitted, it is assumed to be the reference of the cell in which the ROW function appears.If reference is a range of cells, and if ROW is entered as a vertical array, ROW returns the row numbers of reference as a vertical array.Reference cannot refer to multiple areas. | =ROW([reference]) | Returns the row number of a reference. |
| ROWS | The array parameter is mandatory. An array, an array formula, or a reference to a range of cells for which you want the number of rows. | =ROWS(array) | Returns the number of rows in a reference or array. |
| TRANSPOSE | One obligatory parameter: an array or range of cells within a worksheet that you want to transpose. The transposition of an array is created by using the first column of the new array, the second row of the array as the second column of the new array, and so on. | =TRANSPOSE(array) | Returns a vertical range of cells as a horizontal range, and vice versa. You must enter the TRANSPOSE function as an array formula in a range that has the same number of rows and columns, respectively, as the source range has columns and rows. Use TRANSPOSE to swap the vertical and horizontal orientation of an array or range within a worksheet. |
| VLOOKUP | Three obligatory parameters: the value to search in the first column of the table or range, the range of cells that contains the data, and the column number in the table_array parameter from which the matching value must be returned.Additionally, you can specify the range_lookup that switches the VLOOKUP finding between an exact match and an approximate match. | =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) | Looks in the first column of an array and moves across the row to return the value of a cell. You can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range. The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in columns to the left of the data that you want to find.When searching number or date values, ensure that the data in the first column of table_array is not stored as text values. In this case, VLOOKUP might return an incorrect or unexpected value.If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters – the question mark (?) and asterisk (*) – in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character. |
A set of mathematical and trigonometric functions, including basic
arithmetic, conditional sums and products, and trigonometric ratios.
Mathematicians may also find the Statistical
Functions
useful.
| Mnemonics | Operand Type | Example | Explanation |
| ABS | One parameter of double type. | =ABS(B6) | Returns the absolute value. |
| ACOS | One parameter of double type. | =ACOS(B16) | Returns the arccosine. |
| ACOSH | One parameter of double type. | =ACOSH(A1) | Returns the inverse hyperbolic cosine. |
| ACOT | One parameter of double type. | =ACOT(A1) | Returns the principal value of the arccotangent, or inverse cotangent, of a number. The returned angle is given in radians in the range 0 (zero) to pi. |
| ACOTH | One parameter of double type. The absolute value of the parameter must be greater than 1. | =ACOTH(A1) | Returns the inverse hyperbolic cotangent of a number. If the absolute value of the parameter is less than 1, the ACOT function returns the #VALUE! error value. |
| ASIN | One parameter of double type. | =ASIN(B6) | Returns the arcsine. |
| ASINH | One parameter of double type. | =ASINH(C2) | Returns the inverse hyperbolic sine of the parameter. |
| ATAN | One parameter of double type. | =ATAN(C5) | Returns the arctangent. |
| ATAN2 | Two parameters of double type. | =ATAN2(A1, A2) | Returns the arctangent using x- and y- coordinates. |
| ATANH | One parameter of double type. | =ATANH(D4) | Returns the inverse hyperbolic tangent. |
| BASE | Two parameters of integer type: the first is greater than 0 and less than 2^53; the second (radix) must be greater than or equal to 2 and less than or equal to 36. Third parameter is optional, which must be greater or equal to 0. | =BASE(A1, Radix[Min_length]) | Converts a number into a text representation with the given radix (base).If A1, Radix, or Min_length are outside the minimum or maximum constraints, BASE returns the #NUM! error value.If A1 is a non-numeric value, BASE returns the #VALUE! error value.Any non-integer number used as an parameter is truncated to an integer.If the Min_length parameter is included, leading zeros are added to the result if the result would otherwise be shorter than the minimum length specified. For example, BASE(16,2) returns 10000 but BASE(16,2,8) returns 00010000. |
| CEILING | Two parameter of double type | =CEILING(2.5, 1) | Rounds the first parameter up to the nearest multiple based on the significance specified by the second parameter. |
| CEILING.MATH | Three parameters. The first parameter must be less than 9.99E+307 and greater than –2.229E-308; the second parameter is the multiple to which the number is rounded; the optional Mode parameter. | =CEILING.MATH(A1, [significance], [mode]) | Rounds a number up to the nearest integer or to the nearest multitude of significance.By default, significance is +1 for positive numbers and –1 for negative numbers.By default, both positive and negative numbers with decimal portions are rounded up to the nearest integer. If you specify the Significance and Mode parameters, you can change the direction of rounding for negative numbers. For example, rounding –6.3 to a significance of 1 with a mode of 1 rounds away from 0, to –7. There are many combinations of Significance and Mode values that affect rounding of negative numbers in different ways.The Mode parameter does not affect positive numbers. |
| CEILING.PRECISE | Two parameters. The first parameter is a number of double type. The Significance parameter is optional. | =CEILING.PRECISE(A1, [significance]) | Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up. However, if the number or the significance is zero, CEILING.PRECISE returns zero. If the Significance parameter is omitted, its default value is 1. |
| CONCATENATE | At least one text item is mandatory; you can add more text items, up to a maximum of 255 items. Commas must separate the specified items. | =CONCATENATE(text1, [text2], …) | Joins several text items into a single text item. The CONCATENATE function joins up to 255 text strings into one text string. The joined items can be text, numbers, cell references, or a combination of those items.Note that you can also use the ampersand (&) calculation operator instead of the CONCATENATE function to join text items. For instance, =A1 & B1 returns the same value as =CONCATENATE(A1, B1). |
| COS | One parameter of double type | =COS(A1) | Returns the cosine. |
| COSH | One parameter of double type | =COSH(D7) | Returns the hyperbolic cosine. |
| COMBIN | Two parameters of integer type. Non-integer numeric parameters are truncated to integers. | =COMBIN(number, number_chosen) | Returns the number of combinations for a given number of items. Use COMBIN to determine the total possible number of groups for a given number of items.If either parameter is non-numeric, COMBIN returns the #VALUE! error value.If either parameter is negative, COMBIN returns the #NUM! error value. |
| COMBINA | Two parameters of integer type. Non-integer numeric parameters are truncated to integers. | =COMBINA(number, number_chosen) | Returns the number of combinations (with repetitions) for a given number of items.If the value of either parameter is outside of its constraints, COMBINA returns the #NUM! error.If either parameter is a non-numeric value, COMBINA returns the #VALUE! error value. |
| COT | One parameter of double type, which should be more than 0 and less than 2^27. | =COT(number) | Returns the cotangent of an angle specified in radians.If the specified parameter is outside its constraints, the COT function returns the #NUM! error value.If the specified parameter is not a numeric value, the COT function returns the #VALUE! error value.The COT(0) expression returns the #DIV/0! error value. |
| COTH | One parameter of double type, which should be less than 2^27. | =COTH(number) | Returns the hyperbolic cotangent of a hyperbolic angle.If the specified parameter is outside its constraints, the COTH function returns the #NUM! error value.If the specified parameter is a non-numeric value, the COTH function returns the #VALUE! error value. |
| CSC | One parameter of double type, which should be less than 2^27. | =CSC(number) | Returns the cosecant of an angle specified in radians.If the specified parameter is outside its constraints, the CSC function returns the #NUM! If the specified parameter is a non-numeric value, the CSC function returns the #VALUE! error value.CSC(n) equals 1/SIN(n). |
| CSCH | One parameter of double type, which should be less than 2^27. | =CSCH(number) | Returns the hyperbolic cosecant of an angle specified in radians.If the specified parameter is outside its constraints, the CSCH function returns the #NUM! error value.If the specified parameter is a non-numeric value, the CSCH function returns the #VALUE! error value. |
| DECIMAL | One parameter is a text string that must be less or equal to 255 characters, other parameter is an integer that must be greater or equal to 2 and less than or equal to 36. | =DECIMAL(text, radix) | Converts a text representation of a number in a given base into a decimal number.A radix greater than 10 uses the numeric values 0 – 9 and the letters A-Z as needed. For example, base 16 (hexadecimal) uses 0 – 9 and A – F, and base 36 uses 0 – 9 and A – Z.If either parameter is outside its constraints, DECIMAL may return the #NUM! or #VALUE! error value. |
| DEGREES | One parameter of double type that specifies angle in radians | =DEGREES(PI()/2) | Converts radians to degrees. |
| EVEN | One parameter of double type | =EVEN(-1) | Rounds the parameter up to the nearest even integer. |
| EXP | One parameter of double type | =EXP(C1) | Returns the exponent value of the parameter. |
| FACT | One non-negative parameter. If a number is not an integer, it is truncated. | =FACT(2) | Returns the factorial of the parameter. |
| FACTDOUBLE | One non-negative parameter. If a number is not an integer, it is truncated. | =FACTDOUBLE(number) | Returns the double factorial of a number.If the number is non-numeric, the FACTDOUBLE function returns the #VALUE! error value.If the number is negative, the FACTDOUBLE function returns the #NUM! error value. |
| FLOOR | Two parameters of double type | =FLOOR(-2.5, -2) | Rounds the first parameter down, towards zero, to the nearest multiple of the significance specified by the second parameter. |
| FLOOR.MATH | Three parameters: first is the number to be rounded down, second is the multiple to which you want to round, and third is the direction (either towards or away from 0) to round negative numbers. | =FLOOR.MATH(number, significance, mode) | By default, both positive and negative numbers are rounded down to the nearest integer. By using 0 or a negative number as the Mode parameter, you can change the direction of the rounding for negative numbers.The Significance parameter rounds the number down to the nearest integer that is a multiple of the significance specified. The exception is where the number to be rounded is an integer. For example, for a Significance of 3, the number is rounded down to the next integer that is a multiple of 3. If Number is divided by a Significance of 2 or greater results in a remainder, the result is rounded. |
| FLOOR.PRECISE | Two parameters. First is the number to be rounded down, second is the multiple to which the number is to be rounded. The Significance parameter is optional. | =FLOOR.PRECISE(number, [significance]) | Returns a number that is rounded down to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded down. However, if the number of the significance is zero, zero is returned.If the significance is omitted, its default value is 1.The absolute value of the multiple is used, so that the FLOOR.PRECISE function returns the mathematical floor irrespective of the signs of the number and significance. |
| INT | Parameter of type double | =INT(E4) | Rounds the parameter down to the nearest integer. |
| ISO.CEILING | Two parameters. First is the value to be rounded; second is the optional multiple to which the number is to be rounded. | =ISO.CEILING(number, [significance]) | Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up. However, if the number or the significance is zero, zero is returned.If the significance is omitted, its default value is 1. |
| LN | One parameter of double type | =LN(C1) | Returns the natural logarithm. |
| LOG | Two parameters: the number and the base | =LOG(100, 10) | Returns the logarithm of a number to the specified base. |
| LOG10 | One parameter of double type | =LOG10(1000) | Returns the base-10 logarithm of the parameter. |
| MMULT | Two obligatory parameters: the arrays you want to multiply. The number of columns in array1 must be the same as the number of rows in array2, and both arrays must contain only numeric values. Both arrays can be specified as cell ranges, array constants, or references. | =MMULT(array1, array2) | Returns the matrix product of two arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2. You must enter formulas that return arrays only as array formulas.The MMULT function returns #VALUE! error when any of the specified cells are empty or contain non-numeric values. If the number of columns in array1 is different from the number of rows in array2, MMULT also returns the #VALUE! error. |
| MOD | Two parameters: a number and divisor | =MOD(4,3) | Returns the remainder after the number is divided by the divisor. |
| MROUND | Two parameters: the value to be rounded, and the multiple to which you want to round the value. | =MROUND(number, multiple) | Returns a number rounded to the desired multiple.MROUND rounds up, away from zero, if the remainder of dividing the number by the multiple is greater than or equal to half the value of the multiple. |
| ODD | One parameter of double type | =ODD(3.5) | Rounds the parameter up to the nearest odd integer. |
| PI | No parameters required | =PI() | Returns the value of Pi. |
| POWER | Two parameters of double type: the number and the power | =POWER(A1,4) | Raises the number to the base. |
| PRODUCT | The first parameter is the number or range that you want to multiply. Additional optional parameters that specify additional numbers or ranges that you want to multiply, up to a maximum of 255 parameters. | =PRODUCT(number1, [number2], …) | The PRODUCT multiplies all the numbers given as parameters and returns the product. For example, if cells A1 and A2 contain numbers, you can use the formula =PRODUCT(A1, A2) to multiply those two numbers together. You can also perform the same operation by using the multiply (*) mathematical operator; for example, =A1 * A2.The PRODUCT function is useful when you need to multiply multiple cells or cell ranges together. For example, the formula =PRODUCT(A1:A3, C1:C3) is equivalent to = A1 * A2 * A3 * C1 * C2 * C3. |
| QUOTIENT | Two parameters: the dividend and the divisor. | =QUOTIENT(numerator, denominator) | Returns the integer portion of a division. Use this function when you want to discard the remainder of a division. |
| RADIANS | One parameter of type double that specifies angle in degrees. | =RADIANS(180) | Converts degrees to radians. |
| RAND | No parameters required | =RAND() | Returns a random number between 0 and 1. |
| RANDBETWEEN | Two parameters of integer type: bottom that specifies the smallest returned value, and top that specifies the largest returned value. | =RANDBETWEEN(bottom, top) | Returns a random integer between the numbers you specify. A new random integer is returned every time the worksheet is calculated. |
| ROUND | Two parameters: the number of double type and the number of digits | =ROUND(20.57, 1) | Rounds the first parameter to the specified number of digits. |
| ROUNDDOWN | Two parameters of double type | =ROUNDDOWN(2.75,0) | Rounds the parameter toward zero. The first parameter specifies a number to round up. The second parameter defines the number of digits to which you want to round the first parameter. |
| ROUNDUP | Two parameters of double type | =ROUNDUP(-3.42,1) | Rounds the parameter toward infinity. The first parameter specifies a number to round up. The second parameter defines the number of digits to which you want to round the first parameter. |
| SEC | One parameter of double type. Number is the angle in radians for which you want to calculate the secant. | =SEC(number) | Returns the secant of an angle.The absolute value of the parameter must be less than 2^27.If the parameter is outside of its constraints, SEC returns the #NUM! error value.If the parameter is a non-numeric value, SEC returns the #VALUE! error value. |
| SECH | One parameter of double type. Number is the angle in radians for which you want to calculate the hyperbolic secant. | =SECH(number) | Returns the hyperbolic secant of an angle.The absolute value of the parameter must be less than 2^27.If the parameter is outside of its constraints, SECH returns the #NUM! error value.If the parameter is a non-numeric value, SECH returns the #VALUE! error value. |
| SIGN | One parameter of double type | =SIGN(A2) | Returns the sign. |
| SIN | One parameter of double type | =SIN(A1) | Returns the sine. |
| SINH | One parameter of double type | =SINH(1) | Returns the hyperbolic sine. |
| SQRT | One parameter of double type | =SQRT(B5) | Returns the square root. |
| SQRTPI | One parameter of double type. | =SQRTPI(number) | Returns the square root of (number * pi).If the parameter is negative, SQRTPI returns the #NUM! error value. |
| SUBTOTAL | The first parameter is a number from 1 to 11 (includes hidden values) or from 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list.The second parameter is the first named range or reference for which you want the subtotal.Subsequent parameters are optional and specify named ranges or references 2 to 254 for which you want the subtotal. | =SUBTOTAL(function_num, ref1, [ref2], …) | Returns a subtotal in a list or database.If there are other subtotals within ref1, ref2, … (or nested subtotals), these nested subtotals are ignored to avoid double counting.The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. However, hiding a row in a subtotal of a vertical range does affect the subtotal.If any of the references are 3-D references, SUBTOTAL returns the #VALUE! error value. |
| SUM | A list of parameters | =SUM(A1:C12)=SUM(A1, 3.14, 1.57) | Sums all the values in the list. |
| SUMIF | Three parameters. The range of cells that you want to evaluate by criteria. Blank and text values are ignored. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. The optional sum_range parameter that allows you to add more cells to those specified ion the range parameter. | =SUMIF(range, criteria, [sum_range]) | The SUMIF function is designed to sum the values in a range that meet a criteria you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 5. You can use the following formula:=SUMIF(B2:B25, “>5”)In this example, the criteria is applied the same values that are being summed. If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, “John”, C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal “John”.Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks (“). If the criteria is numeric, double quotation marks are not necessary.You can use both the question mark (?) and asterisk (*) – as the criteria parameter. A question mark matches any single character; an asterisk matches any sequence of characters. If you need to find an actual question mark or asterisk, type a tilde (~) preceding the character. |
| SUMIFS | Three mandatory parameters: the range of cells to sum, the criteria range and its associated criteria.Optionally, you can specify additional ranges and their associated criteria, up to 127 range/criteria pairs. | =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) | The SUMIFS function adds all of its arguments that meet multiple criteria.Include the criteria parameters in quotation marks if you are testing for text values.TRUE and FALSE values for the sum_range parameter are evaluated differently, which may cause unexpected results when they are added. Cells in the range passed as the sum_range parameter that contain TRUE evaluate to 1. Those that contain FALSE evaluate to 0 (zero). |
| SUMPRODUCT | The function accepts from 1 to 255 identically sized arrays. | =SUMPRODUCT(array1, [array2], [array3], …) | The function multiplies the corresponding components of all specified arrays and sums the multiplication results. If a single array is passed as a parameter, the SUMPRODUCT function sums all the array values.All the provided arrays must have the same dimensions. Otherwise, the SUMPRODUCT function returns the #VALUE! error value.The function treats all non-numeric values as zeros in the parameter arrays. |
| SUMSQ | A list of parameters | =SUMSQ(B1:D1)=SUM(1, 2, 3, 4) | Sums the square of values in the list. |
| SUMX2MY2 | The function accepts two identically sized arrays. | =SUMX2MY2(array_x, array_y) | The function sums the differences of the squared corresponding array_x and array_y values.Both parameters should be numeric values, arrays of numeric values or cell references to arrays of numeric values. Text, logical values, or empty cells within the specified parameter arrays are ignored.If sizes of the specified arrays differ, the SUMX2MY2 function returns the #N/A! error value.The function uses the following equation to calculate the result:Result = Σ(x^2 –y^2) |
| SUMX2PY2 | The function accepts two identically sized arrays. | =SUMX2PY2(array_x, array_y) | The function sums the sums of the squared corresponding array_x and array_y values.Both parameters should be numeric values, arrays of numeric values or cell references to arrays of numeric values. Text, logical values, or empty cells within the specified parameter arrays are ignored.If sizes of the specified arrays differ, the SUMX2PY2 function returns the #N/A! error value.The function uses the following equation to calculate the result:Result = Σ(x^2 + y^2) |
| SUMXMY2 | The function accepts two identically sized arrays. | =SUMXMY2(array_x,. array_y) | The function sums the squares of differences of corresponding array_x and array_y values.Both parameters should be numeric values, arrays of numeric values or cell references to arrays of numeric values. Text, logical values, or empty cells within the specified parameter arrays are ignored.If sizes of the specified arrays differ, the SUMXMY2 function returns the #N/A! error value.The function uses the following equation to calculate the result:Result = Σ(x – y)^2 |
| TAN | One parameter of double type | =TAN(C3) | Returns the tangent. |
| TANH | One parameter of double type | =TANH(0) | Returns the hyperbolic tangent. |
| TRUNC | One parameter of double type | =TRUNC(PI()) | Returns the integer part. Compare this with the INT function, which returns a double |
The ExpressSpreadSheet control provides a large selection of statistical
functions, performing most of the common statistical calculations, from
basic mean, median and mode calculations to more complex statistical
distribution and probability tests.
| Mnemonics | Operand Type | Example | Explanation |
| AVEDEV | Number1 is required, subsequent parameters are optional. You can specify from 1 to 255 numbers for which you want the average of the absolute deviations. Alternatively, you can use a single array or a reference to an array instead of parameters separated by commas. | =AVEDEV(number1, [number2], …) | Returns the average of the absolute deviations of data points from their mean. AVEDEV is a measure of the variability in a data set.AVEDEV is influenced by the unit of measurement in the input data.Parameters must either be numbers or be names, arrays, or references that contain numbers.Logical values and text representations of numbers that you type directly into the list of parameters are counted.If an array or reference parameter contains text, logical values, or empty cells, those values are ignored; however, cells with the zero value are included. |
| AVERAGE | A list of parameters | =AVERAGE(A1:A5)=AVERAGE(10.3, 9.1) | Calculates the average value of values within the list. |
| AVERAGEA | A list of parameters | =AVERAGEA(A1:A5) | Calculates the average value of the non-empty cells referenced.AVERAGEA(range) equals SUM(range)/COUNTA(range). |
| AVERAGEIF | Two mandatory parameters: the range which specifies one or more cells to average, including numbers or names, arrays or references that contain numbers; the criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged.Additionally, you can specify the actual set of cells to average. If omitted, the range parameter is used. | =AVERAGEIF(range, criteria, [average_range]) | Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.Cells that contain TRUE or FALSE are ignored.If a cell in average_range is an empty cell, AVERAGEIF ignores it.If range is a blank or text value, AVERAGEIF returns the #DIV/0! error value.If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.The average_range parameter does not have to be the same size and shape as range. The actual cells that are averaged are determined by using the top, left cell in average_range as the beginning cell, and then including cells that correspond in size and shape to range. |
| AVERAGEIFS | Three mandatory parameters: the average range, the criteria range, and criteria, subsequent criteria ranges and criteria are optional.You can specify from 1 to 127 ranges in which the AVERAGEIFS function should evaluate the associated criteria. Criteria can be expressed as a number, string, logical expression, or cell reference. | =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) | Returns the average (arithmetic mean) of all cells that meet multiple criteria.If average_range is a blank or text value, AVERAGEIFS returns the #DIV/0! error value.If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value.Cells in range that contain TRUE evaluate as 1, and cells in range that contain FALSE evaluate as 0 (zero).Unlike the range and criteria arguments in the AVERAGEIF function, in AVERAGEIFS each criteria range passed as the criteria_range parameter must have the same size and shape as the sum range.If cells referred by the average_range parameter cannot be translated into numbers, the AVERAGEIFS function returns the #DIV/0! error value.If there are no cells that meet all the provided criteria, the AVERAGEIFS function returns the #DIV/0! error value.The AVERAGEIFS function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. |
| BETA.DIST | Six parameters, the first four of which are mandatory: X is the value between A and B at which the function should be evaluated; Alpha and Beta are parameters of the distribution; Cumulative is a logical value that determines the form of the function; A and B are lower and higher bounds to the interval of X, respectively. | =BETA.DIST(x, alpha, beta, cumulative, [A], [B]) | Returns the beta distribution. The beta distribution is commonly used to study variation in the percentage of something across samples, such as fraction of the day people spend watching television.If any parameter is non-numeric, BETA.DIST returns the #VALUE! error value.If either alpha or beta is not positive, BETA.DIST returns the #NUM! error value.If x < A, x > B, or A = B, BETA.DIST returns the #NUM! error value.If you omit values for A and B, BETA.DIST uses the standard cumulative beta distribution, so that A = 0 and B = 1.If cumulative is TRUE, BETA.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function.If you omit values for A and B, BETA.DIST uses the standard cumulative beta distribution, so that A = 0, and B = 1. |
| BETA.INV | Five parameters, the first three of which are mandatory: Probability is the probability associated with the beta distribution; Alpha and Beta are parameters of the distribution; A and B are lower and higher bounds to the interval of X, respectively. | =BETA.INV(probability, alpha, beta, [A], [B]) | Returns the inverse of the beta cumulative probability density function (BETA.DIST).If probability = BETA.DIST(x, … TRUE), then BETA.INV(probability, …) = x. The beta distribution can be used in project planning to model probable completion times given an expected completion time and variability.If any parameter is non-numeric, BETA.INV returns the #VALUE! error value.If either alpha or beta is not positive, BETA.INV returns the #NUM! error value.If probability is either below zero or higher than 1, BETA.INV returns the #NUM! error value.If you omit values for A and B, BETA.INV uses the standard cumulative beta distribution, so that A = 0 and B = 1.Given a value for probability, BETA.INV seeks that value x such that BETA.DIST(x, alpha, beta, TRUE, A, B) = probability. Thus, the precision of BETA.INV depends on the precision of BETA.DIST. |
| BINOM.DIST | Four obligatory parameters: the number of successes in trials, the number of independent trials, the probability of success on each trial, and the logical value that determines the form of the function.If cumulative is TRUE, then BINOM.DIST returns the cumulative distribution function, which is the probability that there are at most number_s successes; if FALSE, it returns the probability mass function, which is the probability that there are number_s successes. | =BINOM.DIST(number_s, trials, probability_s, cumulative) | Returns the individual term binominal distribution probability. Use BINOM.DIST in problems with a fixed number of tests or trials, when the outcome of any trial are only success or failure, when trials are independent, and when the probability of success is constant throughout the experiment. For example, BINOM.DIST can calculate the probability that two of the next three babies born are male.The number_s and trials parameters are truncated to integers.If number_s, trials, or probability_s is non-numeric, BINOM.DIST returns the #VALUE! error value.If number_s < 0 or number_s > trials, BINOM.DIST returns the #NUM! error value.If probability_s < 0 or probability_s > 1, BINOM.DIST returns the #NUM! error value. |
| BINOM.DIST.RANGE | Three obligatory parameters: the number of independent trials, the probability of success in each trial, and the number of successes in trials.Additionally, you can provide an additional range bound for the number of successes in trials. | =BINOM.DIST.RANGE(trials, probability_s, number_s, [number_s2]) | Returns the probability of a trial result using a binomial distribution.If any parameters are outside of their constraints, BINOM.DIST.RANGE returns the #NUM! error value:The trials parameter must be greater than or equal to zero.The probability_s parameter must be greater than or equal to zero and less than or equal to 1.The number_s parameter must be greater than or equal to zero and less than or equal to trials.The number_s2 parameter, if provided, must be greater or equal to number_s, and less than or equal to trials.If any parameters are non-numeric values, BINOM.DIST.RANGE returns the #VALUE! error value.Numeric parameters are truncated to integers. |
| CHISQ.DIST | Three obligatory parameters: the value at which you want to evaluate the distribution, the number of degrees of freedom, and the logical value that determines the form of the function.If cumulative is TRUE, CHISQ.DIST returns the cumulative distribution function; otherwise, it returns the probability density function. | =CHISQ.DIST(x, deg_freedom, cumulative) | Returns the cumulative beta probability density functions. The chi-squared distribution is commonly used to study variation in the percentage of something across samples, such as the fraction of the day people spend watching television.If any parameter is non-numeric, CHISQ.DIST returns the #VALUE! error value.If x is negative, CHISQ.DIST returns the #NUM! error value.If deg_freedom is not an integer, it is truncated.If deg_freedom < 1 or deg_freedom > 10^10, CHISQ.DIST returns the #NUM! error value. |
| CHISQ.DIST.RT | Two obligatory parameters: the value at which you want to evaluate the distribution and the number of degrees of freedom. | =CHISQ.DIST.RT(x, deg_freedom) | Returns the one-tailed probability of the chi-squared distribution. The chi-squared distribution is associated with a chi-squared test. Use the chi-squared test to compare observed and expected values. For instance, a genetic experiment might hypothesize that the next generation of plants will exhibit a certain set of colors. By comparing the observed results with the expected ones, you can decide whether your original hypothesis is valid.If either parameter is non-numeric, CHISQ.DIST.RT function returns the #VALUE! error value.If deg_freedom is not an integer, it is truncated.If deg_freedom < 1 or deg_freedom > 10^10, CHISQ.DIST.RT returns the #NUM! error value. |
| CHISQ.INV | Two obligatory parameters: the probability associated with the chi-squared distribution and the number of degrees of freedom. | =CHISQ.INV(probability, deg_freedom) | Returns the inverse of the left-tailed probability of the chi-squared distribution. The chi-squared distribution is commonly used to study variation in the percentage of something across samples, such as the fraction of the day people spend watching television.If parameter is non-numeric, CHISQ.INV returns the #VALUE! error value.If probability < 0 or probability > 1, CHISQ.INV returns the #NUM! error value.If deg_freedom is not an integer, it is truncated.If deg_freedom < 1 or deg_freedom > 10^10, CHISQ.INV returns the #NUM! error value. |
| CHISQ.INV.RT | Two obligatory parameters: the probability associated with the chi-squared distribution and the number of degrees of freedom. | =CHISQ.INV.RT(probability, deg_freedom) | Returns the inverse of the right-tailed probability of the chi-squared distribution. If probability = CHISQ.DIST.RT(x,…), then CHISQ.INV.RT(probability,…) = x. Use this function to compare observed results with expected ones in order to decide whether your original hypothesis is valid.If either parameter is non-numeric, CHISQ.INV.RT returns the #VALUE! error value.If probability < 0 or probability > 1, CHISQ.INV.RT returns the #NUM! error value.If deg_freedom is not an integer, it is truncated.If deg_freedom < 1, CHISQ.INV.RT returns the #NUM! error value.Given a value for probability, CHISQ.INV.RT seeks that value x such that CHISQ.DIST.RT(x, deg_freedom) = probability. Thus, precision of CHISQ.INV.RT depends on precision of CHISQ.DIST.RT. CHISQ.INV.RT uses an iterative search technique. If the search has not converged after 64 iterations, the function returns the #N/A error value. |
| CORREL | Two obligatory parameters: array1 specifies a cell range of values and array2 specifies a second range of values. | =CORREL(array1, array2) | Returns the correlation coefficient of the array1 and array2 cell ranges. Use the correlation coefficient to determine the relationship between two properties. For example, you can examine the relationship between a location's average temperature and the use of air conditioners.If an array or reference parameter contains text, logical values, or empty cells, those values are ignored; however cells with the zero value are included.If array1 and array2 have a different number of data points, CORREL returns the #N/A error value.If either array1 or array2 is empty, or if s (the standard deviation) of their values equals zero, CORREL returns the #DIV/0! error value. |
| COUNT | A list of parameters | =COUNT(A1:A4)=COUNT(1, 5, 8) | Returns the number of cells in a given range. |
| COUNTA | A list of parameters | =COUNTA(A1:A4) | Counts the number of non-empty cells in a given range. |
| COUNTBLANK | A list of parameters | =COUNTBLANK(A1:E1) | Counts the number of empty cells. |
| COUNTIF | Two parameters: the range of cells and the condition of type string. | =COUNTIF(A1:A7,">2") | Counts the number of nonblank cells within a range, which meet a given condition. |
| COUNTIFS | Two mandatory parameters: the criteria range in which the function should evaluate the associated criteria and the criteria in the form of a number, expression, cell reference, or text string.Additional criteria ranges and their associated criteria are optional. You can specify up to 127 range/criteria pairs. | =COUNTIFS(criteria_range1, criteria1, [critertia_range2, criteria2], ...) | The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met.Each range's criteria is applied one cell at a time. If all of the first cells in each range meet their associated criteria, the count increases by 1. If all of the second cells meet their associated criteria, the count again increases by 1.If the criteria parameter is a reference to an empty cell, the COUNTIFS function treats the empty cell as a 0 value.You can use the question mark (?) and asterisk (*) (i.e., wildcard characters) in criteria. A question mark matches any single character, and an asterisk matches any sequence of characters. If you need to find the actual question mark or asterisk character, add a tilde (~) before the character. |
| COVARIANCE.P | Two obligatory parameters: array1 specifies the first cell range of integers and array2 specifies the second range of integers. | =COVARIANCE.P(array1, array2) | Returns population covariance, the average of the products of deviations for each data point pair in two data sets. Use covariance to determine the relationship between two data sets. For example, you can examine whether greater income accompanies greater levels of education.The parameters must either be numbers or be names, arrays, or references that contain numbers.If an array or reference parameter contains text, logical values, or empty cells, those values are ignored; however, cells with the zero value are included.If array1 and array2 have different numbers of data points, COVARIANCE.P returns the #N/A error value.If either array1 or array2 is empty, COVARIANCE.P returns the #DIV/0! error value. |
| COVARIANCE.S | Two obligatory parameters: array1 specifies the first cell range of integers and array2 specifies the second range of integers. | =COVARIANCE.S(array1, array2) | Returns the sample covariance, the average of the products of deviations for each data point pair in two data sets.The parameters must either be numbers or be names, arrays, or references that contain numbers.If an array or reference parameter contains text, logical values, or empty cells, those values are ignored; however, cells with the zero value are included.If array1 and array2 have different numbers of data points, COVARIANCE.S returns the #N/A error value.If either array1 or array2 is empty or contains only 1 data point each, COVARIANCE.S returns the #DIV/0! error value. |
| DEVSQ | The first parameter is required, subsequent numbers are optional. You can specify from 1 to 255 parameters for which you want to calculate the sum of squared deviations. You can also use a single array or a reference to an array instead of parameters separated by commas. | =DEVSQ(number1, [number2], …) | Returns the sum of squares of deviations of data points from their sample mean.Parameters can either be numbers or names, arrays, or references that contain numbers.Logical values and text representations of numbers that you type directly into the list of parameters are counted.If an array or reference parameter contains text, logical values, or empty cells, those values are ignored; however, cells with the zero value are included.Parameters that are error values or text that cannot be translated into numbers cause errors. |
| EXPON.DIST | Three obligatory parameters: the value of the function, the parameter value, and the logical value that indicates which form of the exponential function to provide.If cumulative is TRUE, EXPON.DIST returns the cumulative distribution function; otherwise, it returns the probability density function. | =EXPON.DIST(x, lambda, cumulative) | Returns the exponential distribution. Use EXPON.DIST to model the time between events, such as how long an automated bank teller takes to deliver cash. For example, you can use EXPON.DIST to determine the probability that the process takes at most 1 minute.If x or lambda is non-numeric, EXPON.DIST returns the #VALUE! error value.If x < 0, EXPON.DIST returns the #NUM! error value.If lambda is not positive, EXPON.DIST returns the #NUM! error value. |
| FORECAST | Three obligatory parameters: the data point for which you want to predict a value, the dependent array or range of data, and the independent array or range of data. | =FORECAST(x, known_y's, known_x's) | Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, or consumer trends.If x is non-numeric, FORECAST returns the #VALUE! error value.If known_y's and known_x's are empty or contain different number of data points, FORECAST returns the #N/A error value.If the variance of known_x's equals zero, then FORECAST returns the #DIV/0! error value. |
| GAMMA | The number parameter is mandatory. | =GAMMA(number) | Returns the gamma function value.If number is not positive, GAMMA returns the #NUM! error value.If number contains characters that are not valid, GAMMA returns the #VALUE! error value. |
| GAMMA.DIST | Four mandatory parameters: X – the value at which you want to evaluate the distribution; Alpha and Beta are parameters to the distribution; Cumulative is a logical value that determines the form of the function. | =GAMMA.DIST(x, alpha, beta, cumulative) | Returns the gamma distribution. You can use this function to study variables that may have a skewed distribution. The gamma distribution is commonly used in queuing analysis.If x, alpha, or beta is non-numeric, GAMMA.DIST returns the #VALUE! error value.If x is negative, GAMMA.DIST returns the #NUM! error value.If either alpha or beta is not positive, GAMMA.DIST returns the #NUM! error value.If beta = 1, GAMMA.DIST returns the standard gamma distribution.If cumulative is TRUE, GAMMA.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function. |
| GAMMA.INV | Three mandatory parameters: Probability is the probability associated with the gamma distribution; Alpha and beta are parameters to the distribution. | =GAMMA.INV(probability,alpha, beta) | Returns the inverse of the gamma cumulative distribution. If p = GAMMA.DIST(x, …), then GAMMA.INV(p, …) = x. You can use this function to study a variable whose distribution may be skewed.If beta = 1, GAMMA.INV returns the standard gamma distribution.If any parameter is a text string, GAMMA.INV returns the #VALUE! error value.If the probability is either negative or higher than 1, GAMMA.INV returns the #NUM! error value.If either alpha or beta is not positive, GAMMA.INV returns the #NUM! error value.Given a value for the probability, GAMMA.INV seeks that value x such that GAMMA.DIST(x, alpha, beta, TRUE) = probability. Thus, the precision of GAMMA.INV depends on precision of GAMMA.DIST. |
| GAMMALN | A single mandatory parameter of double type, which is the value for which you want to calculate GAMMALN. | =GAMMALN(X) | Returns the natural logarithm of the gamma function, ((x).If x is non-numeric, GAMMALN returns the #VALUE! error value.If x is not positive, GAMMALN returns the #NUM! error value.The number e raised to the GAMMALN(i) power, where i is an integer, returns the same result as (I – 1)!. |
| GAMMALN.PRECISE | A single mandatory parameter of double type, which is the value for which you want to calculate the natural logarithm of the gamma function. | =GAMMALN.PRECISE(x) | Returns the natural logarithm of the gamma function, ((x).If x is non-numeric, GAMMALN.PRECISE returns the #VALUE! error value.If x is not positive, GAMMALN.PRECISE returns the #NUM! error value.The number e raised to the GAMMALN.PRECISE(i) power, where i is an integer, returns the same result as (I – 1)!. |
| GAUSS | The z parameter is mandatory and specifies a number. | =GAUSS(z) | Calculates the probability that a member of a standard normal population will fall between the mean and z standard deviations from the mean.If z is not a valid number, GAUSS returns the #NUM! error value.If z is not a valid data type, GAUSS returns the #VALUE! error value.Because NORM.S.DIST(0, True) always returns 0.5, GAUSS(z) will always be 0.5 less than NORM.S.DIST(z, True). |
| GEOMEAN | Up to 255 parameters, for which you calculate the mean. Only the first parameter is mandatory. You can also use a single array instead of number of parameters. | =GEOMEAN(number1, [number2], …) | Returns the geometric mean of an array or range of positive data. For example, you can use GEOMEAN to calculate average growth rate given compound interest with variable rates.Parameters can either be numbers or names, arrays, or references that contain numbers.Logical values and text representations of numbers that you type directly into the list of parameters are counted.If an array or reference parameter contains text, logical values, or empty cells, those values are ignored; however, cells with the zero value are included.Error values or text strings that cannot be translated into numbers cause errors, if you use them as parameters.If any data point is not positive, GEOMEAN returns the #NUM! error value. |
| HYPGEOM.DIST | Five obligatory parameters: the number of successes in the sample, the size of the sample, the number of successes in the population, the population size, and the logical value that determines the form of the function.If cumulative is TRUE, then HYPGEOM.DIST returns the cumulative distribution function; otherwise, it returns the probability mass function. | =HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative) | Returns the hypergeometric distribution. HYPGEOM.DIST returns the probability of a given number of sample successes, given the sample size, population successes, and population size. Use HYPGEOM.DIST for problems with a finite population, where each observation is either a success or a failure, and where each subset of a given size is chosen with equal likelihood.All parameters are truncated to integers.If any parameter is non-numeric, HYPGEOM.DIST returns the #VALUE! error value.If sample_s < 0 or sample_s is greater than the lesser of number_sample or population_s, HYPGEOM.DIST returns the #NUM! error value.If number_sample is not positive or number_sample > number_population, HYPGEOM.DIST returns the #NUM! error value.If population_s is not positive or population_s > number_population, HYPGEOM.DIST returns the #NUM! error value.If number_pop is not positive, HYPGEOM.DIST returns the #NUM! error value. |
| INTERCEPT | Two obligatory parameters: the dependent and independent sets of observations or data. | =INTERCEPT(known_y's, known_x's) | Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values. The intercept point is based on a best-fit regression line plotted through the known x-values and known y-values. Use the INTERCEPT function when you want to determine the value of the dependent variable when the independent variable is 0 (zero). For example, you can use the INTERCEPT function to predict a metal's electrical resistance at a certain temperature when your data points were taken at room temperature and higher.The parameters should be either numbers or names, arrays, or references that contain numbers.If an array or reference parameter contains text, logical values, or empty cells, those values are ignored; however, cells with the zero value are included.If known_y's and known_x's contain a different number of data points or contain no data points, INTERCEPT returns the #N/A error value. |
| LARGE | Two parameters: the array or range of data for which you want to determine the k-th largest value. K is the position (from the largest) in the array or cell range of data to return. | =LARGE(array, k) | Returns the k-th largest value in a data set. You can use this function to select a value based on its relative standing. For example, you can use LARGE to return the highest, runner-up, or third-place score.If array is empty, LARGE returns the #NUM! error value.If k is not positive or if k is greater than the number of data points, LARGE returns the #NUM! error value.If n is the number of data points in a range, then LARGE(array, 1) returns the largest value, and LARGE(array, n) returns the smallest value. |
| MAX | A list of parameters | =MAX(A1:D1)=MAX(A1, 100, C2) | Returns the largest parameter value. |
| MAXA | From 1 to 255 parameters. Value1 is the mandatory parameter for which you want to find the largest value; the following values from 2 to 255 are optional parameters for which you want to find the largest value. | =MAXA(value1, [value2], …) | Returns the largest value in a list of parameters. Parameters can be the following: numbers; names, arrays, or references that contain numbers; text representations of numbers; logical values, such as TRUE and FALSE, in a reference.Logical values and text representations of numbers that you type directly into the list of parameters are counted.If an parameter is an array or reference, only values in that array or reference are used. Empty cells and text values in the array or reference are ignored.If the parameters contain no values, MAXA returns 0 (zero).If you do not want to include logical values and text representations of numbers in a reference s part of the calculation, use the MAX function instead. |
| MEDIAN | From 1 to 255 parameters. Only the number1 is required, subsequent numbers are optional. | =MEDIAN(number1, [number2], …) | Returns the median of the given numbers. The median is the number in the middle of a set of numbers.Note that the MEDIAN function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. |
| MIN | A list of parameters | =MIN(A1:D1)=MIN(0, C2) | Returns the smallest parameter value. |
| MINA | From 1 to 255 parameters. Only the value1 is required, subsequent values are optional. | =MINA(number1, [number2], …) | Returns the smallest value in a list of parameters, including members, text, and logical values. Parameters can be the following: numbers; names, arrays, or references that contain numbers; text representations of numbers; logical values, such as TRUE and FALSE, in a reference.Logical values and text representations of numbers that you type directly into the list of parameters are counted.If an parameter is an array or reference, only values in that array or reference are used. Empty cells and text values in the array or reference are ignored.If the parameters contain no values, MINA returns 0 (zero).If you do not want to include logical values and text representations of numbers in a reference s part of the calculation, use the MIN function instead. |
| NORM.DIST | Four obligatory parameters: the value for which you want the distribution, the arithmetic mean of the distribution, the standard deviation of the distribution, and the logical value that determines the form of the function.If cumulative is TRUE, NORM.DIST returns the cumulative distribution function; otherwise, it returns the probability mass function. | =NORM.DIST(x, mean, standard_dev, cumulative) | Returns the normal distribution for the specified mean and standard deviation. This function has a very wide range of applications in statistics, including hypothesis testing.If mean or standard_dev is non-numeric, NORM.DIST returns the #VALUE! error value.If standard_dev is not positive, NORM.DIST returns the #NUM! error value.If mean = 0, standard_dev = 1, and cumulative = TRUE, NORM.DIST returns the standard normal distribution, NORM.S.DIST. |
| NORM.INV | Three mandatory parameters: the probability corresponding to the normal distribution, the arithmetic mean of the distribution, and the standard deviation of the distribution. | =NORM.INV(probability, mean, standard_dev) | Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.If any parameter is non-numeric, NORM.INV returns the #VALUE! error value.If probability is not positive or if probability > 1, NORM.INV returns the #NUM! error value.If standard_dev is not positive, NORM.INV returns the #NUM! error value.If mean = 0 and standard_dev = 1, NORM.INV uses the standard normal distribution.Given a value for probability, NORM.INV seeks that value x such that NORM.DIST(x, mean, standard_dev, TRUE) = probability. Thus, precision of NORM.INV depends on precision of NORM.DIST. |
| NORM.S.DIST | Two mandatory parameters: the value for which you want the distribution, and the cumulative logical value that determines the form of the function.If cumulative is TRUE, NORM.S.DIST returns the cumulative distribution function; otherwise, it returns the probability mass function. | =NORM.S.DIST(z, cumulative) | Returns the standard normal distribution (has a mean of zero and a standard deviation of one). Use this function in place of a table of standard normal curve areas.If z is non-numeric, NORM.S.DIST returns the #VALUE! error value. |
| NORM.S.INV | The probability parameter is mandatory. | =NORM.S.INV(probability) | Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one.If probability is non-numeric, NORM.S.INV returns the #VALUE! error value.If probability is not positive or if probability >= 1, NORM.S.INV returns the #NUM! error value.Given a value for probability, NORM.S.INV seeks that value z that NORM.S.DIST(z, TRUE) = probability. Thus, precision of NORM.S.INV depends on precision of NORM.S.DIST. NORM.S.INV uses an iterative search technique. |
| PEARSON | Two mandatory parameters: array1 and array2 which represent two sets of independent and dependent values, respectively. | =PEARSON(array1, array2) | Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from –1.0 to 1.0 inclusive and reflects the extent of a linear relationship between two data sets.The parameters must be either numbers or names, array constants, or references that contain numbers.If an array or reference parameter contains text, logical values, or empty cells, those values are ignored; however, cells with the zero value are included.If array1 and array2 are empty or have a different number of data points, PEARSON returns the #N/A error value. |
| PERCENTILE.INC | Two mandatory parameters: the array or range of data that defines relative standing, and the percentile value in the range 0..1, inclusive. | =PERCENTILE.INC(array, k) | Returns the k-th percentile of values in a range. You can use this function to establish a threshold of acceptance. For example, you can decide to examine candidates who score above 90th percentile.If array is empty, PERCENTILE.INC returns the #NUM! error value.If k is non-numeric, PERCENTILE.INC returns the #VALUE! error value.If k is < 0 or if k > 1, PERCENTILE.INC returns the #NUM! error value.If k is not a multiple of 1/(n – 1), PERCENTILE.INC interpolates to determine the value at the k-th percentile. |
| PERCENTILE.EXC | Two mandatory parameters: the array or range of data that defines relative standing, and the percentile value in the range 0..1, exclusive. | =PERCENTILE.EXC(array, k) | Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive.If array is empty, PERCENTILE.EXC returns the #NUM! error value.If k is non-numeric, PERCENTILE.EXC returns the #VALUE! error value.If k is not positive or if k is equal to or exceeds 1, PERCENTILE.EXC returns the #NUM! error value.If k is not a multiple of 1/(n – 1), PERCENTILE.EXC interpolates to determine the value at the k-th percentile.PERCENTILE.EXC will interpolate when the value for the specified percentile lies between two values in the array. If it cannot interpolate for the percentile, specified by the k parameter, the ExpressSpreadSheet returns #NUM! error. |
| PERMUT | Two mandatory parameters: integer values that describe the number of objects and the number of objects in each permutation. | =PERMUT(number, number_chosen) | Returns the number of permutations for a given number of objects that can be selected from number objects. A permutation is any set or subset of objects or events where internal order is significant. Permutations are different from combinations, for which the internal order is not significant. Use this function for lottery-style probability calculations.Both of the accepted parameters are truncated to integers.If number or number_chosen is non-numeric, PERMUT returns the #VALUE! error value.If number is not positive or if number_chosen is negative, PERMUT returns the #NUM! error value.If number < number_chosen, PERMUT returns the #NUM! error value. |
| POISSON.DIST | Three mandatory parameters: the number of events, the expected numeric value, and the cumulative logical value that determines the form of the probability distribution returned.If cumulative is TRUE, POISSON.DIST returns the cumulative Poisson probability that the number of random events occurring will fall between zero and x inclusive; otherwise, it returns the Poisson probability mass function that the number of events occurring will be exactly x. | =POISSON.DIST(x, mean, cumulative) | Returns the Poisson distribution. A common application of the Poisson distribution is predicting the number of events over a specific time, such as the number of cars arriving at a toll plaza in 1 minute.If x is not an integer, it is truncated.If x or mean is non-numeric, POISSON.DIST returns the #VALUE! error value.If x < 0, POISSON.DIST returns the #NUM! error value.If mean < 0, POISSON.DIST returns the #NUM! error value. |
| QUARTILE.INC | Two mandatory parameters: the array or cell range of numeric values for which you want the quartile value, and the quart that indicates which value to return.If quart = 0, QUARTILE.INC returns the minimum value.If quart = 1, QUARTILE.INC returns the first quartile (25th percentile).If quart = 2, QUARTILE.INC returns the median value (50th percentile).If quart = 3, QUARTILE.INC returns the third quartile (75th percentile).If quart = 4, QUARTILE.INC returns the maximum value. | =QUARTILE.INC(array, quart) | Returns the quartile of a data set, based on percentile values from 0..1, inclusive. Quartiles often are used in sales and survey data to divide populations into groups. For example, you can use QUARTILE.INC to find the top 25 percent of incomes in a population.If array is empty, QUARTILE.INC returns the #NUM! error value.If quart is not an integer, it is truncated.If quart < 0 or if quart > 4, QUARTILE.INC returns the #NUM! error value.MIN, MEDIAN, and MAX return the same value as QUARTILE.INC when quart is equal to 0 (zero), 2, and 4, respectively. |
| QUARTILE.EXC | Two mandatory parameters: the array or cell range of numeric values for which you want the quartile value, and the quart that indicates which value to return. | =QUARTILE.EXC(array, quart) | Returns the quartile of the data set, based on percentile values from 0..1, exclusive.If array is empty, QUARTILE.EXC returns the #NUM! error value.If quart is not an integer, it is truncated.If quart is not positive or if quart equals to or exceeds 4, QUARTILE.EXC returns the #NUM! error value.MIN, MEDIAN, and MAX return the same value as QUARTILE.EXC when quart is equal to 0 (zero), 2, and 4, respectively. |
| RANK.AVG | Two mandatory parameters: the number whose rank you want to find, and the ref parameter specifies an array of, or a reference to, a list of numbers.Additionally, you can set the way of ranking the number as the order parameter. | =RANK.AVG(number, ref, [order]) | Returns the rank of a number in a list of numbers: its size relative to other values in the list. If more than one value has the same rank, the average rank is returned.If order is 0 (zero) or omitted, the ExpressSpreadSheet ranks number as if ref were a list sorted in descending order.If order is any non-zero value, the ExpressSpreadSheet ranks number as if ref were a list sorted in ascending order. |
| RANK.EQ | Two mandatory parameters: the number whose rank you want to find, and the ref parameter specifies an array of, or a reference to, a list of numbers.Additionally, you can set the way of ranking the number as the order parameter. | =RANK.EQ(number, ref, [order]) | Returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned. If you were to sort the list, the rank of the number would be its position.If order is 0 (zero) or omitted, the ExpressSpreadSheet ranks number as if ref were a list sorted in descending order.If order is any non-zero value, the ExpressSpreadSheet ranks number as if ref were a list sorted in ascending order.RANK.EQ gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers sorted in ascending order, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).For some purposes one might want to use a definition of rank that takes ties into account. In previous example, you would want a revised rank of 5.5 for the number 10. This can be done by adding the following correction factor to the value returned by RANK.EQ. This correction factor is appropriate oth for the case where rank is calculated in descending order (order = 0 or omitted) or ascending order (order = non-zero value).Correction factor for tied ranks=[COUNT(ref) + 1 – RANK.EQ(number, ref, 1)]/2. |
| RSQ | Two mandatory parameters are two arrays or ranges of data points. | =RSQ(known_y's, known_x's) | Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's. For more information, refer to the description of the PEARSON function. The r-squared value can be interpreted as the proportion of the variance in y attributable to the variance in x.Parameters can either be numbers or names, arrays, or references that contain numbers.Logical values and text representations of numbers that you type directly into the list of parameters are counted.If an array or reference parameter contains text, logical values, or empty cells, those values are ignored; however, cells with the zero value are included.Parameters that are error values or text that cannot be translated into numbers cause errors.If known_y's and known_x's are empty or have a different number of data points, RSQ returns the #N/A error value.If known_y's and known_x's contain only 1 data point, RSQ returns the #DIV/0! error value. |
| SKEW | The SKEW function accepts from one to 255 parameters for which you want to calculate skewness. You can also use a single array or a reference to an array instead of parameters separated by commas.Note that you must specify at least one parameter. | =SKEW(number1, [number2], ...) | Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry of a distribution around its mean. Positive skewness indicates a distribution with an asymmetric tail extending toward more positive values. Negative skewness indicates a distribution with an asymmetric tail extending towards more negative values.Parameters can either be numbers or names, arrays, or references that contain numbers.Logical values and text representations of numbers that you type directly into the list of parameters are counted.If an array or reference parameter contains text, logical values, or empty cells, those values are ignored; however, cells with the zero value are included.Parameters that are error values or text that cannot be translated into numbers cause errors.If there are fewer than three data points, or the sample standard deviation is zero, SKEW returns the #DIV/0! error value. |
| SKEW.P | The SKEW function accepts from one to 255 parameters for which you want to calculate skewness. You can also use a single array or a reference to an array instead of parameters separated by commas.Note that you must specify at least one parameter. | =SKEW.P(number1, [number2], …) | Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean.Parameters can either be numbers or names, arrays, or references that contain numbers.Logical values and text representations of numbers that you type directly into the list of parameters are counted.If an array or reference parameter contains text, logical values, or empty cells, those values are ignored; however, cells with the zero value are included.SKEW.P uses the standard deviation of an entire population, not a sample.If parameters are values that are not valid, SKEW.P returns the #NUM! error value.If parameters use data types that are not valid, SKEW.P returns the #VALUE! error value.If there are fewer than three data points, or the sample standard deviation is zero, SKEW.P returns the #DIV/0! error value. |
| SLOPE | Two mandatory parameters: an array or cell range of numeric dependent data points and the set of independent data points. | =SLOPE(known_y's, known_x's) | Returns the slope of the linear regression line through data points in known_y's and known_x's. The slope is the vertical distance divided by the horizontal distance between any two points on the line, which is the rate of change along the regression line.The parameters must be either numbers or names, arrays, or references that contain numbers.If an array or reference parameter contains text, logical values, or empty cells, those values are ignored; however, cells with the zero value are included.If known_y's and known_x's are empty or have a different number of data points, SLOPE returns the #N/A value. |
| SMALL | Two mandatory parameters: an array or range of numerical data for which you want to determine the k-th smallest value and the position (from the smallest) in the array or range of data to return. | =SMALL(array, k) | Returns the k-th smallest value in a data set. Use this function to return values with a particular relative standing in a data set.If array is empty, SMALL returns the #NUM! error value.If k is not positive or if k exceeds the number of data points, SMALL returns the #NUM! error value.If n is the number of data points in array, SMALL(array, 1) equals the smallest value, and SMALL(array, n) equals the largest value. |
| STDEV.S | This function accepts from 1 to 255 parameters. Only the number1 is required, subsequent values are optional.You can also use a single array or a reference to an array instead of parameters separated by commas. | =STDEV.S(number1, [number2], …) | Estimates the standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).STDEV.S assumes that its parameters are a sample of the population. If your data represents the entire population, then calculate the standard deviation using the STDEV.P function instead.The standard deviation is calculated using the “n-1” method.Parameters can either be numbers or names, arrays, or references that contain numbers.Logical values and text representations of numbers that you type directly into the list of parameters are counted.If an parameter is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.Parameters that are error values or text that cannot be converted into numbers cause errors.If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the STDEVA function instead. |
| STDEV.P | From 1 to 255 parameters. Only the number1 is required, subsequent values are optional.You can also use a single array or a reference to an array instead of parameters separated by commas. | =STDEV.P(number1, [number2], …) | Estimates the standard deviation based on the entire population. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).STDEV.P assumes that its parameters are the entire population. If you data represents a sample of the population, then calculate the standard deviation using STDEV.For large sample sizes, STDEV.S and STDEV.P return approximately equal values.The standard deviation is calculated using the “n” method.Parameters can either be numbers or names, arrays, or references that contain numbers.Logical values, and text representations of numbers that you type directly into the list of parameters are counted.If an parameter is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.Parameters that are error values or text that cannot be converted into numbers cause errors.If you want to include logical values and text representations of numbers in a reference part of the calculation, use the STDEVPA function instead. |
| STDEVA | From 1 to 255 parameters. Only the value1 is required, subsequent values are optional.You can also use a single array or a reference to an array instead of parameters separated by commas. | =STDEVA(value1, [value2], …) | Estimates the standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).STDEVA assumes that its parameters are a sample of the population. If your data represents the entire population, you must calculate the standard deviation using STDEVPA instead.The standard deviation is calculated using the “n–1” method.Parameters can be the following: numbers, names, arrays, or references that contain numbers; text representations of numbers, or logical values, such as TRUE and FALSE, in a reference.Parameters that contain TRUE evaluate as 1; parameters that contain text or FALSE evaluate as 0 (zero).If an parameter is an array or reference, only values in that array or reference are used. Empty cells and text values in that array or reference are ignored.Parameters that are error values or text that cannot be translated into numbers cause errors.If you do not want to include logical values and text representations of numbers in a reference as part of the calculation, use the STDEV function instead. |
| STDEVPA | From 1 to 255 parameters. Only the value1 is required, subsequent values are optional.You can also use a single array or a reference to an array instead of parameters separated by commas. | =STDEVPA(value1, [value2], …) | Calculates the standard deviation based on the entire population, including numbers, text, and logical values. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).STDEVPA assumes that its parameters are the entire population. If your data represents a sample of the population, you must calculate the standard deviation by using the STDEVA function.The standard deviation is calculated using the “n” method.Parameters can be the following: numbers, names, arrays, or references that contain numbers; text representations of numbers, or logical values, such as TRUE and FALSE, in a reference.Text representations of numbers that you type directly into the list of parameters are counted.Parameters that contain TRUE evaluate as 1; parameters that contain text or FALSE evaluate as 0 (zero).If an parameter is an array or reference, only values in that array or reference are used. Empty cells and text values in the array or reference are ignored.Parameters that are error values or text that cannot be converted into numbers cause errors.If you do not want to include logical values and text representations of numbers in a reference as part of the calculation, use the STDEVP function instead. |
| STEYX | Two mandatory parameters: the two arrays or ranges of dependent and independent data points, respectively. | =STEYX(known_y's, known_x's) | Returns the standard error of the predicted y-value for each x in the regression. The standard error is a measure of the amount of error in the prediction of y for an individual x.Parameters can either be numbers or names, arrays, or references that contain numbers.Logical values and text representations of numbers that you type directly into the list of parameters are counted.In an array or reference parameter contains text, logical values, or empty cells, those values are ignored; however, cells with the zero value are included.Parameters that are error values or text that cannot be converted into numbers cause errors.If known_y's and known_x's have a different number of data points, STEYX returns the #N/A error value.If known_y's and known_x's are empty or have less than three data points, STEYX returns the #DIV/0! error value. |
| SUM | A list of parameters | =SUM(A1:C12)=SUM(A1, 3.14, 1.57) | Sums values within the list. |
| SUMSQ | A list of parameters | =SUMSQ(B1:D1)=SUMSQ(1, 2, 3, 4) | Sums squares of values within the list. |
| T.DIST | Three obligatory parameters: the numeric value at which to evaluate the distribution, the integer value indicating the number of degrees of freedom, and the cumulative logical value that determines the form of the function.If cumulative is TRUE, T.DIST returns the cumulative distribution function; otherwise, it returns the probability density function. | =T.DIST(x, deg_freedom, cumulative) | Returns the Student's left-tailed t-distribution. The t-distribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the t-distribution.If any parameter is non-numeric, T.DIST returns the #VALUE! error value.If deg_freedom < 1, T.DIST returns an error value. The deg_freedom parameter should be at no less than 1. |
| T.DIST.2T | Two obligatory parameters: the numeric value at which to evaluate the distribution, and the integer value that indicates the number of degrees of freedom. | =T.DIST.2T(x, deg_freedom) | Returns the two-tailed Student's t-distribution. The Student's t-distribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the t-distribution.If any parameter is non-numeric, T.DIST.2T returns the #VALUE! error value.If deg_freedom < 1, T.DIST.2T returns the #NUM! error value.If x < 0, then T.DIST.2T returns the #NUM! error value. |
| T.DIST.RT | Two obligatory parameters: the numeric value at which to evaluate the distribution, and the integer value that indicates the number of degrees of freedom. | =T.DIST.RT(x, deg_freedom) | Returns the right-tailed Student's t-distribution. The t-distribution is used in the hypothesis testing of small data sets. Use this function in place of a table of critical values for the t-distribution.If any parameter is non-numeric, T.DIST.RT returns the #VALUE! error value.If deg_freedom < 1, T.DIST.RT returns the #NUM! error value. |
| T.INV | Two obligatory parameters: the probability associated with the Student's t-distribution and the number of degrees of freedom with which to characterize the distribution. | =T.INV(probability, deg_freedom) | Returns the left-tailed inverse of the student's t-distribution.If either parameter is non-numeric, T.INV returns the #VALUE! error value.If probability <= 0 or if probability> 1, T.INV returns the #NUM! error value.If deg_freedom is not an integer, it is truncated.If deg_freedom < 1, T.INV returns the #NUM! error value. |
| T.INV.2T | Two obligatory parameters: the probability associated with the Student's t-distribution and the number of degrees of freedom with which to characterize the distribution. | =T.INV.2T(probability, deg_freedom) | Returns the two-tailed inverse of the Student's t-distribution.If either parameter is non-numeric, T.INV.2T returns the #VALUE! error value.If probability <= 0 or if probability> 1, T.INV.2T returns the #NUM! error value.If deg_freedom < 1, T.INV.2T returns the #NUM! error value.T.INV.2T returns that value t, such that P(|X| > t) = probability where X is a random variable that follows the t-distribution and P(|X| > t) = P(X < -t or X > t).A one-tailed t-value can be returned by replacing probability with 2*probability. For a probability of 0.05 and degrees of freedom of 10, the two-tailed value is calculated with T.INV.2T(0.05, 10), which returns 2.28139. The one-tailed value of the same probability and degrees of freedom can be calculated with T.INV.2T(2 * 0.05, 10), which returns 1.812462.Given a value for probability, T.INV.2T seeks that value x such that T.DIST.2T(x, deg_freedom, 2) = probability. Thus, precision of T.INV.2T depends on precision of T.DIST.2T. |
| VAR.P | From 1 to 255 parameters. Only the number1 is required, subsequent values are optional. | =VAR.P(number1, [number2], …) | Calculates variance based on the entire population (ignores logical values and text in the population).VAR.P assumes that its parameters are the entire population. If your data represents a sample of the population, than calculate the variance by using the VAR.S function instead.Parameters can either be numbers or names, arrays, or references that contain numbers.Logical values, and text representations of numbers that you type directly into the list of parameters are counted.If an parameter is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text or error values in the array or reference are ignored.Parameters that are error values or text that cannot be translated into numbers cause errors.If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the VARPA function instead. |
| VAR.S | From 1 to 255 parameters. Only the number1 is required, subsequent values are optional. | =VAR.S(number1, [number2], …) | Estimates variance based on a sample (ignores logical values and text in the sample).VAR.S assumes that its parameters are a sample of the population. If your data represents the entire population, then calculate the variance by using the VAR.P function instead.Parameters can either be numbers or names, arrays, or references that contain numbers.Logical values, and text representations of numbers that you type directly into the list of parameters are counted.If an parameter is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.Parameters that are error values or text that cannot be translated into numbers cause errors.If you want to include logical values or text representations of numbers in a reference as part of the calculation, use the VARA function instead. |
| VARA | From 1 to 255 parameters. Only the value1 is required, subsequent values are optional. | =VARA(value1, [value2], …) | Estimates variance based on a sample.VARA assumes that its parameters are a sample of the population. If your data represents the entire population, you must calculate the variance by using the VARPA function instead.Parameters can be the following: numbers, names, arrays, or references that contain numbers; text representations of numbers, or logical values, such as TRUE and FALSE, in a reference.Logical values and text representations of numbers that you type directly into the list of parameters are counted.Parameters that contain TRUE evaluate as 1; parameters that contain text or FALSE evaluate as 0 (zero).If an parameter is an array or reference, only values in that array or reference are used. Empty cells and text values in the array or reference are ignored.Parameters that are error values or text that cannot be translated into numbers cause errors.If you do not want to include logical values and text representations of numbers in a reference as part of the calculation, use the VAR.P and VAR.S functions instead. |
| VARPA | From 1 to 255 parameters. Only the value1 is required, subsequent values are optional. | =VARPA(value1, [value2], …) | Calculates variance based on the entire population.VARPA assumes that its parameters are the entire population. If your data represents a sample of the population, you must calculate the variance by using the VARA function instead.Parameters can be the following: numbers, names, arrays, or references that contain numbers; text representations of numbers, or logical values, such as TRUE and FALSE, in a reference.Logical values and text representations of numbers that you type directly into the list of parameters are counted.Parameters that contain TRUE evaluate as 1; parameters that contain text or FALSE, evaluate as 0 (zero).If an parameter is an array or reference, only values are used in that array or reference. Empty cells and text values in the array or reference are ignored.Parameters that are error values or text that cannot be translated into numbers cause errors.If you do not want to include logical values or text representations of numbers in a reference as part of the calculation, use the VARP function instead. |
| WEIBULL.DIST | Four obligatory parameters: the value at which to evaluate the function, the alpha and beta parameters to the distribution, and the cumulative parameter that determines the form of the function. | =WEIBULL.DIST(x, alpha, beta, cumulative) | Returns the Weibull distribution. Use this distribution in reliability analysis, such as calculating a device's mean time to failure.If x, alpha, or beta is non-numeric, WEIBULL.DIST returns the #VALUE! error value.If x is negative, the WEIBULL.DIST function returns the #NUM! error value.If either alpha or beta is not positive, WEIBULL.DIST returns the #NUM! error value. |
Financial functions are designed for financial calculations and
analyses. Use them to perform many of the commonly used financial
calculations, such as the calculation of yield, interest rates,
investment valuations, internal rate of return, payments and asset
depreciation.
| Mnemonics | Operand Type | Example | Explanation |
| FV | Three mandatory parameters: the interest rate per period, the total number of payment periods in an annuity, and the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must specify the pv parameter instead.Additionally, you can specify the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt parameter.The second optional parameter is type that indicates when payments are due. If type is omitted, it is assumed to be 0. | =FV(rate, nper, pmt, [pv], [type]) | Returns the future value of an investment.Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.For all parameters, cash you pay out, such as deposits to savings, is represented by negative numbers; cash you receive, such as dividend checks, is represented by positive numbers. |
| IPMT | Four mandatory parameters include: the interest rate per period, the period for which you want to find the interest and must be in the range 1 to nper, the total number of payment periods in an annuity, and the lump-sum that a series of future payments is worth right now.Additionally, you can specify two more parameters that include: the future value, or a cash balance you want to attain after the last payment is made (if this parameter is omitted, it is assumed to be 0), and the type that indicates when payments are due (if type is omitted, it is assumed to be 0). | =IPMT(rate, per, nper, pv, [fv], [type]) | Returns the interest payment for an investment for a given period.Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.For all parameters, cash you pay out, such as deposits to savings, is represented by negative numbers; cash you receive, such as dividend checks, is represented by positive numbers. |
| NPER | Three mandatory parameters include: the interest rate per period, the payment made each period which cannot change over the life of annuity, and the lump-sum amount that a series of future payments is worth right now.Additionally, you can specify the cash balance you want to attain after the last payment is made (if this parameter is omitted, it is assumed to be 0), and the type that indicates when payments are due. | =NPER(rate, pmt, pv, [fv], [type]) | Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. |
| NPV | Two mandatory parameters include the rate of discount over the length of one period and the value1 parameter specifies the payments and income. You can specify up to 254 value parameters. | =NPV(rate, value1, [value2], …) | Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).The NPV investment begins one period before the date of the value1 cash flow and ends with the last cash flow in the list. The NPV calculation is based on future cash flows. If your first cash flow occurs at the beginning of the first period, the first value must be added to the NPV result, not included in the value parameters.NPV is similar to the PV function (present value). The primary difference between PV and NPV is that PV allows cash flows to begin either at the end or at the beginning of the period. Unlike the variable NPV cash flow values, PV cash flows must be constant throughout the investment. For information about annuities and financial functions, refer to the description of the PV function. |
| PMT | Three mandatory parameters include: the interest rate per period, the payment made each period which cannot change over the life of annuity, and the lump-sum amount that a series of future payments is worth right now.Additionally, you can specify the cash balance you want to attain after the last payment is made (if this parameter is omitted, it is assumed to be 0), and the type that indicates when payments are due. | =PMT(rate, nper, pv, [fv], [type]) | Calculates the payment for a loan based on constant payments and a constant interest rate.The payment returned by PMT includes principal and interest but no taxes, reserve payments, or fees sometimes associated with loans.Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4 * 12 for nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for nper. |
| PPMT | Four mandatory parameters include: the interest rate per period, the period for which you want to find the interest and must be in the range 1 to nper, the total number of payment periods in an annuity, and the lump-sum that a series of future payments is worth right now.Additionally, you can specify two more parameters that include: the future value, or a cash balance you want to attain after the last payment is made (if this parameter is omitted, it is assumed to be 0), and the type that indicates when payments are due (if type is omitted, it is assumed to be 0). | =PPMT(rate, per, nper, pv, [fv], [type]) | Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper. |
| PV | Three mandatory parameters: the interest rate per period, the total number of payment periods in an annuity, and the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must specify the pv parameter instead.Additionally, you can specify the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt parameter.The second optional parameter is type that indicates when payments are due. If type is omitted, it is assumed to be 0. | =PV(rate, nper, pmt, [fv], [type]) | Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.An annuity is a series of constant cash payments made over a continuous period. For example, a car loan or a mortgage is an annuity. For more information, refer to the description of each annuity function.In annuity functions, cash you pay out, such as a deposit to savings, is represented by a negative number; cash you receive, such as a dividend check, is represented by a positive number. For example, a $1000 deposit to the bank would be represented by the parameter –1000 if you are the depositor and by the parameter 1000 if you are the bank. |
Use this set of functions to identify the current date, extract a part
of the date, convert the DateTime values to other data types, and
perform calculations with the dates.
| Mnemonics | Operand Type | Example | Explanation |
| DATE | Three operands defining the year, month and day. | =DATE(1900,1,1) | Calculates the serial number that represents a specified date. |
| DATEVALUE | The date_text parameter specifies a date or reference to a cell that contains text representing a date in the Microsoft Excel® date format. For example, “1/30/2008” or “30-Jan-2008” are text strings within quotation marks that specify dates. | =DATEVALUE(date_text) | The DATEVALUE function converts a date that is stored as text to a serial number that the Spreadsheet control recognizes as a date.Like Microsoft Excel®, the Spreadsheet control stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is a serial number 1, and January 1, 2008 is serial number 39448 because it is 39447 days after January 1, 1900.Most functions automatically convert date values to serial numbers.The DATEVALUE function returns the #VALUE! error value if the value passed as the date_text parameter falls outside the range of dates between January 1, 1900 and December 31, 9999. |
| DAY | One parameter defining the serial number of the required date. | =DAY(TODAY()) | Returns the day portion of a given date. |
| DAYS | Two mandatory parameters: start_date and end_date are two dates between which you want to know the number of days. | =DAYS(end_date, start_date) | Returns the number of days between two dates.If both date parameters are numbers, DAYS uses EndDate-StartDate to calculate the number of days in between both dates.If either one of the date parameters is text, that parameter is treated as DATEVALUE(date_text) and returns an integer value instead of a time component.If date parameters are numeric values that fall outside the range of valid dates, DAYS returns the #NUM! error value.If date parameters are strings that cannot be parsed as valid dates, DAYS returns the #VALUE! error value. |
| DAYS360 | Two mandatory parameters: start_date and end_date are two dates between which you want to know the number of days. If start_date occurs after end_date, the DAYS360 returns a negative number.Additionally, you can specify if the ExpressSpreadSheet should use the European method instead of the U.S. method in the calculation. | =DAYS360(start_date, end_date, [method]) | The DAYS360 function returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help calculate payments if your accounting system is based on twelve 30-day months.Like Excel, the ExpressSpreadSheet stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is a serial number 1, and January 1, 2008 is serial number 39448 because it is 39447 days after January 1, 1900. |
| EDATE | Two mandatory parameters: start_date specifies the start date, and months specifies the number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date. | =EDATE(start_date, months) | Returns the serial number of the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.Like Excel, the ExpressSpreadSheet stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is a serial number 1, and January 1, 2008 is serial number 39448 because it is 39447 days after January 1, 1900. |
| EOMONTH | Two mandatory parameters: start_date (specifies the start date), and months (specifies the number of months before or after start_date). A positive value for months yields a future date; a negative value yields a past date. | =EOMONTH(start_date, months) | Returns the serial number of the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month. |
| HOUR | One parameter defining the serial number of the required date/time value. | =HOUR(NOW()) | Returns the hour portion of a given date/time value. |
| ISOWEEKNUM | The date parameter is the date-time code used by the ExpressSpreadSheet for date and time calculations. | =ISOWEEKNUM(date) | Returns the number of ISO week number of the year for a given date. |
| MONTH | One parameter defining the serial number of the required date. | =MONTH(TODAY()) | Returns the month portion of a given date. |
| MINUTE | One parameter defining the serial number of the required date/time value. | =MINUTE(NOW()) | Returns the minutes portion of a given date/time value. |
| NOW | No parameters required. | =NOW() | Returns the current time in general format. You can apply further formatting to the result of the function. |
| SECOND | One parameter defining the serial number of the required date/time value. | =SECOND(NOW()) | Returns the seconds portion of a given date/time value. |
| TIME | Three parameters defining hour, minute and second parts of a time value. | =TIME(16, 48, 10) | Returns a decimal number for a specified time. |
| TIMEVALUE | The time_text parameter is a text string specifies a time in any one of the Microsoft Excel® time formats; for example, “6:45 PM” and “18:45” text strings within quotation marks that specify time. | =TIMEVALUE(time_text) | Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 PM).Date information in time_text is ignored.Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day). |
| TODAY | No parameters required. | =TODAY() | Returns the serial number of the current date. |
| WEEKDAY | Two parameters: the serial number of the required date, weekday base. | =WEEKDAY(TODAY()) =WEEKDAY(DATE( 2002, 12, 1),1) returns 1(Sunday) | Returns the day of the week corresponding to the specified date.The weekday base identifies the first day of the week and determines the return value type:1 or omitted: the first day of the week is Sunday. The function returns 1 for Sunday, 2 for Monday, etc.2: the first day of the week is Monday. The function returns 1 for Monday, 2 for Tuesday, etc.3: the first day of the week is Monday. The function returns 0 for Monday, 1 for Tuesday, etc. |
| WEEKNUM | The serial_number parameter specifies a date within the week. Dates should be entered by using the DATE function, or as results of other formulas or functions.Additionally, you can specify the return_type parameter that determines on which day the week begins (the default value is 1). | =WEEKNUM(serial_number, [return_type]) | Returns the week number of a specific date. For example, the week containing January 1 is the first week of the year, and is numbered week 1. |
| YEAR | One parameter defining the serial number of the required date. | =YEAR(TODAY()) | Returns the year portion of a given date. |
| YEARFRAC | Two mandatory parameters include start_date and end_date that represent start date and end date, respectively.Additionally, you can specify which type of the day count basis to use. | =YEARFRAC(start_date, end_date, [basis]) | Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and end_date parameters). Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term. |
The text functions allow you to work with text strings.
| Mnemonics | Operand Type | Example | Explanation |
| & (ampersand) | Two parameters of type string | = “Yellow ”&”river” | Concatenates specified strings. |
| CHAR | The number parameter allows you to specify a number between 1 and 255 representing the required character. The character is from the character set used by your computer. | =CHAR(number) | Returns the character specified y a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters. |
| CLEAN | The text parameter specifies any worksheet information from which you want to remove non-printable characters. | =CLEAN(text) | Removes all non-printable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed.The CLEAN function was designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove these additional nonprinting characters. |
| CODE | The text parameter specifies the text for which you want the code of the first character. | =CODE(text) | Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer. |
| CONCATENATE | A list of strings | =CONCATENATE(“Developer”, “ Express”) | Joins several text strings in one text string. An alternative to “&”. |
| DOLLAR | Two parameters defining the value and the number of digits to the right of the decimal point in the output string. | =DOLLAR(957.344, 2) | Converts the number to text using currency format #,##0.00), with the decimals rounded to the specified number of places. |
| EXACT | The text1 and text2 parameters specify the first and second text strings. | =EXACT(text1, text2) | Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. Use EXACT to test text being entered into a document. |
| FIND | The find_text parameter specifies the text you want to find, the within_text parameter specifies the source text for the text fragment you want to find.Additionally, you can specify the character at which to start the search as the start_num parameter. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1. | =FIND(find_text, within_text, [start_num]) | FIND locates one text string within a second text string, and returns the number of the starting position of the first text string from the first character of the second text string. FIND always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is. |
| FIXED | The first parameter of type double is required. The second parameter of type integer is optional (the default value is 2). The third parameter of type Boolean is optional. | =FIXED(1234.567, 1) | Rounds the first parameter to the number of decimals determined by the second parameter and returns it as a string. The third parameter specifies whether to omit commas in the output string. |
| LEFT | The first parameter of type string is required. The second parameter of type integer is optional. | =LEFT(A1)=LEFT(A1, 3) | Returns the first character or characters in a text string. The second parameter defines the number of characters to extract. The default value is 1. |
| LEN | One parameter of type string. | =LEN(“ABC”) | Returns the length of a given string. |
| LOWER | One parameter of type string. | =LOWER(A1) | Converts a string to lowercase. |
| MID | The first parameter is of type string, the second and the third parameters are of type integer. | =MID(“ABC”,3,1) | Returns the substring of a given text string. The position of the substring is defined by the second parameter. The third parameter specifies the number of characters to extract. |
| PROPER | The text parameter of the string type is mandatory. Text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text you want to partially capitalize. | =PROPER(text) | Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters. |
| REPLACE | Four mandatory parameters: the text in which you want to replace some characters, the position of the character in old_text that you want to replace with new_text, the number of characters in old_text that you want REPLACE to replace with new_text, and the text that will replace a specified number of characters in old_text. | =REPLACE(old_text, start_num, num_chars, new_text) | Replaces part of a text string, based on the number of characters you specify, with a different text string.REPLACE always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is. |
| REPT | Two obligatory parameters: the text you want to repeat, and the positive number of times to repeat text. | =REPT(text, number_times) | Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.If number_times is 0 (zero), REPT returns “” (empty string).If number_times is not an integer, it is truncated.The result of the REPT function cannot be longer than 32767 characters, otherwise REPT returns the #VALUE! error value. |
| RIGHT | The first parameter of type string is required. The second parameter of type integer is optional. | =RIGHT(B2, 3) | Returns the last character or characters in a text string. The second parameter defines the number of characters to extract. The default value is 1. |
| SEARCH | Two obligatory parameters of string type. | =SEARCH(“n”, “printer”) | The SEARCH function locates one text string with a second text string, and returns the number of the starting position of the first text string from the first character of the second text string. For example, to find the position of the letter “n” in the word “printer”, you can use this function as =SEARCH(“n”, “printer”). |
| SUBSTITUTE | Three obligatory parameters of string type plus an optional instance_num parameter that allows you to specify what instance of the old text should be replaced. | =SUBSTITUTE(text, old_text, new_text, [instance_num]) | Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string. |
| T | One obligatory parameter of any type. | =T(value) | Returns the text referred by value. |
| TEXT | Two obligatory parameters: first is a numeric value or a reference to a cell containing a numeric value; second is a numeric format as a text string enclosed in quotation marks, for example “m/d/yyyy” or “#,##0.00”. | =TEXT(A1, “$0.00”) | The TEXT function converts a numeric value to text and allows you to specify the display formatting by using special format strings. This function is useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols. For example, suppose cell A1 contains the number 23.5. To format the number as a dollar amount, you can use the following formula: =TEXT(A1, “$0.00”) |
| TRIM | One parameter of string type | =TRIM(" Express Spread Sheet ") | Removes all spaces from text except for single spaces between words. |
| TRUNC | Two parameters: the number that is truncated, and the optional num_digits parameter that specifies the precision of the trunctaion. The default value for num_digits is 0 (zero). | =TRUNC(number, [num_digits]) | Truncates a number to an integer by removing the fractional part of the number. |
| UPPER | One parameter of type string. | =UPPER(A1&A2) | Converts a string to uppercase. |
| VALUE | One obligatory parameter of string type. | =VALUE(text) | Converts a text string that represents a number to a member.Text can be in any of the constant number, date, or time formats recognized by the ExpressSpreadSheet. If text is not in one of these formats, VALUE returns the #VALUE! error value. You do not generally need to use the VALUE function in a formula because the ExpressSpreadSheet automatically converts text to numbers as necessary. This function is provided for compatibility with other spreadsheet applications. |
Functions which test the type of a value and return a Boolean result.
| Mnemonics | Operand Type | Example | Explanation |
| ISBLANK | A value of any type. | =ISBLANK(A1) | Returns TRUE if cell is empty. |
| ISERR | A value of any type. | =ISERR(H1) | Returns TRUE if the cell contains any error value except #N/A. |
| ISEVEN | A value of any type. | =ISEVEN(B1) | Returns TRUE if the number is even |
| ISERROR | A value of any type. | =ISERROR(A7) | Returns TRUE if the cell contains any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!). |
| ISLOGICAL | A value of any type. | =ISLOGICAL(C3) | Returns TRUE if the specified value refers to a logical value. |
| ISNA | A value of any type. | =ISNA(E5) | Returns TRUE if the cell contains #N/A (value not available) error value. |
| ISNONTEXT | A value of any type. | =ISNONTEXT(D4) | Returns TRUE if the cell does not contain text. Returns TRUE for blank cells. |
| ISNUMBER | A value of any type. | =ISNUMBER(A2) | Returns TRUE if the cell contains a number. |
| ISTEXT | A value of any type. | =ISTEXT(D1) | Returns TRUE if the specified cell contains text. |
| ISODD | A value of any type. | =ISODD(A2) | Returns TRUE if the number is odd. |
| N | A value of any type. | =N(F7) | Returns a value converted to a number. |
| NA | A value of any type. | =NA(B2) | Returns the error value #N/A. |
Copyright (c) 2001-2017 Developer Express Inc. All rights reserved.