Statistical Functions in Excel

Function

Description

Syntax and Formula

AVEDEV

AVEDEV: The AVEDEV function in Excel returns the average of the absolute deviations of data points from their mean.

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

Formula: AVEDEV(2,4,6,8)The result of this function is 2, which is the average of the absolute deviations of 2, 4, 6, and 8 from their mean of 5.

AVERAGE

The AVERAGE function in Excel calculates the arithmetic mean of a given set of values.

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

Formula: AVERAGE(10,20,30,40)Result: 25

AVERAGEA

The AVERAGEA function is an Excel function that calculates the average of all numbers, including text and logical values.

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

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

AVERAGEIF

The AVERAGEIF function returns the average (arithmetic mean) of all numbers in a range of cells, based on a given criteria.

Syntax: AVERAGEIF(range, criteria, [average_range])

Formula: AVERAGEIF(A2:A9, “>50”, B2:B9)This formula will return the average of all numbers in range B2:B9, where the corresponding cell in range A2:A9 is greater than 50.

AVERAGEIFS

The AVERAGEIFS function is an Excel function that calculates the average of a range of cells that meet multiple criteria.

Syntax: AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Formula: To calculate the average of the numbers in the range A1:A10, if the corresponding cells in the range B1:B10 contain the value “apple”, the formula would be: AVERAGEIFS(A1:A10, B1:B10, “apple”)

BETA.DIST

BETA.DIST is an Excel function used to calculate the cumulative beta probability density function (PDF) for a given set of parameters.

Syntax: BETA.DIST(x, alpha, beta, cumulative, [A], [B])

Formula: To calculate the cumulative beta probability density function for a given set of parameters x = 0.5, alpha = 2, beta = 3, and cumulative = TRUE, the following formula is used:=BETA.DIST(0.5, 2, 3, TRUE)The result of this formula is 0.7421875.

BETA.INV

BETA.INV is an Excel function that returns the inverse of the cumulative distribution function for a specified beta distribution.

Syntax: BETA.INV(probability, alpha, beta, [A], [B])

Formula: BETA.INV(0.7, 2, 5, 0, 1)This example returns the value 0.837, which is the inverse of the cumulative distribution function for the specified beta distribution with a probability of 0.7, alpha of 2, and beta of 5.

BINOM.DIST

The BINOM.DIST function in Excel returns the individual term binomial distribution probability. It calculates the probability of a certain number of successes in a given number of independent trials, each with the same probability of success.

Syntax: BINOM.DIST(number_s, trials, probability_s, cumulative)

Formula: BINOM.DIST(2, 10, 0.5, FALSE)This example returns the probability of 2 successes in 10 independent trials, each with a probability of success of 0.5.

BINOM.DIST.RANGE

BINOM.DIST.RANGE is an Excel function that calculates the probability of a certain number of successes in a sequence of independent Bernoulli trials, given a probability of success in each trial.

Syntax: BINOM.DIST.RANGE(trials, probability_s, number_s, number_s2)

Formula: To calculate the probability of getting between 2 and 4 successes in a sequence of 5 independent Bernoulli trials, with a probability of success of 0.4 in each trial, the formula would be: BINOM.DIST.RANGE(5, 0.4, 2, 4)

BINOM.INV

The BINOM.INV function is an Excel statistical function that returns the inverse of the cumulative binomial distribution. This function is useful in determining the number of successes given a probability and a number of trials.

Syntax: BINOM.INV(probability, number_trials, alpha)

Formula: Suppose you want to find the number of successes given a probability of 0.5 and 10 trials. The formula would be:=BINOM.INV(0.5,10,0.5)The result would be 5, meaning that there is a 50% chance of 5 successes in 10 trials.

CHISQ.DIST

The CHISQ.DIST function returns the cumulative probability of a chi-squared distribution.

Syntax: CHISQ.DIST(x,deg_freedom,cumulative)

Formula: CHISQ.DIST(4,3,TRUE)This example returns the cumulative probability of a chi-squared distribution with 4 degrees of freedom. The result is 0.2266.

CHISQ.DIST.RT

The CHISQ.DIST.RT function returns the right-tailed probability of the chi-squared distribution.

Syntax: CHISQ.DIST.RT(x,deg_freedom)

Formula: CHISQ.DIST.RT(3,4)This example returns the right-tailed probability of the chi-squared distribution with 3 degrees of freedom. The result is 0.0682689492137086.

CHISQ.INV

The CHISQ.INV function in Excel returns the inverse of the left-tailed probability of the chi-squared distribution.

Syntax: CHISQ.INV(probability,deg_freedom)

Formula: CHISQ.INV(0.95,2)This example returns the inverse of the left-tailed probability of the chi-squared distribution with a probability of 0.95 and 2 degrees of freedom. The result is 5.9915.

CHISQ.INV.RT

The CHISQ.INV.RT function returns the inverse of the right-tailed probability of the chi-squared distribution.

Syntax: CHISQ.INV.RT(probability,deg_freedom)

Formula: CHISQ.INV.RT(0.95,2)This example returns the inverse of the right-tailed probability of the chi-squared distribution with a probability of 0.95 and a degree of freedom of 2. The result is 5.991464547.

CHISQ.TEST

CHISQ.TEST is an Excel function used to perform a chi-square test of independence. It is used to determine whether there is a significant association between two categorical variables.

Syntax: CHISQ.TEST(observed_range, expected_range)

Formula: CHISQ.TEST(A1:B10, C1:D10)

CONFIDENCE.NORM

The CONFIDENCE.NORM function returns the confidence interval for a population mean, using a normal distribution. This function uses a confidence level to calculate the margin of error that is added and subtracted from the sample mean to create the confidence interval.

Syntax: CONFIDENCE.NORM(alpha,standard_dev,size)alpha: The significance level used to compute the confidence level.standard_dev: The standard deviation for the data range.size: The number of observations in the data range.

Formula: To calculate the 95% confidence interval for a sample of 100 observations with a standard deviation of 10, the formula would be:1.95996398454005The result would be a margin of error of 2.262.

CONFIDENCE.T

The CONFIDENCE.T function is used to calculate the confidence interval for a population mean, based on a sample mean and standard deviation.

Syntax: CONFIDENCE.T(alpha, standard_dev, size)

Formula: To calculate the 95% confidence interval for a sample mean of 10, with a standard deviation of 4, and a sample size of 25, the formula would be: CONFIDENCE.T(0.05, 4, 25) which would return a result of 8.8 to 11.2.

CORREL

CORREL is an Excel function that returns the correlation coefficient of two sets of values. It is used to measure the linear relationship between two variables.

Syntax: CORREL(array1, array2)

Formula: CORREL(A1:A10, B1:B10)This example returns the correlation coefficient of the values in cells A1 through A10 and B1 through B10.

COUNT

The COUNT function is used to count the number of cells that contain numbers within a specified range.

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

Formula: COUNT(A1:A6, C1:C3), This example counts numbers across multiple ranges, say A1:A6 and C1:C3 where C1 to C3 also contain numbers.

COUNTA

The COUNTA function in Microsoft Excel counts the number of cells that contain data within a range.

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

Formula: COUNTA(A1:A10)This example will count the number of cells in the range A1:A10 that contain data.

COUNTBLANK

The COUNTBLANK function counts the number of empty cells in a range of cells.

Syntax: COUNTBLANK(range)

Formula: COUNTBLANK(A1:A10)This formula would count the number of blank cells in the range A1 to A10.

COUNTIF

COUNTIF is an Excel function used to count the number of cells within a range that meet a specified criteria.

Syntax: COUNTIF(range, criteria)

Formula: COUNTIF(A1:A10, “>5”)This example will count the number of cells in range A1:A10 that are greater than 5.

COUNTIFS

The COUNTIFS function is a statistical function used to count the number of cells in a range that meet multiple criteria.

Syntax: COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2], …)

Formula: COUNTIFS (A1:A10, “>20”, B1:B10, “>30”)This example will count the number of cells in the range A1:A10 that are greater than 20, and the number of cells in the range B1:B10 that are greater than 30.

COVARIANCE.P

The COVARIANCE.P function calculates the population covariance of two sets of values.

Syntax: COVARIANCE.P(array1, array2)

Formula: COVARIANCE.P(A2:A7,B2:B7)This example returns the population covariance of the values in cells A2 through A7 and the values in cells B2 through B7.

COVARIANCE.S

The COVARIANCE.S function is an Excel function used to calculate the sample covariance of two sets of values.

Syntax: COVARIANCE.S(array1,array2)

Formula: COVARIANCE.S(A1:A5,B1:B5)This example will calculate the sample covariance of the two sets of values in the range A1:A5 and B1:B5.

DEVSQ

DEVSQ is an Excel function that calculates the sum of squares of deviations of data points from their sample mean.

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

Formula: DEVSQ(2, 3, 4, 5)This example would return the sum of squares of deviations of the data points 2, 3, 4, and 5 from their sample mean, which is 3.5. The result would be 4.5.

EXPON.DIST

The EXPON.DIST function returns the exponential distribution. It returns the probability that a random variable, which follows an exponential distribution, is less than or equal to a given value.

Syntax: EXPON.DIST(x,lambda,cumulative)x: The value at which you want to evaluate the distribution.lambda: The rate parameter of the distribution.cumulative: A logical value that determines the form of the function.

Formula: EXPON.DIST(2,1,TRUE)This formula will return the probability that a random variable, which follows an exponential distribution with rate parameter 1, is less than or equal to 2.

F.DIST

F.DIST is an Excel function that returns the F probability distribution. It calculates the probability of a value occurring given a certain number of degrees of freedom.

Syntax: F.DIST(x,deg_freedom1,deg_freedom2)

Formula: F.DIST(2,2,4)This example returns the probability of a value of 2 occurring given 2 and 4 degrees of freedom.

F.DIST.RT

The F.DIST.RT function returns the right-tailed F probability distribution. This function is used to calculate the probability that the observed variance in a sample is greater than the variance in the entire population.

Syntax: F.DIST.RT(x,deg_freedom1,deg_freedom2)

Formula: F.DIST.RT(4,5,6)This example will return the probability that the observed variance in a sample is greater than 4, when the degrees of freedom for the sample and the population are 5 and 6, respectively.

F.INV

F.INV is an Excel function used to calculate the inverse of the F-distribution. It returns the probability associated with a given x-value and degrees of freedom.

Syntax: F.INV(probability, degrees_freedom1, degrees_freedom2)

Formula: F.INV(0.05, 5, 10)This example returns the x-value associated with a probability of 0.05, with 5 degrees of freedom in the numerator and 10 degrees of freedom in the denominator.

F.INV.RT

F.INV.RT is an Excel function that returns the inverse of the F probability distribution. It returns the value of the inverse cumulative distribution function (inverse of the cumulative probability) for a given probability.

Syntax: F.INV.RT(probability,deg_freedom1,deg_freedom2)

Formula: F.INV.RT(0.95,2,3)This example returns the inverse cumulative distribution for a probability of 0.95 with two degrees of freedom in the numerator and three degrees of freedom in the denominator. The result is 6.867.

F.TEST

F.TEST is an Excel function used to calculate the probability of two samples having the same variance. It takes two sets of data as its arguments and returns a probability value.

Syntax: F.TEST(array1, array2)

Formula: F.TEST(A1:A10, B1:B10)This function returns the probability that the two sets of data in cells A1:A10 and B1:B10 have the same variance.

FISHER

FISHER is an Excel function that returns the Fisher transformation at x-value. The Fisher transformation is a way to normalize data that may not be normally distributed.

Syntax: FISHER(x)

Formula: FISHER(0.5) returns 0.279415498198926

FISHERINV

The FISHERINV function returns the inverse of the Fisher transformation at a specified value.

Syntax: FISHERINV(x)

Formula: FISHERINV(0.9) returns 0.47140452079103

FORECAST

The FORECAST function is used to calculate a future value based on existing values provided. It uses linear regression to calculate the value.

Syntax: FORECAST(x, known_y’s, known_x’s)

Formula: FORECAST(4,A1:A4,B1:B4)This example uses the FORECAST function to calculate the future value of 4 based on the existing values in cells A1-A4 and B1-B4.

FORECAST.ETS

The FORECAST.ETS function is an Excel function that predicts future values based on existing values. It uses the Exponential Triple Smoothing (ETS) algorithm to predict future values.

Syntax: FORECAST.ETS(known_y’s, [known_x’s], new_x’s, [seasonality], [trend], [confidence])

Formula: FORECAST.ETS(A1:A10, B1:B10, 11, 0, 1, 95)This example uses the values in cells A1 to A10 and B1 to B10 to predict the value in cell A11 using the Exponential Triple Smoothing (ETS) algorithm with a trend of 1 and a confidence of 95%.

FORECAST.ETS.CONFINT

The FORECAST.ETS.CONFINT function is an Excel function that returns a confidence interval for a forecast generated by the FORECAST.ETS function.

Syntax: FORECAST.ETS.CONFINT(known_y’s, [known_x’s], [confidence_level], [forecast_type], [seasonality], [data_completion], [aggregation])

Formula: FORECAST.ETS.CONFINT(B2:B13,C2:C13,90%)This example will return a 90% confidence interval for the forecast generated by the FORECAST.ETS function using the known y values in the range B2:B13 and the known x values in the range C2:C13.

FORECAST.ETS.SEASONALITY

The FORECAST.ETS.SEASONALITY function in Microsoft Excel is used to predict future values based on existing values that have a seasonal pattern.

Syntax: FORECAST.ETS.SEASONALITY(x, known_y’s, [seasonality], [data_completion], [aggregation])

Formula: FORECAST.ETS.SEASONALITY(B2, B3:B12, 12, 1, 0)This example uses the FORECAST.ETS.SEASONALITY function to predict the value in cell B2 based on the values in cells B3:B12. The seasonality is set to 12, data completion is set to 1, and aggregation is set to 0.

FORECAST.ETS.STAT

The FORECAST.ETS.STAT function in Microsoft Excel is used to calculate a future value based on existing values using the Exponential Triple Smoothing (ETS) algorithm.

Syntax: FORECAST.ETS.STAT(known_y’s, [known_x’s], [new_x’s], [stat_type], [seasonality], [aggregation])

Formula: FORECAST.ETS.STAT(A1:A10, B1:B10, 11, “stat”, 12, “average”)

FORECAST.LINEAR

The FORECAST.LINEAR function is an Excel function that predicts a value based on existing values. It uses a linear regression algorithm to calculate the best fit line for the existing values and predict a value for the specified x value.

Syntax: FORECAST.LINEAR(x, known_y’s, known_x’s)

Formula: To predict the sales for the month of April based on the existing sales data for the months of January, February and March, the following formula can be used:FORECAST.LINEAR(4, B2:B4, A2:A4)Where A2:A4 contains the months of January, February and March, and B2:B4 contains the sales figures for those months.

FREQUENCY

The FREQUENCY function in Excel returns a frequency distribution, which is a summary table that shows the frequency of values within a range.

Syntax: FREQUENCY(data_array, bins_array)

Formula: FREQUENCY(A2:A8,B2:B4)This example will return a frequency distribution of the values in range A2:A8, using the range B2:B4 as the bins.

GAMMA

The GAMMA function is used to calculate the gamma value of a given number. Gamma is a mathematical function that is used to calculate the probability of a random variable taking on a value less than or equal to a certain number.

Syntax: GAMMA(number)

Formula: GAMMA(5)This formula will return the gamma value of 5.

GAMMA.DIST

The GAMMA.DIST function in Excel returns the gamma distribution, which is a type of probability distribution. It is used to calculate the probability that a random variable is less than or equal to a given value.

Syntax: GAMMA.DIST(x,alpha,beta,cumulative)

Formula: GAMMA.DIST(2,2,2,TRUE)This example returns the cumulative gamma distribution for x=2, alpha=2, and beta=2. The result is 0.632120558828558.

GAMMA.INV

The GAMMA.INV function returns the inverse of the gamma cumulative distribution.

Syntax: GAMMA.INV(probability,alpha,beta,cumulative)

Formula: To calculate the inverse of the gamma cumulative distribution with a probability of 0.5, an alpha of 2, and a beta of 3, the following formula would be used:=GAMMA.INV(0.5,2,3,TRUE)The result of this formula would be 3.

GAMMALN

GAMMALN: The GAMMALN function returns the natural logarithm of the gamma function, G(x).

Syntax: GAMMALN(x)

Formula: GAMMALN(7)The result of this example would be 1.94591.

GAMMALN.PRECISE

The GAMMALN.PRECISE function is an Excel function that returns the natural logarithm of the gamma function, G(x), to a high degree of precision.

Syntax: GAMMALN.PRECISE(x)

Formula: GAMMALN.PRECISE(5)The result of this function would be the natural logarithm of the gamma function, G(5), to a high degree of precision.

GAUSS

The GAUSS function returns the cumulative distribution function (CDF) of a standard normal distribution.

Syntax: GAUSS(x)

Formula: GAUSS(1.2)This function returns the value of 0.8849.

GEOMEAN

The GEOMEAN function calculates the geometric mean of a set of numbers. The geometric mean is the average of a set of numbers multiplied together and then taking the nth root, where n is the number of numbers in the set.

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

Formula: GEOMEAN(2,3,4,5)This example would return 3.5, which is the geometric mean of the set of numbers (2, 3, 4, and 5).

GROWTH

The GROWTH function returns an array of y-values for a series of new x-values that are linearly interpolated from a given set of x-values and y-values.

Syntax: GROWTH(known_y’s, [known_x’s], [new_x’s], [const])

Formula: GROWTH(B2:B6,A2:A6,A7:A10,TRUE)

HARMEAN

The HARMEAN function is used to calculate the harmonic mean of a set of numbers. The harmonic mean is the reciprocal of the arithmetic mean of the reciprocals of the given numbers.

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

Formula: HARMEAN(2,4,6)Result: 3.6

HYPGEOM.DIST

HYPGEOM.DIST is an Excel function that calculates the probability of a given number of successes in a sample, without replacement, from a population of a given size.

Syntax: HYPGEOM.DIST(sample_s, number_sample, population_s, successes, cumulative)

Formula: To calculate the probability of getting 3 successes in a sample of 10 from a population of 20, the formula would be: HYPGEOM.DIST(10, 20, 3, FALSE).

INTERCEPT

The INTERCEPT function in Excel returns the y-intercept of a given linear regression line. It is used to calculate the point at which a line crosses the y-axis.

Syntax: INTERCEPT(known_y’s, known_x’s)

Formula: INTERCEPT(B2:B7,A2:A7)This formula returns the y-intercept of the linear regression line based on the data in cells A2:A7 and B2:B7.

KURT

KURT: The KURT function in Excel returns the kurtosis of a data set. Kurtosis is a measure of the “peakedness” of a distribution.

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

Formula: KURT(2,4,6,8,10)This formula would return -1.2, which is the kurtosis of the given data set.

LARGE

The LARGE function returns the nth largest value from a range of values.

Syntax: LARGE(array, nth)

Formula: LARGE(A1:A10, 3)This example would return the third largest value from the range of values in cells A1 to A10.

LINEST

The LINEST function is a statistical function in Excel that returns the parameters of a linear trend. It can be used to calculate the slope and y-intercept of a line, as well as the correlation coefficient, standard error of the estimate, and other statistics.

Syntax: LINEST(known_y’s, [known_x’s], [const], [stats])

Formula: LINEST(B2:B11,A2:A11,TRUE,TRUE)

LOGEST

The LOGEST function is used to calculate an exponential curve that best fits a set of data points. It returns an array of values that describe the exponential curve, including the y-intercept, the slope, and the correlation coefficient.

Syntax: LOGEST(known_y’s, [known_x’s], [const], [stats])

Formula: LOGEST(A2:A10, B2:B10, TRUE, TRUE)This example will calculate an exponential curve that best fits the data points in cells A2:A10 and B2:B10, and return an array of values that describe the exponential curve, including the y-intercept, the slope, and the correlation coefficient.

LOGNORM.DIST

The LOGNORM.DIST function returns the cumulative lognormal distribution of x, given parameters for the distribution’s mean and standard deviation.

Syntax:LOGNORM.DIST(x, mean, standard_dev, cumulative)

Formula: LOGNORM.DIST(2, 3, 0.5, TRUE)This example returns the cumulative lognormal distribution of 2, given a mean of 3 and a standard deviation of 0.5.

LOGNORM.INV

The LOGNORM.INV function returns the inverse of the lognormal cumulative distribution for a specified value, mean and standard deviation.

Syntax: LOGNORM.INV(probability, mean, standard_dev)

Formula: LOGNORM.INV(0.5, 2, 0.5)This example returns the inverse of the lognormal cumulative distribution for a probability of 0.5, a mean of 2 and a standard deviation of 0.5. The result is 1.897.

MAX

The MAX function in Excel returns the largest value from a range of values.

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

Formula: MAX(A1:A10)This example returns the largest value from the range A1:A10.

MAXA

The MAXA function is an Excel function that returns the maximum value in a range of values, including logical values and text.

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

Formula: MAXA(A1:A10)This example returns the maximum value in the range of cells A1 to A10.

MAXIFS

The MAXIFS function returns the maximum value in a range, based on multiple criteria.

Syntax: MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Formula: MAXIFS(A1:A10, B1:B10, “>=10”, C1:C10, “<=20”)This formula returns the maximum value in the range A1:A10, where the values in the range B1:B10 are greater than or equal to 10 and the values in the range C1:C10 are less than or equal to 20.

MEDIAN

The MEDIAN function returns the median of the given numbers. The median is the middle number in a set of numbers; it is calculated by arranging all the numbers from lowest to highest value and picking the middle number.

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

Formula: MEDIAN(2, 4, 5, 7, 9)Result: 5

MIN

The MIN function returns the smallest value from a given set of numbers.

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

Formula: MIN(2,4,6,8)Result: 2

MINA

The MINA function is used to find the minimum value in a given array of values, including text and logical values.

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

Formula: MINA(2,5,8,1,6)This will return 1, which is the minimum value in the array.

MINIFS

The MINIFS function is used to find the minimum value from a range of cells that meet multiple criteria.

Syntax: MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Formula: MINIFS(C2:C10,A2:A10,”>10″,B2:B10,”<20″)This formula will find the minimum value in the range C2:C10 where the values in A2:A10 are greater than 10 and the values in B2:B10 are less than 20.

MODE.MULT

MODE.MULT is an Excel function that returns a vertical array of the most frequently occurring, or repetitive, values in an array or range of data.

Syntax: MODE.MULT(array)

Formula: MODE.MULT(A2:A10)This formula returns the most frequently occurring value in the range A2:A10.

MODE.SNGL

MODE.SNGL is an Excel function that returns the most frequently occurring value in a set of numbers.

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

Formula: MODE.SNGL(1,2,2,3,3,3,4,4,4,4)The result of this example would be 4, since 4 is the most frequently occurring value in the set.

NEGBINOM.DIST

NEGBINOM.DIST is an Excel function that calculates the negative binomial distribution. The negative binomial distribution is a probability distribution that is used to represent the number of successes in a sequence of independent Bernoulli trials before a specified number of failures occur.

Syntax: NEGBINOM.DIST(number_f, number_s, probability_s, cumulative) Where:number_f = the number of failuresnumber_s = the number of successesprobability_s = the probability of successcumulative = a logical value that determines the form of the function (TRUE for cumulative distribution function and FALSE for probability mass function)

Formula: NEGBINOM.DIST(4, 2, 0.5, FALSE)This formula returns the probability of having 2 successes in 4 independent Bernoulli trials with a probability of success of 0.5.

NORM.DIST

NORM.DIST is an Excel function that returns the cumulative normal distribution for a given mean and standard deviation. It is used to calculate the probability that a random variable is less than or equal to a certain value.

Syntax: NORM.DIST(x, mean, standard_dev, cumulative)Where:x = The value for which you want to calculate the distribution.mean = The mean of the distribution.standard_dev = The standard deviation of the distribution.cumulative = A logical value that determines the form of the function. If cumulative is TRUE, NORM.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function.

Formula: NORM.DIST(2,1,1,TRUE)This formula returns the cumulative normal distribution for a mean of 1 and a standard deviation of 1, with a value of 2. The result is 0.97725.

NORM.INV

NORM.INV is an Excel function that returns the inverse of the standard normal cumulative distribution. The syntax is NORM.INV(probability, mean, standard_dev).

Syntax: NORM.INV(probability, mean, standard_dev)

Formula: NORM.INV(0.5, 0, 1) The above formula will return a value of 0, which is the inverse of the standard normal cumulative distribution at the probability of 0.5.

NORM.S.DIST

NORM.S.DIST is an Excel statistical function that returns the standard normal cumulative distribution function. It gives the probability that a random variable with a normal distribution will be less than or equal to a specified value.

Syntax: NORM.S.DIST(x, cumulative)x: The value for which you want the distribution.Cumulative: A logical value that determines the form of the function. If cumulative is TRUE, NORM.S.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function.

Formula: To find the probability that a random variable with a normal distribution will be less than or equal to 2.5, use the formula =NORM.S.DIST(2.5, TRUE). The result is 0.993790.

NORM.S.INV

NORM.S.INV is an Excel function that returns the inverse of the standard normal cumulative distribution. This function is used to calculate the probability of a value being greater than or less than a certain number.

Syntax: NORM.S.INV(probability)

Formula: To calculate the probability of a value being greater than 0.5, the formula would be NORM.S.INV(0.5). This would return 0.67448975019608.

PEARSON

The PEARSON function returns the Pearson product moment correlation coefficient, which is a measure of the linear correlation between two variables.

Syntax: PEARSON(array1, array2)

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

PERCENTILE.EXC

The PERCENTILE.EXC function is an Excel function that returns the k-th percentile of values in a range.

Syntax: PERCENTILE.EXC(array, k)

Formula: PERCENTILE.EXC(A1:A10, 0.5)This example will return the 50th percentile of the values in the range A1:A10.

PERCENTILE.INC

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

Syntax: PERCENTILE.INC(array, k)

Formula: PERCENTILE.INC(B2:B10, 0.5)This example would return the 50th percentile of the values in the range B2:B10.

PERCENTRANK.EXC

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

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

Formula: PERCENTRANK.EXC(A2:A10, A7, 4)In this example, the PERCENTRANK.EXC function returns the rank of the value in cell A7 (which is 5) as a percentage of the values in the range A2:A10. The third argument, 4, specifies that the result should be calculated to four decimal places.

PERCENTRANK.INC

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

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

Formula: PERCENTRANK.INC(A2:A8,A4,4)In this example, the PERCENTRANK.INC function would return the rank of the value in cell A4 as a percentage of the data set in cells A2 to A8, with 4 decimal places of significance.

PERMUT

The PERMUT function is used to calculate the number of permutations for a given number of objects. A permutation is the arrangement of a set of objects in a specific order.

Syntax: PERMUT(number, number_chosen)

Formula: To calculate the number of permutations of 8 objects taken 3 at a time, the formula would be: =PERMUT(8,3)The result would be 336.

PERMUTATIONA

The PERMUTATIONA function returns the number of permutations for a given number of objects that can be selected from number objects.

Syntax: PERMUTATIONA(number, number_chosen)

Formula: PERMUTATIONA(5,2)This example returns the number of permutations for 5 objects that can be selected from 2 objects, which is 10.

PHI

PHI is a statistical function in Excel that calculates the value of the Phi coefficient, which is a measure of association between two binary variables. It is often used to measure the strength of a linear relationship between two variables.

Syntax: PHI(x, y)

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

POISSON.DIST

The POISSON.DIST function in Excel returns the probability of a given number of events occurring in a fixed period of time when the rate of events is known.

Syntax: POISSON.DIST(x,mean,cumulative)

Formula: POISSON.DIST(5,3,FALSE)This formula returns the probability of 5 events occurring in a given period of time when the rate of events is 3.

PROB

The PROB function in Microsoft Excel is used to calculate the probability of a given event. It returns the probability that values in a range are between two limits.

Syntax: PROB(x_range,prob_range,lower_limit,upper_limit)

Formula: PROB(A1:A5,B1:B5,20,30)

QUARTILE.EXC

The QUARTILE.EXC function returns the quartile of a dataset, which is a type of statistical measure.

Syntax: QUARTILE.EXC(array, quart)

Formula: QUARTILE.EXC(A1:A10,3)This example returns the third quartile of the data in cells A1 through A10.

QUARTILE.INC

The QUARTILE.INC function returns the quartile of a given data set, where quartiles divide a ranked set of data into four equal parts.

Syntax: QUARTILE.INC(array, quart)Array: The array or range of cells containing numerical data for which you want the quartile value.Quart: A number specifying which quartile value to return.

Formula: The formula =QUARTILE.INC(A1:A10,2) returns the second quartile (median) of the data set in cells A1:A10.

RANK.AVG

The RANK.AVG function returns the rank of a number in a list of numbers. It assigns the same rank to numbers with the same value.

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

Formula: RANK.AVG(B2,B2:B7,1)This example returns the rank of the number in cell B2 in the range B2:B7, in ascending order.

RANK.EQ

The RANK.EQ function returns the rank of a number in a list of numbers.

Syntax: RANK.EQ (number, reference, [order])

Formula: RANK.EQ (5, {1,2,3,5,6,7}, 0)The example returns 4, as 5 is the 4th number in the list.

RSQ

RSQ is a statistical function in Microsoft Excel that returns the square of the Pearson product-moment correlation coefficient (R-squared) of two supplied sets of values. It is used to measure how closely two sets of data points fit a given regression line.

Syntax: RSQ(known_y’s, known_x’s)

Formula: RSQ(A1:A6,B1:B6)

SKEW

SKEW: The SKEW function returns the skewness of a distribution based on a supplied set of values. Skewness is a measure of asymmetry in a probability distribution.

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

Formula: SKEW(A2:A10)This function will return the skewness of the distribution of the values in cells A2 through A10.

SKEW.P

SKEW.P is a statistical function in Microsoft Excel that calculates the skewness of a distribution based on a population of numerical data.

Syntax: SKEW.P(number1,[number2],…)

Formula: SKEW.P(2,4,6,8,10)The result of this formula is 0, indicating that the data set is perfectly symmetrical.

SLOPE

The SLOPE function calculates the slope of a regression line based on the given data points. It is used to measure the rate of change in a data set.

Syntax: SLOPE (known_y’s, known_x’s)

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

SMALL

The SMALL function returns the nth smallest value from a given set of values.

Syntax: SMALL(array, nth_smallest)

Formula: SMALL(A1:A10, 3)This example returns the 3rd smallest value from the range A1:A10.

STANDARDIZE

The STANDARDIZE function returns a normalized value from a distribution characterized by a mean and standard deviation.

Syntax: STANDARDIZE(x, mean, standard_dev)

Formula: STANDARDIZE(3, 5, 2)This example returns -1, which is the normalized value from a distribution with a mean of 5 and a standard deviation of 2.

STDEV.P

STDEV.P is an Excel function that calculates the standard deviation of a population based on a sample of numeric values.

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

Formula: STDEV.P(1,2,3,4,5)This example would return 1.58113883008419.

STDEV.S

STDEV.S is an Excel function that calculates the standard deviation of a sample based on a supplied set of values.

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

Formula: STDEV.S(2,4,6,8)This will return 2, the standard deviation of the sample set.

STDEVA

STDEVA is an Excel function that returns the standard deviation of a population based on a sample of numeric values.

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

Formula: STDEVA(2,4,6,8,10)This example returns 2.58198889747161, which is the standard deviation of the given sample.

STDEVPA

STDEVPA is an Excel function that calculates the standard deviation of a population based on a sample of numbers. It takes the following syntax:STDEVPA(number1, [number2], …)Where number1, number2, etc. are the numeric values for which you want to calculate the standard deviation.

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

Formula: STDEVPA(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)This will return 2.87228132326901.

STEYX

STEYX is a statistical function in Microsoft Excel that calculates the standard error of the predicted y-value for each x in the regression.

Syntax: STEYX(known_y’s, known_x’s)

Formula: STEYX(A2:A6, B2:B6)

T.DIST

T.DIST is an Excel function that returns the probability associated with a Student’s t-distribution.

Syntax: T.DIST(x,deg_freedom,cumulative)

Formula: T.DIST(1.5,5,TRUE)This example returns the cumulative probability associated with a t-distribution with a value of 1.5 and 5 degrees of freedom.

T.DIST.2T

The T.DIST.2T function returns the probability associated with a Student’s t-distribution. It can be used to determine the probability of a given range of values.

Syntax: T.DIST.2T(x,deg_freedom,cumulative)

Formula: To calculate the probability of obtaining a value of 2 or less in a t-distribution with 20 degrees of freedom, the formula would be =T.DIST.2T(2,20,TRUE). This will return the probability of 0.97725.

T.DIST.RT

The T.DIST.RT function returns the right-tailed Student’s t-distribution. The function is used to calculate probabilities for a given value of the t-distribution.

Syntax: T.DIST.RT(x,deg_freedom,cumulative)

Formula: T.DIST.RT(2,5,TRUE)This example returns the cumulative probability of 2 for a t-distribution with 5 degrees of freedom.

T.INV

T.INV is an Excel function that returns the t-value of the Student’s t-distribution, given the probability and the degrees of freedom.

Syntax: T.INV(probability, degrees_of_freedom)

Formula: T.INV(0.95, 10)This example returns the t-value of 0.816496580927726, which is the t-value for a 95% confidence interval with 10 degrees of freedom.

T.INV.2T

The T.INV.2T function returns the two-tailed probability of the Student’s t-distribution.

Syntax: T.INV.2T(probability,deg_freedom)

Formula: T.INV.2T(0.05,10)This example returns the two-tailed probability of the Student’s t-distribution with a probability of 0.05 and 10 degrees of freedom. The result is 2.228.

T.TEST

T.TEST is an Excel statistical function used to calculate the probability associated with the Student’s t-test. The syntax for the T.TEST function is:T.TEST(array1, array2, tails, type)Where:• array1 – The first array or range of data.• array2 – The second array or range of data.• tails – The number of tails in the t-test. This can either be 1 (for one-tailed) or 2 (for two-tailed).• type – The type of t-test to be performed. This can either be 1 (for paired) or 2 (for two-sample equal variance).

Syntax: T.TEST(array1,array2,tails,type)

Formula: T.TEST(A1:A10, B1:B10, 2, 1)This example would calculate the two-tailed probability associated with the paired t-test of the data in the range A1:A10 and B1:B10.

TREND

The TREND function is used to calculate linear trend line to a given set of data points and extend it to new data points.

Syntax: TREND(known_y’s, [known_x’s], [new_x’s], [const])

Formula: TREND(A2:A5, B2:B5, B6, FALSE)This example would calculate the linear trend line for the data points in cells A2 to A5, using the corresponding x values in cells B2 to B5. It would then extend the trend line to the x value in cell B6. The FALSE argument specifies that the trend line should not include a constant.

TRIMMEAN

The TRIMMEAN function is an Excel function used to calculate the mean of a dataset after a certain percentage of the data points have been excluded from the calculation.

Syntax: TRIMMEAN(array, percent)

Formula: TRIMMEAN(A2:A8, 0.2)This example would calculate the mean of the dataset in the range A2:A8, excluding the top and bottom 20% of the data points.

VAR.P

VAR.P is a statistical function in Excel that calculates the population variance of a given set of values.

Syntax: VAR.P(value1, [value2], …)

Formula: VAR.P(1,2,3,4,5)This example would return 2.5, which is the population variance of the set of values (1,2,3,4,5).

VAR.S

VAR.S is an Excel function that calculates the sample variance of a population based on a sample of numbers.

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

Formula: VAR.S(2, 4, 6, 8, 10)This example would calculate the sample variance of the population of numbers (2, 4, 6, 8, 10) and return 8.

VARA

The VARA function in Excel returns the variance of an array or range of numbers.

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

Formula: VARA(5, 10, 15, 20)This example will return the variance of the numbers 5, 10, 15, and 20, which is 25.

VARPA

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

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

Formula: VARPA(2,4,6,8,10)Result: 8

WEIBULL.DIST

WEIBULL.DIST is an Excel function used to calculate the probability that a value is less than or equal to a given value in a Weibull distribution.

Syntax: WEIBULL.DIST(x,alpha,beta,cumulative)x: The value for which you want to calculate the distribution.alpha: The shape parameter of the distribution.beta: The scale parameter of the distribution.cumulative: A logical value that determines the form of the function. If cumulative is TRUE, WEIBULL.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function.

Formula: WEIBULL.DIST(3,2,4,TRUE)This formula will return the cumulative probability that a value is less than or equal to 3 in a Weibull distribution with a shape parameter of 2 and a scale parameter of 4.

Z.TEST

The Z.TEST function in Excel is used to calculate the one-tailed or two-tailed probability-value of a z-test.

Syntax: Z.TEST(array, x, sigma)

Formula: Z.TEST(A2:A10, 7, 2)This example would calculate the one-tailed or two-tailed probability-value of a z-test using the data in cells A2 through A10, with a mean of 7 and a standard deviation of 2.