Compatibility Functions in Excel

Function

Description

Syntax and Formula

BETADIST

The BETADIST function in Excel returns the cumulative beta probability density function or the probability that a variable, which follows a beta distribution, is less than or equal to a specified value.

Syntax: BETADIST(x,alpha,beta,A,B)x: A value at which the probability density function is to be evaluated.alpha: The alpha parameter of the beta distribution.beta: The beta parameter of the beta distribution.A: Optional. The lower bound of the beta distribution.B: Optional. The upper bound of the beta distribution.

Formula: BETADIST(2,2,3,0,4)This example returns the probability that a variable, which follows a beta distribution with alpha = 2 and beta = 3, is less than or equal to 2. The lower bound of the beta distribution is 0 and the upper bound is 4.

BETAINV

The BETAINV function returns the inverse of the cumulative beta probability density function (BETA.DIST).

Syntax: BETAINV(probability, alpha, beta, [A], [B])

Formula: BETAINV(0.5,2,4,0,1)This example returns 0.5, which is the inverse of the cumulative beta probability density function with a probability of 0.5, alpha of 2, beta of 4, and lower bound of 0 and upper bound of 1.

BINOMDIST

The BINOMDIST function in Excel calculates the individual term binomial distribution probability.

Syntax: BINOMDIST(number_s,trials,probability_s,cumulative)

Formula: To calculate the probability of getting 3 successes in 5 trials, with a probability of success of 0.4, the formula would be:BINOMDIST(3,5,0.4,FALSE)The result would be 0.3456.

CHIDIST

CHIDIST is an Excel function that returns the one-tailed probability of the chi-squared distribution. The syntax for CHIDIST is:CHIDIST(x,deg_freedom)Where x is the value at which to evaluate the distribution, and deg_freedom is the number of degrees of freedom.

Syntax: CHIDIST(x,deg_freedom)

Formula: To find the probability of a chi-squared value of 10 with 5 degrees of freedom, the formula would be:=CHIDIST(10, 5)The result would be 0.0752352461465122.

CHIINV

The Excel CHIINV function returns the inverse of the left-tailed probability of the chi-squared distribution.

Syntax: CHIINV(probability,deg_freedom)

Formula: To calculate the inverse of the left-tailed probability of the chi-squared distribution with a probability of 0.95 and degrees of freedom of 5, use the following Formula: CHIINV(0.95,5)The result is 11.070.

CHITEST

The CHITEST function in Microsoft Excel is used to calculate the test for independence of two ranges of categorical data.

Syntax: CHITEST(actual_range, expected_range)

Formula: CHITEST(A2:A7, B2:B7)

CONFIDENCE

CONFIDENCE is an Excel function that calculates the confidence interval for a population mean, using a normal distribution. It takes three arguments: alpha, standard_dev, and size.Alpha is the significance level used to compute the confidence level. It is a number between 0 and 1.Standard_dev is the standard deviation for the population.Size is the sample size.

Syntax: CONFIDENCE(alpha,standard_dev,size)

Formula: CONFIDENCE(0.05,2,100)This example would calculate the 95% confidence interval for a population with a standard deviation of 2 and a sample size of 100.

COVAR

COVAR is an Excel function that calculates the covariance between two sets of data. It is used to measure the linear relationship between two variables.

Syntax: COVAR(array1, array2)

Formula: COVAR(A1:A5, B1:B5)This example will calculate the covariance between the data in the range A1:A5 and the data in the range B1:B5.

CRITBINOM

The CRITBINOM function returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value.

Syntax: CRITBINOM(trials, probability_s, alpha)

Formula: CRITBINOM(10, 0.5, 0.95)This example would return the smallest value for which the cumulative binomial distribution with 10 trials and a probability of success of 0.5 is greater than or equal to 0.95. The result would be 8.

EXPONDIST

The EXPONDIST function returns the exponential distribution for a specified value, lambda, and cumulative.

Syntax: EXPONDIST(x,lambda,cumulative)

Formula: EXPONDIST(2,3,TRUE)This formula returns the cumulative exponential distribution for x=2 and lambda=3.

FDIST

The FDIST function in Excel is a statistical function that returns the F probability distribution. It returns the probability that the random variable has a value between two specified values.

Syntax: FDIST(x,deg_freedom1,deg_freedom2)

Formula: FDIST(2,3,4)This example returns the probability that the random variable has a value between 2 and 3 with degrees of freedom 3 and 4.

FINV

FINV is an Excel function used to calculate the inverse of the F-Distribution.

Syntax: FINV(probability, degrees_freedom1, degrees_freedom2)

Formula: FINV(0.95, 10, 10)This example would return the inverse of the F-Distribution with a probability of 0.95, 10 degrees of freedom for the numerator, and 10 degrees of freedom for the denominator.

FTEST

The FTEST function returns the result of an F-test. An F-test is a type of statistical test that is used to compare the variances of two data sets.

Syntax: FTEST(array1, array2)

Formula: FTEST(A1:A10, B1:B10)

GAMMADIST

GAMMADIST is an Excel function that calculates the Gamma probability density function or the cumulative Gamma distribution.

Syntax: GAMMADIST(x,alpha,beta,cumulative)

Formula: GAMMADIST(2,3,4,TRUE)This example will calculate the cumulative Gamma distribution for x = 2, alpha = 3, and beta = 4.

GAMMAINV

The GAMMAINV function returns the inverse of the gamma cumulative distribution.

Syntax: GAMMAINV(probability,alpha,beta)

Formula: GAMMAINV(0.95,2,2)This example returns the inverse of the gamma cumulative distribution for a probability of 0.95, an alpha of 2, and a beta of 2.

HYPGEOMDIST

The HYPGEOMDIST function returns the hypergeometric distribution. It is used to calculate the probability of a given number of successes in a sample of a given size, taken from a population of a given size containing a certain number of successes.

Syntax: HYPGEOMDIST(sample_s, number_sample, population_s, number_successes)

Formula: HYPGEOMDIST(10, 5, 20, 3)This example returns the probability of 3 successes in a sample of 5 taken from a population of 20 containing 3 successes.

LOGINV

LOGINV is an Excel function that returns the inverse of the lognormal cumulative distribution.

Syntax: LOGINV(probability, mean, standard_dev)

Formula: LOGINV(0.5, 0.5, 0.5) returns 1.29

LOGNORMDIST

The LOGNORMDIST function in Microsoft Excel returns the cumulative log-normal distribution for a given set of parameters.

Syntax: LOGNORMDIST(x, mean, standard_dev)

Formula: To find the cumulative log-normal distribution for a value of x = 0.5, with a mean of 0.3 and a standard deviation of 0.2, the formula would be: LOGNORMDIST(0.5, 0.3, 0.2). The result of this formula is 0.788.

MODE

MODE: The MODE function returns the most frequently occurring, or repetitive, value in an array or range of data.

Syntax: MODE(number1, [number2], …)

Formula: MODE(1,2,3,3,3,4,5)Result: 3

NEGBINOMDIST

The NEGBINOMDIST function returns the probability of a given number of failures occurring before a specified number of successes in a negative binomial experiment.

Syntax: NEGBINOMDIST(number_f, number_s, probability_s)

Formula: To calculate the probability of 3 failures occurring before 5 successes in a negative binomial experiment with a success probability of 0.4, the formula would be:=NEGBINOMDIST(3, 5, 0.4)The result would be 0.09216.

NORMDIST

NORMDIST is a statistical function in Microsoft Excel which returns the normal cumulative distribution for a specified mean and standard deviation.

Syntax: NORMDIST(x, mean, standard_dev, cumulative)

Formula: To find the normal cumulative distribution for a mean of 10 and a standard deviation of 2, the formula would be =NORMDIST(10, 10, 2, TRUE).

NORMINV

The NORMINV function returns the inverse of the normal cumulative distribution for a given mean and standard deviation.

Syntax: NORMINV(probability, mean, standard_dev)

Formula: To find the inverse of the normal cumulative distribution for a probability of 0.5, a mean of 10 and a standard deviation of 2, the formula would be:=NORMINV(0.5, 10, 2)The result would be 10.

NORMSDIST

The NORMSDIST function returns the cumulative normal distribution for a given value. It is used to calculate the probability that a random variable will take a value less than or equal to a given value.

Syntax: NORMSDIST(x)

Formula: To calculate the probability that a random variable will take a value less than or equal to 0.5, the formula would be: =NORMSDIST(0.5)

NORMSINV

NORMSINV is an Excel function that calculates the inverse of the standard normal cumulative distribution. It returns the value of the inverse standard normal distribution for a given probability.

Syntax: NORMSINV(probability)

Formula: To calculate the inverse of the standard normal cumulative distribution for a probability of 0.5, the formula would be:=NORMSINV(0.5)This would return a value of 0, as the inverse of the standard normal cumulative distribution for a probability of 0.5 is 0.

PERCENTILE

The PERCENTILE function returns the k-th percentile of values in a range.

Syntax: PERCENTILE(array,k)

Formula: PERCENTILE(A1:A10,0.5)This example returns the 50th percentile of the values in cells A1 through A10.

PERCENTRANK

The PERCENTRANK function in Excel returns the rank of a value in a data set as a percentage of the data set.

Syntax: PERCENTRANK(array, x, [significance])

Formula: PERCENTRANK(A2:A10, A2)This formula will return the rank of the value in cell A2 as a percentage of the values in the range A2:A10.

POISSON

The POISSON function in Excel calculates the probability of a given number of events occurring in a fixed period of time, given the average rate of occurrence.

Syntax: POISSON(x, mean, cumulative)x = The number of eventsmean = The expected mean number of eventscumulative = A logical value that determines the form of the function (TRUE for cumulative, FALSE for probability)

Formula: POISSON(4,6,FALSE)This example would calculate the probability of 4 events occurring in a fixed period of time, given the average rate of occurrence is 6. The result would be 0.1839.

QUARTILE

The QUARTILE function returns the quartile of a given set of data. Quartiles divide a set of data into four equal parts.

Syntax: QUARTILE(array, quart)

Formula: QUARTILE(A2:A10,2)This example returns the second quartile of the data set in cells A2 to A10.

RANK

The RANK function in Excel is used to return the rank of a number within a set of numbers.

Syntax: RANK(number,ref,[order])

Formula: RANK(A2,A2:A7,1)In this example, the RANK function will return the rank of the number in cell A2 within the range of cells A2 to A7, in ascending order.

STDEV

STDEV is an Excel function that calculates the standard deviation of a set of numbers. It is used to measure the amount of variation or dispersion from the average of a set of values.

Syntax: STDEV(number1, [number2], …)

Formula: STDEV(2,4,6,8,10)The result of this example would be 2.87.

STDEVP

STDEVP is a statistical function in Microsoft Excel that calculates the standard deviation of a population based on a sample of numbers.

Syntax: STDEVP(number1, [number2], …)

Formula: STDEVP(2, 4, 6, 8)This would return 2, which is the standard deviation of the sample set (2, 4, 6, 8).

TDIST

The TDIST function returns the probability associated with a Student’s t-distribution.

Syntax: TDIST(x,deg_freedom,tails)

Formula: TDIST(2,5,1)This example returns the probability associated with a Student’s t-distribution for x=2, degrees of freedom=5, and 1 tail.

TINV

TINV is an Excel function used to calculate the inverse of the t-distribution. It is used to calculate the critical value of the t-distribution, given a certain significance level.

Syntax: TINV(probability, degrees_of_freedom)

Formula: TINV(0.05, 10)This example returns the critical value of the t-distribution with 10 degrees of freedom and a significance level of 0.05. The result is 1.81246.

TTEST

TTEST Function:The TTEST function calculates the probability associated with the Student’s t-test. The Student’s t-test is used to determine whether two samples are likely to have come from the same two underlying populations that have the same mean.

Syntax: TTEST(array1, array2, tails, type)Array1: The first array of data.Array2: The second array of data.Tails: The number of tails in the t-test. The default value is 2.Type: The type of t-test. The default value is 1.

Formula: TTEST(A2:A7, B2:B7, 2, 1)This example will calculate the probability associated with the Student’s t-test for the two arrays of data in A2:A7 and B2:B7, using two tails and type 1.

VAR

The VAR function in Excel is used to calculate the variance of a set of values.

Syntax: VAR(number1, [number2], …)

Formula: VAR(2,4,6,8)This will return 6, which is the variance of the set of values.

VARP

VARP is an Excel function that calculates the variance of a population based on a set of values.

Syntax: VARP(value1, [value2], …)

Formula: VARP(1,2,3,4,5)Result: 2

WEIBULL

The WEIBULL function in Excel calculates the Weibull probability density function or the Weibull cumulative distribution function for a supplied set of parameters.

Syntax: WEIBULL(x,alpha,beta,cumulative)

Formula: WEIBULL(2,3,4,TRUE)This example will calculate the Weibull cumulative distribution function with x = 2, alpha = 3, and beta = 4.

ZTEST

The ZTEST function in Excel calculates the one-tailed probability-value (P-value) of a z-test.

Syntax: ZTEST(array, x, [sigma])

Formula:=ZTEST(A1:A50, 70, 15), calculates the one-tailed P-value of a z-test for a sample of data in cells A1 to A50 in Excel.