Framework / Formulas / Functions / Statistical Functions
In This Topic
    Statistical Functions
    In This Topic

     

    Syntax Description Example
    BETADIST(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 the fraction of the day people spend watching television.
    Arguments:
    • x - Required.The value between A and B at which to evaluate the function
    • alpha - Required.A parameter of the distribution.
    • Beta - Required. A parameter of the distribution.
    • Cumulative - Required. A logical value that determines the form of the function. If cumulative is TRUE, BETADIST returns the cumulative distribution function; if FALSE, it returns the probability density function.
    • A - Optional. A lower bound to the interval of x.
    • B - Optional. An upper bound to the interval of x.
    		 BETADIST(2,8,10,TRUE,1,3)
    Returns: 0.6854706
    BETAINV(probability,alpha,beta, [A], [B]) Returns the inverse of the beta cumulative probability density function(BETADIST). If probability = BETADIST(x, ...TRUE), then BETAINV(probability,...) = x. The beta distribution can be used in project planning to model probable completion times given an expected completion time and variability.
    Arguments:
    • Probability - Required.A probability associated with the beta distribution.
    • Alpha - Required. A parameter of the distribution.
    • Beta - Required. A parameter the distribution.
    • A - Optional. A lower bound to the interval of x.
    • B - Optional. An upper bound to the interval of x.
    		 BETAINV(0.685470581,8,10,1,3)
    Returns: 2
    BINOMDIST(number_s,trials,probability_s,cumulative) Returns the individual term binomial distribution probability. Use BINOMDIST in problems with a fixed number of tests or trials, when the outcomes 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, BINOMDIST can calculate the probability that two of the next three babies born are male.
    Arguments:
    • number_s - Required.The number of successes in trials.
    • trials - Required. The number of independent trials.
    • probability_s - Required. The probability of success on each trial.
    • cumulative - Required. A logical value that determines the form of the function. If cumulative is TRUE, then BINOMDIST 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.
    		 BINOMDIST(6, 10, 0.5, FALSE)
    Returns: 0.205
    CHISQDIST(x, deg_freedom, cumulative) Returns 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.
    Arguments:
    • x - Required.The value at which you want to evaluate the distribution.
    • deg_freedom - Required. The number of degrees of freedom.
    • cumulative - Required. A logical value that determines the form of the function. If cumulative is TRUE, CHISQDIST returns the cumulative distribution function; if FALSE, it returns the probability density function.
    		 CHISQDIST(0.5,1,TRUE)
    Returns: 0.520
    CHISQINV(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.
    Arguments:
    • probability - Required. A probability associated with the chi-squared distribution.
    • deg_freedom - Required. The number of degrees of freedom.
    		 CHISQINV(0.93,1)
    Returns: 3.283020286
    CORRELATION(array1, array2) The CORRELATION function returns the correlation coefficient of two 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.
    Arguments:
    • array1 - Required.A range of cell values.
    • array2 - Required. A second range of cell values.
    		 CORRELATION(ARRAY(3, 2, 4, 5, 6), ARRAY(9, 7, 12, 15, 17), true)
    Returns: 0.997
    COVARIANCE(array1,array2,entirePopulation) Returns the population or sample covariance. The sample covariance, is average of the products of deviations for each data point pair in two data sets. The population covariance, is 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.
    Arguments:
    • array1 - Required.The first cell range of integers.
    • array2 - Required. The second cell range of integers.
    		 COVARIANCE(ARRAY(3,2,4,5,6), ARRAY(9,7,12,15,17), true)
    Returns: 5.2

    OVARIANCE(ARRAY(2, 4, 8), ARRAY(5, 11, 12), false)
    Returns: 9.666666667
    EXPONDIST(x,lambda,cumulative) Returns the exponential distribution. Use EXPONDIST to model the time between events, such as how long an automated bank teller takes to deliver cash. For example, you can use EXPONDIST to determine the probability that the process takes at most 1 minute.
    Arguments:
    • x - Required. The value of the function.
    • lambda - Required. The parameter value.
    • cumulative - Required. A logical value that indicates which form of the exponential function to provide. If cumulative is TRUE, EXPONDIST returns the cumulative distribution function; if FALSE, it returns the probability density function.
    		 EXPONDIST(0.2,10,TRUE)
    Returns: 0.86466472
    EXPONINV(probability,lambda) Returns the inverse of the exponent probability distribution.If p = EXPONDIST(x, ...), then EXPONINV(p,...) = x.
    Arguments:
    • probability - Required. A probability associated with the exponential cumulative distribution.
    • lamba - Required. The numerator degrees of freedom.
    		 EXPONINV(0.86466472, 10)
    Returns: 0.2
    FDIST(x, degOfFreedom1, degOfFreedom2, cumulative) Returns the F probability distribution.You can use this function to determine whether two data sets have different degrees of diversity. For example, you can examine the test scores of men and women entering high school, and determine if the variability in the females is different from that found in the males.
    Arguments:
    • x - Required.The value at which to evaluate the function.
    • degOfFreedom1 - Required. The numerator degrees of freedom.
    • degOfFreedom2 - Required. The denominator degrees of freedom.
    • cumulative - Required. A logical value that determines the form of the function. If cumulative is TRUE, FDIST returns the cumulative distribution function; if FALSE, it returns the probability density function.
    		 FDIST(15.2069,6,7,TRUE)
    Returns: 0.99
    FINV(probability,deg_freedom1,deg_freedom2) Returns the inverse of the F probability distribution.If p = FDIST(x, ...), then FINV(p,...) = x. The F distribution can be used in an F-test that compares the degree of variability in two data sets.For example, you can analyze income distributions in the United States and Canada to determine whether the two countries have a similar degree of income diversity.
    Arguments:
    • probability - Required. A probability associated with the F cumulative distribution.
    • deg_freedom1 - Required. The numerator degrees of freedom.
    • deg_freedom2 - Required. The denominator degrees of freedom.
    		 FINV(0.01,6,4)
    Returns: 0.10930991
    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.
    Arguments:
    • X - Required.The value at which you want to evaluate the distribution.
    • Alpha - Required. A parameter to the distribution.
    • Beta - Required. A parameter to the distribution.If beta = 1, GAMMADIST returns the standard gamma distribution.
    • Cumulative - Required. A logical value that determines the form of the function. If cumulative is TRUE, GAMMADIST returns the cumulative distribution function;
    		 GAMMADIST(10.00001131,9,2,FALSE)
    Returns: 0.032639
    GAMMAINV(probability,alpha,beta) Returns the inverse of the gamma cumulative distribution. If p = GAMMADIST(x,...), then GAMMAINV(p,...) = x. You can use this function to study a variable whose distribution may be skewed.
    Arguments:
    Probability Required.The probability associated with the gamma distribution. Alpha Required. A parameter to the distribution. Beta Required. A parameter to the distribution. If beta = 1, GAMMAINV returns the standard gamma distribution.
    		GAMMAINV(0.068094,9,2)
    Returns:10.0000112
    HYPGEOMDIST(sample_s,number_sample,population_s,number_pop,cumulative) Returns the hypergeometric distribution.HYPGEOMDIST returns the probability of a given number of sample successes, given the sample size, population successes, and population size. Use HYPGEOMDIST 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.
    Arguments:
    • sample_s - Required.The number of successes in the sample.
    • number_sample - Required. The size of the sample.
    • population_s - Required. The number of successes in the population.
    • number_pop - Required. The population size.
    • cumulative - Required. A logical value that determines the form of the function. If cumulative is TRUE, then HYPGEOMDIST returns the cumulative distribution function; if FALSE, it returns the probability mass function.
    		 HYPGEOMDIST(1,4,8,20,TRUE)
    Returns: 0.4654

    HYPGEOMDIST(1,4,8,20,FALSE)
    Returns: 0.3633
    LINEST(known_y's, [known_x's], [const]) You can also combine LINEST with other functions to calculate the statistics for other types of models that are linear in the unknown parameters, including polynomial, logarithmic, exponential, and power series. Because this function returns an array of values, it must be entered as an array formula. Instructions follow the examples in this article. The equation for the line is:
    y = mx + b
    –or–
    y = m1x1 + m2x2 + ... + b
    if there are multiple ranges of x-values, where the dependent y-values are a function of the independent x-values. The m-values are coefficients corresponding to each x-value, and b is a constant value.Note that y, x, and m can be vectors. The array that the LINEST function returns is {mn,mn-1,...,m1,b}. LINEST can also return additional regression statistics.
    Arguments:
    • known_y's - Required. The set of y-values that you already know in the relationship y = mx + b. If the range of known_y's is in a single column, each column of known_x's is interpreted as a separate variable. If the range of known_y's is contained in a single row, each row of known_x's is interpreted as a separate variable.
    • known_x's - Optional. A set of x-values that you may already know in the relationship y = mx + b. The range of known_x's can include one or more sets of variables. If only one variable is used, known_y's and known_x's can be ranges of any shape, as long as they have equal dimensions. If more than one variable is used, known_y's must be a vector(that is, a range with a height of one row or a width of one column). If known_x's is omitted, it is assumed to be the array {1,2,3,...} that is the same size as known_y's.
    • const - Optional.A logical value specifying whether to force the constant b to equal 0.
    		 LINEST(ARRAY(1, 9, 5, 7), ARRAY(0, 4, 2, 3) ,FALSE)
    Returns: 2.31

    LINEST(ARRAY(1, 9, 5, 7), ARRAY(0, 4, 2, 3) ,TRUE)
    Returns: { 2, 1 }
    LOGEST(known_y's, [known_x's], [const]) In regression analysis, the LOGEST function calculates an exponential curve that fits your data and returns an array of values that describes the curve. Because this function returns an array of values, it must be entered as an array formula. The equation for the curve is:
    y = b* m^x
    or
    y = (b*(m1^x1)* (m2^x2)* _)
    if there are multiple x-values, where the dependent y-value is a function of the independent x-values. The m-values are bases corresponding to each exponent x-value, and b is a constant value.Note that y, x, and m can be vectors. The array that LOGEST returns is {mn,mn-1,...,m1,b}.
    Arguments:
    • known_y's - Required. The set of y-values you already know in the relationship y = b*m^x. If the array known_y's is in a single column, then each column of known_x's is interpreted as a separate variable. If the array known_y's is in a single row, then each row of known_x's is interpreted as a separate variable.
    • known_x's - Optional. An optional set of x-values that you may already know in the relationship y = b*m^x. The array known_x's can include one or more sets of variables. If only one variable is used, known_y's and known_x's can be ranges of any shape, as long as they have equal dimensions. If more than one variable is used, known_y's must be a range of cells with a height of one row or a width of one column(which is also known as a vector). If known_x's is omitted, it is assumed to be the array {1,2,3,...} that is the same size as known_y's.
    • const - Optional.A logical value specifying whether to force the constant b to equal 1. If const is TRUE or omitted, b is calculated normally. If const is FALSE, b is set equal to 1, and the m-values are fitted to y = m ^ x.
    		 LOGEST(ARRAY(33100,47300,69000,102000,150000,220000), ARRAY(11,12,13,14,15,16))
    Returns: { 1.46328, 495.305 }

    LOGEST(ARRAY(33100,47300,69000,102000,150000,220000), ARRAY(11,12,13,14,15,16), FALSE)
    Returns: { 2.300393, 1 }
    LOGNORMDIST(x,mean,standard_dev,cumulative) Returns the lognormal distribution of x, where ln(x) is normally distributed with parameters Mean and Standard_dev. Use this function to analyze data that has been logarithmically transformed.
    Arguments:
    • x - Required.The value at which to evaluate the function.
    • mean - Required. The mean of ln(x).
    • standard_dev - Required. The standard deviation of ln(x).
    • cumulative - Required. A logical value that determines the form of the function. If cumulative is TRUE, LOGNORMDIST returns the cumulative distribution function; if FALSE, it returns the probability density function.
    		 LOGNORMDIST(4,3.5,1.2,TRUE)
    Returns: 0.0390836
    LOGNORMDIST(4,3.5,1.2,FALSE)
    Returns: 0.0176176
    LOGNORMINV(probability, mean, standard_dev) Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters Mean and Standard_dev. If p = LOGNORMDIST(x, ...) then LOGNORMINV(p,...) = x. Use the lognormal distribution to analyze logarithmically transformed data.
    Arguments:
    • probability - Required. A probability associated with the lognormal distribution.
    • mean - Required. The mean of ln(x).
    • standard_dev - Required. The standard deviation of ln(x).
    		 LOGNORMINV(0.039084, 3.5, 1.2)
    Returns: 4.0000252
    NEGBINOMDIST(number_f,number_s,probability_s,cumulative) Returns the negative binomial distribution, the probability that there will be Number_f failures before the Number_s-th success, with Probability_s probability of a success. This function is similar to the binomial distribution, except that the number of successes is fixed, and the number of trials is variable.Like the binomial, trials are assumed to be independent. For example, you need to find 10 people with excellent reflexes, and you know the probability that a candidate has these qualifications is 0.3. NEGBINOMDIST calculates the probability that you will interview a certain number of unqualified candidates before finding all 10 qualified candidates.
    Arguments:    		 NEGBINOMDIST(10,5,0.25,TRUE)
    Returns: 0.3135141

    NEGBINOMDIST(10,5,0.25,FALSE)
    Returns: 0.0550487
    NORMDIST(x,mean,stdDev,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.
    Arguments:
    • x - Required.The value for which you want the distribution.
    • mean - Required. The arithmetic mean of the distribution.
    • stdDev - Required. The standard deviation of the distribution.
    • cumulative - Required. A logical value that determines the form of the function. If cumulative is TRUE, NORMDIST returns the cumulative distribution function; if FALSE, it returns the probability mass function.
    		 NORMDIST(42, 40, 1.5, true)
    Returns: 0.9087887756126962

    NORMDIST(42, 40, 1.5, false)
    Returns: 0.10934004978399577
    NORMINV(x,mean,stdDev,cumulative) The Excel NORMINV function calculates the inverse of the Cumulative Normal Distribution Function for a supplied value of x, and a supplied distribution mean & standard deviation. The NORMINV function is new in Excel 2010 and so is not available in earlier versions of Excel.However, the function is simply an updated version of the Norminv function, which is available in earlier versions of Excel.
    Arguments:
    • probability - The value at which you want to evaluate the inverse function.
    • mean - The arithmetic mean of the distribution.
    • standard_dev - The standard deviation of the distribution.
    		 NORMINV(0.6, 5, 2)
    Returns: 5.506694206
    POISSONDIST(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.
    Arguments:
    • X Required. The number of events.
    • Mean Required. The expected numeric value.
    • Cumulative Required. A logical value that determines the form of the probability distribution returned. If cumulative is TRUE, POISSONDIST returns the cumulative Poisson probability that the number of random events occurring will be between zero and x inclusive; if FALSE, it returns the Poisson probability mass function that the number of events occurring will be exactly x.
    		 POISSONDIST(2,5,TRUE) =
    Returns: 0.124652

    POISSONDIST(2,5,FALSE)
    Returns: 0.084224
    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, see the PEARSON function.The r-squared value can be interpreted as the proportion of the variance in y attributable to the variance in x.
    Arguments:
    • Known_y's - Required. An array or range of data points.
    • Known_x's - Required. An array or range of data points.
    		 RSQ(ARRAY(2,3,9,1,8,7,5), ARRAY(6,5,11,7,5,4,4))
    Returns: 0.05795
    STDDEV(arr, entirePopulation) Returns the standard deviation of the values in the set. It is computed as the positive square root of the variance. STDDEV(ARRAY(1345,1301,1368,1322,1310,1370,1318,1350,1303,1299), true)
    Returns: 26.05455814

    STDDEV(ARRAY(1345,1301,1368,1322,1310,1370,1318,1350,1303,1299), false) =
    Returns: 27.46392
    TDIST(x,degOfFreedom, 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.
    Arguments:
    • X - Required.The value at which to evaluate the function.
    • degOfFreedom - Required. An integer indicating the number of degrees of freedom.
    • cumulative - Required. A logical value that determines the form of the function. If cumulative is TRUE, TDIST returns the cumulative distribution function; if FALSE, it returns the probability density function.
    		 TDIST(60,1,true)
    Returns: 0.99469533

    TDIST(8,3,false)
    Returns: 0.00073691
    TINV2T(probability,deg_freedom) Returns the two-tailed inverse of the Student's t-distribution.
    Arguments:
    • probability - Required. The probability associated with the Student's t-distribution.
    • deg_freedom - Required. The number of degrees of freedom with which to characterize the distribution.
    		 TINV2T(0.546449,60) =
    Returns: 0.606533
    TINV(probability,degreeOfFreedom) Returns the left-tailed inverse of the Student's t-distribution.
    Arguments:
    • probability - Required.The probability associated with the Student's t-distribution.
    • degreeOfFreedom - Required. The number of degrees of freedom with which to characterize the distribution.
    		 TINV(0.75,2)
    Returns: 0.8164966
    VARIANCE(arr, entirePopulation) The variance is used to measure the tendency of the values in the set to deviate from the average. VARIANCE(ARRAY(1345,1301,1368,1322,1310,1370,1318,1350,1303,1299), true)
    Returns: 678,84

    VARIANCE(ARRAY(1345,1301,1368,1322,1310,1370,1318,1350,1303,1299), false)
    Returns: 754.2667
    WEIBULLDIST(x,alpha,beta,cumulative) Returns the Weibull distribution. Use this distribution in reliability analysis, such as calculating a device's mean time to failure.
    Arguments:
    • x - Required. The value at which to evaluate the function.
    • alpha - Required. A parameter to the distribution.
    • beta - Required. A parameter to the distribution.
    • cumulative - Required. Determines the form of the function.
    		 WEIBULLDIST(105,20,100,TRUE)
    Returns: 0.929581

    WEIBULLDIST(105,20,100,FALSE)
    Returns: 0.035589

     

     

    © Nevron Software LLC. All rights reserved. Send Feedback.