Built-in Functions: Statistical
- 61 minutes to read
The ExpressSpreadSheet function repository automatically registers all function signatures listed in the table below. Each signature has a corresponding function in Microsoft Excel® under the “Statistical“ category. These functions are designed to perform most of the common statistical operations, from basic mean and mode calculations to more complex statistical distribution and probability tests.
All function signatures marked as “Implemented”, are ready for use in formula expressions. The ExpressSpreadSheet formula engine uses “unimplemented” function signatures only to ensure compatibility with Microsoft Excel® spreadsheet documents that can contain the corresponding functions. You can add implementations to such “empty” signatures and override existing implementations as the “How to Create Custom Functions“ topic describes.
The Syntax column lists each optional function parameter in square brackets, and an ellipsis indicates an open-ended parameter list.
align=”center” colspan=”9” ! | ||||||||
|---|---|---|---|---|---|---|---|---|
No. | Function Name | Implemented | Available in Container Controls | XLS Token | Parameters | Syntax | Description | |
colspan=”9” | | ||||||||
1. |
AVEDEV | Yes | 255 | One mandatory parameter. The function accepts a numeric value, array, or cell reference as the Number1 parameter. Up to 254 optional numeric parameters or arrays of numbers. The AVEDEV function combines all specified values and arrays into a single array and uses it as a data source. The combined source array’s size is unlimited. The function can also accept defined names that store numeric constants or refer to the arrays of numeric values. | =AVEDEV(Number1, [Number2], [Number3], …) | Returns the average of the absolute deviations of all numeric values in the source array from their mean. This function is designed to evaluate the variability of a series of numbers. The AVEDEV function treats TRUE and FALSE values as 1 and 0, respectively, and can recognize a numeric value in a text string (“11” or “0”, for example). The AVEDEV function returns the #N/A or #NAME? error code if it accepts no parameters or a text string as a parameter, respectively. To localize the AVEDEV function’s name and short description, use the sfnAveDev and sfnAveDevDescription resource strings. | ||
2. |
AVERAGE | Yes | 255 | One mandatory parameter. The function accepts a numeric value, array, or cell reference as the Number1 parameter. Up to 254 optional numeric parameters or arrays of numbers. The AVERAGE function combines all specified values and arrays into a single array and uses it as a data source. The combined source array’s size is unlimited. The function can also accept defined names that store numeric constants or refer to the arrays of numeric values. | =AVERAGE(Number1, [Number2], [Number3], …) | Calculates the arithmetic mean (average) of all numeric values in the source array. To calculate the average of only those numeric values that meet specific criteria, use the AVERAGEIF or AVERAGEIFS function instead. The AVERAGE function treats TRUE and FALSE values as 1 and 0, respectively, and can recognize a numeric value in a text string (“11” or “0”, for example). This function omits numeric values as text strings and Boolean values in cells and cell ranges that correspond to references passed as parameters. Unlike AVERAGE, the AVERAGEA function takes such cells into account. The AVERAGE function returns the #N/A or #NAME? error code if it accepts no parameters or a text string, respectively. To localize the AVERAGE function’s name and short description, use the sfnAverage and sfnAverageDescription resource strings. | ||
3. |
AVERAGEA | Yes | 255 | One mandatory parameter: The function accepts a numeric value, array, or cell reference as the Number1 parameter. Up to 254 optional numeric parameters or arrays of numbers. The AVERAGEA function combines all specified values and arrays into a single source array and uses it as a data source. The combined source array’s size is unlimited. The function can also accept defined names that store numeric constants or refer to the arrays of numeric values. | =AVERAGEA(Number1, [Number2], [Number3], …) | Calculates the arithmetic mean (average) of all specified parameters. The AVERAGEA function treats TRUE and FALSE values as 1 and 0, and recognizes numeric values stored in text strings (“11” or “0”, for example) in cell/cell range references, and parameter values separated by commas. Additionally, this function treats empty cells and cells that contain an empty string as zeros. Unlike AVERAGEA, the AVERAGE function ignores such cells and cells that contain Boolean values. The AVERAGEA function returns the #N/A or #NAME? error code if it accepts no parameters or a text string that this function cannot interpret as a numeric value. To localize the AVERAGEA function’s name and short description, use the sfnAverageA and sfnAverageADescription resource strings. | ||
4. |
AVERAGEIF | Yes | 255 | 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, [AverageRange]) | 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 AverageRange 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 AverageRange 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 AverageRange as the beginning cell, and then including cells that correspond in size and shape to range. | ||
5. |
AVERAGEIFS | Yes | 255 | 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. | ||
6. |
BETA.DIST | Yes | 255 | 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. | ||
7. |
BETA.INV | Yes | 255 | 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. | ||
8. |
BINOM.DIST | Yes | 255 | 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. | ||
9. |
BINOM.DIST.RANGE | Yes | 255 | 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:
If any parameters are non-numeric values, BINOM.DIST.RANGE returns the #VALUE! error value. Numeric parameters are truncated to integers. | ||
10. |
BINOM.INV | No | 255 | =BINOM.INV(Trials,ProbabilityS,Alpha) | colspan=”2” | | |||
11. |
CHISQ.DIST | No | 282 | 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. | ||
12. |
CHISQ.DIST.RT | No | 310 | 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. | ||
13. |
CHISQ.INV | Yes | 286 | 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. | ||
colspan=”8” | | colspan=”2” | | rowspan=”75” | | rowspan=”75” | | |||||
14. |
CHISQ.INV.RT | Yes | 287 | 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. | ||
15. |
CHISQ.TEST | No | 289 | =CHISQ.TEST(ActualRange, ExpectedRange) | colspan=”2” | | |||
16. |
CONFIDENCE.NORM | No | 291 | =CONFIDENCE.NORM(Alpha, StandardDeviation, Size) | colspan=”2” | | |||
17. |
CONFIDENCE.T | No | 290 | =CONFIDENCE.T(Alpha, StandardDeviation, Size) | colspan=”2” | | |||
18. |
CORREL | Yes | 330 | 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. | ||
19. |
COUNT | Yes | 292 | A list of parameters | =COUNT(Reference) | Returns the number of cells in a given range. | ||
20. |
COUNTA | Yes | 293 | A list of parameters | =COUNTA(Reference) | Counts the number of non-empty cells in a given range. | ||
21. |
COUNTBLANK | Yes | 295 | A list of parameters | =COUNTBLANK(Reference) | Counts the number of empty cells. | ||
22. |
COUNTIF | Yes | 294 | 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. | ||
23. |
COUNTIFS | Yes | 296 | 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. | ||
24. |
COVARIANCE.P | Yes | 328 | 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. | ||
25. |
COVARIANCE.S | No | 329 | 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. | ||
26. |
DEVSQ | Yes | 300 | 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. | ||
27. |
EXPON.DIST | Yes | 327 | 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. | ||
28. |
F.DIST | No | 216 | =F.DIST(X, DegFreedom1, DegFreedom2, Cumulative) | colspan=”2” | | |||
29. |
F.DIST.RT | No | 12 | =F.DIST.RT(X, DegFreedom1, DegFreedom2) | colspan=”2” | | |||
30. |
F.INV | No | 193 | =F.INV(Probability, DegFreedom1, DegFreedom2) | colspan=”2” | | |||
31. |
F.INV.RT | No | 301 | =F.INV.RT(X, DegFreedom1, DegFreedom2) | colspan=”2” | | |||
32. |
F.TEST | No | 332 | =F.TEST(Array1, Array2) | colspan=”2” | | |||
33. |
FISHER | No | 316 | =FISHER(X) | colspan=”2” | | |||
34. |
FISHERINV | No | 46 | =FISHERINV(Y) | colspan=”2” | | |||
35. |
FORECAST | Yes | 194 | 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. | ||
36. |
FREQUENCY | No | 302 | =FREQUENCY(SourceArray, BinsArray) | colspan=”2” | | |||
37. |
GAMMA | No | 324 | 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. | ||
38. |
GAMMA.DIST | Yes | 255 | 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. | ||
39. |
GAMMA.INV | Yes | 255 | 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. | ||
40. |
GAMMALN | Yes | 271 | 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)!. | ||
41. |
GAMMALN.PRECISE | Yes | 255 | 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)!. | ||
42. |
GAUSS | Yes | 255 | 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). | ||
43. |
GEOMEAN | Yes | 319 | 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. | ||
44. |
GROWTH | No | 52 | =GROWTH(SourceYSet, [SourceXSet], [NewXSet], [Const]) | colspan=”2” | | |||
45. |
HARMEAN | No | 320 | =HARMEAN(Number1, [Number2], [Number3], …) | colspan=”2” | | |||
46. |
HYPGEOM.DIST | Yes | 255 | 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. | ||
47. |
INTERCEPT | Yes | 311 | 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. | ||
48. |
KURT | No | 322 | =KURT(Number1, [Number2], [Number3], …) | colspan=”2” | | |||
49. |
LARGE | Yes | 325 | 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. | ||
50. |
LINEST | No | 49 | =LINEST(ValuesY, [ValuesX], [Const], [Stats]) | colspan=”2” | | |||
51. |
LOGEST | No | 51 | =LOGEST(ValuesY, [ValuesX], [Const], [Stats]) | colspan=”2” | | |||
52. |
LOGNORM.DIST | No | 255 | =LOGNORM.DIST(X, Mean, StandardDeviation, Cumulative) | colspan=”2” | | |||
53. |
LOGNORM.INV | No | 255 | =LOGNORM.INV(Probability, Mean, StandardDeviation) | colspan=”2” | | |||
54. |
MAX | Yes | 7 | One mandatory parameter: The function accepts a numeric value, array, or cell reference passed as the Number1 parameter. Up to 254 optional numeric parameters or arrays of numbers. The MAX function combines all specified values and arrays into a single source array and returns the highest value in it. The combined source array’s size is unlimited. The function can also accept defined names that store numeric constants or refer to the arrays of numeric values. | =MAX(Number1, [Number2], [Number3], …) | Returns the highest value in the source array. The MAX function returns 0 if there are no numeric values in the source array. Use the MAXA function instead if you need to interpret logical values and text as numbers. The MAX function returns the NAME? error code if an accepted parameter is not a numeric value. To localize the MAX function’s name and short description, use the sfnMax and sfnMaxDescription resource strings. | ||
55. |
MAXA | Yes | 362 | One mandatory parameter. The function accepts a numeric value, array, or cell reference passed as the Number1 parameter. Up to 254 optional numeric parameters or arrays of numbers. The MAXA function combines all specified values and arrays into a single source array and returns the highest value in it. The combined source array’s size is unlimited. The function can also accept defined names that store numeric constants or refer to the arrays of numeric values. | =MAXA(Number1, [Number2], [Number3], …) | Returns the highest value in the source array. The MAXA function works similar to MAX, but takes text strings and logical values into account. The MAXA function returns the NAME? error code if an accepted parameter is not a numeric value. To localize the MAXA function’s name and short description, use the sfnMaxA and sfnMaxADescription resource strings. | ||
56. |
MEDIAN | Yes | 227 | One mandatory parameter. The function accepts a numeric value, array, or cell reference passed as the Number1 parameter. Up to 254 optional numeric parameters or arrays of numbers. The MEDIAN function combines all specified values and arrays into a single source array and uses it as a data source. The combined source array’s size is unlimited. The function can also accept defined names that store numeric constants or refer to the arrays of numeric values. | =MEDIAN(Number1, [Number2], [Number3], …) | Returns the median of all numeric values in the source array. The MEDIAN function returns the NAME? error code if an accepted parameter is not a numeric value. To localize the MEDIAN function’s name and short description, use the sfnMedian and sfnMedianDescription resource strings. | ||
57. |
MIN | Yes | 6 | One mandatory parameter: The function accepts a numeric value, array, or cell reference passed as the Number1 parameter. Up to 254 optional numeric parameters or arrays of numbers. The MIN function combines all specified values and arrays into a single array and returns the lowest value in it. The combined source array’s size is unlimited. The function can also accept defined names that store numeric constants or refer to the arrays of numeric values. | =MIN(Number1, [Number2], [Number3], …) | Returns the lowest value in the source array. The MIN function returns 0 if there are no numeric values in the source array. Use the MINA function instead if you need to interpret logical values and text as numbers. The MIN function returns the NAME? error code if an accepted parameter is not a numeric value. To localize the MIN function’s name and short description, use the sfnMin and sfnMinDescription resource strings. | ||
58. |
MINA | Yes | 363 | One mandatory parameter. The function accepts a numeric value, array, or reference as the Number1 parameter. Up to 254 optional numeric parameters, arrays, or cell references. The MINA function combines all specified values and arrays into a single source array and returns the lowest value in it. The combined source array’s size is unlimited. The function can also accept defined names that store numeric constants or refer to the arrays of numeric values. | =MINA(Number1, [Number2], [Number3], …) | Returns the lowest value in the source array. The MINA function works similar to MIN, but takes text strings and logical values into account. The MINA function returns the NAME? error code if an accepted parameter is not a numeric value. To localize the MINA function’s name and short description, use the sfnMinA and sfnMinADescription resource strings. | ||
59. |
MODE.MULT | Yes | 255 | One mandatory parameter. The function accepts a numeric value, array, or reference as the Number1 parameter. Up to 254 optional numeric parameters, arrays, or cell references. The MODE.MULT function combines all specified values and arrays into a single source array and returns one or more of the most repetitive values in it. The combined source array’s size is unlimited. Note that the function ignores text, logical values, and empty cells in references to cell ranges passed as parameters. The function can also accept defined names that store numeric constants or refer to the arrays of numeric values. | =MODE.MULT(Number1, [Number2], [Number3], …) | Returns a column populated with the most repetitive values in the source array. The function returns more than a single result only if the source array has more than one value with the same highest number of occurrences. For example, the “=MODE.MULT(3, 3, 4, 4, 10, 5, 5, 5, 10, 10)” expression returns a two-cell column populated with 10 and 5. That is, the MODE.MULT returns only the values that occur three times in the source array, and ignores the 3 and 4 that both occur twice. Note that you can pass the MODE.MULT function’s result as the TRANSPOSE function parameter to get a row of results instead of a column. The MODE.MULT function returns the #N/A error code if the source array has no duplicate values. The MODE.MULT and MODE.SNGL functions replace the MODE deprecated (compatibility) function. To localize the MODE.MULT function’s name and short description, use the sfnMode_Mult and sfnMode_MultDescription resource strings. | ||
60. |
MODE.SNGL | Yes | 255 | One mandatory parameter: A numeric value, array, or cell reference passed as the Number1 parameter. Up to 254 optional numeric parameters or arrays of numbers. The MODE.SNGL function combines all specified values and arrays into a single array and returns the most repetitive value in it. The combined source array’s size is unlimited. The function can also accept defined names that store numeric constants or refer to the arrays of numeric values. | =MODE.SNGL(Number1, [Number2], [Number3], …) | Calculates the mode of a series of numbers, that is, returns the most repetitive value in the source array. For example, the “=MODE.SNGL(3, 3, 4, 4, 10, 5, 5, 5, 10, 10)” formula expression returns 10. The MODE.SNGL function can return only a single result. Use the MODE.MULT function instead if you need to return multiple most repetitive values among the source value series or arrays. The MODE.SNGL function returns #N/A error code if the provided series has no duplicate values. The MODE.SNGL and MODE.MULT functions replace the MODE deprecated (compatibility) function. To localize the MODE.SNGL function’s name and short description, use the sfnMode_SNGL and sfnMode_SNGLDescription resource strings. | ||
61. |
NEGBINOM.DIST | No | 255 | Four mandatory parameters:
| =NEGBINOM.DIST(NumberF, NumberS, ProbabilityS, Cumulative) | Returns the negative binomial distribution, that is, the probability that there is a specific number of failures before a threshold number of successful trials at the constant probability of an independent trial’s success. Pass TRUE or FALSE as the Cumulative parameter to switch between the cumulative distribution function and probability density function calculation modes. This function works similar to the BINOM.DIST function. Unlike it, NEGBINOM.DIST accepts the number of successful trials instead of the total trial count. The NEGBINOM.DIST function returns:
NEGBINOM.DIST replaces the NEGBINOMDIST deprecated (compatibility) function. To localize the NEGBINOM.DIST function’s name and short description, use the sfnNegBinom_Dist and sfnNegBinom_DistDescription resource strings. | ||
62. |
NORM.DIST | Yes | 255 | Four mandatory parameters:
| =NORM.DIST(X, Mean, StandardDeviation, Cumulative) | Calculates the normal distribution for the specified mean and standard deviation. This function has widespread use in statistics, including hypothesis tests. Pass TRUE or FALSE as the Cumulative parameter to switch between the cumulative distribution and probability mass function calculation modes. If the evaluated distribution is a standard normal distribution (that is, the mean is zero, and the standard deviation equals 1), you can use the NORM.S.DIST function instead. You can use the NORM.INV function to perform the reverse operation (that is, to calculate the inverse of the normal distribution for the specified mean and standard deviation). The NORM.DIST function returns:
NORM.DIST replaces the NORMDIST deprecated (compatibility) function. To localize the NORM.DIST function’s name and short description, use the sfnNorm_Dist and sfnNorm_DistDescription resource strings. | ||
63. |
NORM.INV | Yes | 255 | Three mandatory parameters:
| =NORM.INV(Probability, Mean, StandardDeviation) | Calculates the inverse of the normal cumulative distribution for the specified mean and standard deviation. The NORM.INV function calculates the inverse of the standard normal cumulative distribution (that is, works identically to the NORM.S.INV function) if you pass 0 and 1 as the Mean and StandardDeviation parameters, respectively. You can use the NORM.DIST function to perform the reverse operation (that is, to calculate the probability associated with the normal distribution). The NORM.INV function returns:
The NORM.INV replaces the NORMINV deprecated (compatibility) function in the Compatibility category. To localize the NORM.INV function’s name and short description, use the sfnNorm_Inv and sfnNorm_InvDescription resource strings. | ||
64. |
NORM.S.DIST | Yes | 255 | Two mandatory parameters:
| =NORM.S.DIST(X, Cumulative) | Calculates the standard normal distribution (the normal distribution whose mean is zero, and the standard deviation equals 1). Pass TRUE or FALSE as the Cumulative parameter to switch between the cumulative distribution and probability mass function calculation modes. Use the NORM.DIST function to calculate a normal distribution. You can use the NORM.S.INV function to perform the reverse operation (that is, to calculate the inverse of the standard normal cumulative distribution). The NORM.S.DIST function returns the #VALUE! error code if the X parameter is not a numeric value. To localize the NORM.S.DIST function’s name and short description, use the sfnNorm_S_Dist and sfnNorm_S_DistDescription resource strings. | ||
65. |
NORM.S.INV | Yes | 255 | The only mandatory parameter is Probability that accepts a probability that corresponds to the standard normal distribution. This value must range from 0 to 1. | =NORM.S.INV(Probability) | Iteratively calculates the inverse of the standard normal distribution (the normal distribution whose mean is zero, and the standard deviation equals 1). Use the NORM.INV function to calculate the inverse of any normal distribution. You can use the NORM.S.DIST function to perform the reverse operation (that is, to calculate the probability associated with the standard normal distribution). The NORM.S.INV function returns:
To localize the NORM.S.INV function’s name and short description, use the sfnNorm_S_Inv and sfnNorm_S_InvDescription resource strings. | ||
66. |
PEARSON | Yes | 312 | Two mandatory parameters:
| =PEARSON(IndependentArray, DependentArray) | Calculates the Pearson product moment correlation coefficient (R). This coefficient is a dimensionless index that ranges from -1.0 to 1.0 and reflects the extent of a linear relationship between two same-sized arrays. The PEARSON function returns the #N/A error code if the two specified arrays are not identical in size. The function ignores referred empty cells, Boolean values, and text. To localize the PEARSON function’s name and short description, use the sfnPearson and sfnPearsonDescription resource strings. | ||
67. |
PERCENTILE.EXC | Yes | 255 | Two mandatory parameters:
| =PERCENTILE.EXC(Array, K) | Returns the K-th percentile of values in a range. The PERCENTILE.EXC function returns:
To localize the PERCENTILE.EXC function’s name and short description, use the sfnPercentile_Exc and sfnPercentile_ExcDescription resource strings. | ||
68. |
PERCENTILE.INC | Yes | 255 | 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. | ||
69. |
PERCENTRANK.EXC | No | 255 | =PERCENTRANK.EXC(Array, X, [Significance]) | colspan=”2” | | |||
70. |
PERCENTRANK.INC | No | 255 | =PERCENTRANK.INC(Array, X, [Significance]) | colspan=”2” | | |||
71. |
PERMUT | Yes | 299 | Two mandatory parameters:
| =PERMUT(TotalQuantity, SubsetSize) | Returns the number of unique permutations for a specific subset of objects that you can select from the full set. Like combinations, permutations are sets or subsets of objects or events. Unlike combinations, the internal order is significant for permutations. The PERMUT function is useful for lottery-related probability calculations. Use the PERMUTATIONA function if you need to take all repeating permutations into account. If you pass a floating-point value as a parameter, the PERMUT function truncates it. The PERMUT function returns:
To localize the PERMUT function’s name and short description, use the sfnPermut and sfnPermutDescription resource strings. | ||
72. |
PERMUTATIONA | No | 255 | Two mandatory parameters:
| =PERMUTATIONA(TotalQuantity, SubsetSize) | Returns the number of permutations for a specific subset of objects (with repetitions) that you can select from the full set. This function works similar to PERMUT, but includes repeating permutations into the result. If you pass a floating-point value as a parameter, the PERMUTATIONA function truncates it. The PERMUTATIONA function returns:
To localize the PERMUTATIONA function’s name and short description, use the sfnPermutationA and sfnPermutationADescription resource strings | ||
73. |
PHI | No | 255 | The only mandatory parameter is X that accepts a value of the evaluated function (a position on the X-axis for which the PHI function calculates the density for a standard normal distribution. | =PHI(X) | Calculates the density function result for a standard normal distribution. The PHI function returns the #VALUE! or #NUM! error code if the accepted parameter is not a numeric value or not within the valid range, respectively. To localize the PHI function’s name and short description, use the sfnPHI and sfnPHIDescription resource strings. | ||
74. |
POISSON.DIST | Yes | 255 | Three mandatory parameters:
| =POISSON(X, Mean, Cumulative) | Calculates the Poisson distribution that is often used to predict the number of events that occur over a specific time. Pass TRUE or FALSE as the Cumulative parameter to switch between the cumulative Poisson distribution and Poisson probability mass function calculation modes. The POISSON.DIST function returns the #VALUE! or #NUM! error code if the X or Mean parameter value is not numeric or negative, respectively. If either parameter is a positive floating-point value, the function truncates it. POISSON.DIST replaces the POISSON deprecated (compatibility) function. To localize the POISSON.DIST function’s name and short description, use the sfnPoisson_Dist and sfnPoisson_DistDescription resource strings. | ||
75. |
PROB | No | 317 | =PROB(XRange, ProbRange, [LowerLimit], [UpperLimit]) | colspan=”2” | | |||
76. |
QUARTILE.EXC | Yes | 255 | 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. | ||
77. |
QUARTILE.INC | Yes | 255 | 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. | ||
78. |
RANK.AVG | Yes | 255 | 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. | ||
79. |
RANK.EQ | Yes | 255 | 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. | ||
80. |
RSQ | Yes | 313 | 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. | ||
81. |
SKEW | Yes | 323 | 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. | ||
82. |
SKEW.P | Yes | 255 | 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. | ||
83. |
SLOPE | Yes | 315 | 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. | ||
84. |
SMALL | Yes | 326 | 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. | ||
85. |
STANDARDIZE | Yes | 297 | =STANDARDIZE(X, Mean, StandardDeviation) | colspan=”2” | | |||
86.. |
STDEV.P | Yes | 255 | 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. | ||
87 |
STDEV.S | Yes | 255 | 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. | ||
colspan=”8” | | colspan=”2” | | rowspan=”18” | | rowspan=”18” | | |||||
88. |
STDEVA | Yes | 366 | 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. | ||
89. |
STDEVPA | Yes | 364 | 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. | ||
90. |
STEYX | Yes | 314 | 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. | ||
91. |
T.DIST | Yes | 255 | 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. | ||
92. |
T.DIST.2T | Yes | 255 | 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. | ||
93. |
T.DIST.RT | Yes | 255 | 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. | ||
94. |
T.INV | Yes | 255 | 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. | ||
95. |
T.INV.2T | Yes | 255 | 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. | ||
96. |
T.TEST | No | 255 | =T.TEST(Array1, Array2, Tails, Type) | colspan=”2” | | |||
97. |
TREND | No | 50 | =TREND(ValuesY, [ValuesX], [NewValuesX], [Const]) | colspan=”2” | | |||
98. |
TRIMMEAN | No | 331 | =TRIMMEAN(Array, Percent) | colspan=”2” | | |||
99. |
VAR.P | Yes | 255 | 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. | ||
100. |
VAR.S | Yes | 255 | 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. | ||
101. |
VARA | Yes | 367 | 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. | ||
102. |
VARPA | Yes | 365 | 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. | ||
103. |
WEIBULL.DIST | Yes | 255 | Four mandatory parameters:
| =WEIBULL(X, Alpha, Beta, Cumulative) | Returns the Weibull distribution used in reliability analysis. You can use this function to calculate a device’s MTBF (mean time between failures). Pass TRUE or FALSE as the Cumulative parameter to switch between the Weibull cumulative distribution and probability density function calculation modes. The WEIBULL.DIST function calculates the exponential distribution if the Alpha parameter value is 1. The WEIBULL.DIST function returns:
WEIBULL.DIST replaces the WEIBULL deprecated (compatibility) function. To localize the WEIBULL.DIST function’s name and short description, use the sfnWeibull_Dist and sfnWeibull_DistDescription resource strings. | ||
104. |
Z.TEST | No | 255 | Two mandatory parameters:
The optional Sigma parameter specifies the population’s known standard deviation. The ZTEST function uses the sample standard deviation instead of the Sigma parameter value if it is omitted. | =ZTEST(Array, X, [Sigma]) | Calculates the one-tailed probability associated with a Z-test that the sample mean exceeds the average of all numeric values in the source array (the observed sample mean). The ZTEST function returns the #N/A error code if the source array is empty. Z.TEST replaces the ZTEST deprecated (compatibility) function. To localize the Z.TEST function’s name and short description, use the sfnZ_Test and sfnZ_TestDescription resource strings. | ||