Statistical

This page presents procedures from class WorksheetFunction related to the theme Statistical

Kurt - Returns the kurtosis of a data set. Kurtosis characterizes the relative peakedness or flatness of a distribution compared with the normal distribution. Positive kurtosis indicates a relatively peaked distribution. Negative kurtosis indicates a relatively flat distribution.

Large - 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.

LinEst - Calculates the statistics for a line by using the least squares method to calculate a straight line that best fits your data, and returns an array that describes the line. Because this function returns an array of values, it must be entered as an array formula.

LogEst - In regression analysis, 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.

LogNorm_Dist - 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.

LogNorm_Inv - Returns the inverse of the lognormal cumulative distribution function. Use the lognormal distribution to analyze logarithmically transformed data.

Max - Returns the largest value in a set of values.

AveDev - Returns the average of the absolute deviations of data points from their mean. AveDev is a measure of the variability in a data set.

Average - Returns the average (arithmetic mean) of the arguments.

AverageIf - Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.

AverageIfs - Returns the average (arithmetic mean) of all cells that meet multiple criteria.

Beta_Dist - Returns the beta cumulative distribution function.

Beta_Inv - Returns the inverse of the cumulative distribution function for a specified beta distribution. That is, if probability = Beta_Dist(x,...), then Beta_Inv(probability,...

Binom_Dist - Returns the individual term binomial distribution probability.

Binom_Dist_Range - Returns the probability of a trial result using a binomial distribution.

Binom_Inv - Returns the inverse of the individual term binomial distribution probability.

BinomDist - Returns the individual term binomial distribution probability.

ChiSq_Dist - Returns the chi-squared distribution.

ChiSq_Dist_RT - Returns the right-tailed probability of the chi-squared distribution.

ChiSq_Inv - Returns the inverse of the left-tailed probability of the chi-squared distribution.

ChiSq_Inv_RT - Returns the inverse of the right-tailed probability of the chi-squared distribution.

ChiSq_Test - Returns the test for independence.

Confidence_Norm - Returns a value that you can use to construct a confidence interval for a population mean.

Confidence_T - Returns the confidence interval for a population mean, using a Student's t distribution.

Correl - Returns the correlation coefficient of the Arg1 and Arg2 cell ranges.

Count - Counts the number of cells that contain numbers and counts numbers within the list of arguments.

CountA - Counts the number of cells that are not empty and the values within the list of arguments.

CountBlank - Counts empty cells in a specified range of cells.

CountIf - Counts the number of cells within a range that meet the given criteria.

CountIfs - Counts the number of cells within a range that meet multiple criteria.

Covariance_P - Returns population covariance, the average of the products of deviations for each data point pair.

Covariance_S - Returns the sample covariance, the average of the products of deviations for each data point pair in two data sets.

CritBinom - Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value.

DevSq - Returns the sum of squares of deviations of data points from their sample mean.

F_Dist - Returns the F probability distribution.

F_Dist_RT - Returns the right-tailed 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.

F_Inv - Returns the inverse of the F probability distribution.

F_Inv_RT - Returns the inverse of the right-tailed F probability distribution. If p = F_DIST_RT(x,...), then F_INV_RT(p,...

F_Test - Returns the result of an F-test. An F-test returns the two-tailed probability that the variances in array1 and array2 are not significantly different. Use this function to determine whether two samples have different variances. For example, given test scores from public and private schools, you can test whether these schools have different levels of test score diversity.

Fisher - Returns the Fisher transformation at x. This transformation produces a function that is normally distributed rather than skewed. Use this function to perform hypothesis testing on the correlation coefficient.

FisherInv - Returns the inverse of the Fisher transformation. Use this transformation when analyzing correlations between ranges or arrays of data. If y = FISHER(x), then FISHERINV(y) = x.

Forecast_ETS - Calculates or predicts a future value based on existing (historical) values by using the AAA version of the Exponential Smoothing (ETS) algorithm.

Forecast_ETS_ConfInt - Returns a confidence interval for the forecast value at the specified target date.

Forecast_ETS_Seasonality - Returns the length of the repetitive pattern that Excel detects for the specified time series.

Forecast_ETS_STAT - Returns a statistical value as a result of time series forecasting.

Forecast_Linear - 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.

Frequency - Calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use Frequency to count the number of test scores that fall within ranges of scores. Because Frequency returns an array, it must be entered as an array formula.

Gamma - Returns the gamma function value.

Gamma_Dist - 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.

Gamma_Inv - Returns the inverse of the gamma cumulative distribution. If p = GAMMA_DIST(x,...), then GAMMA_INV(p,...

GammaLn_Precise - Returns the natural logarithm of the gamma function, Γ(x).

Gauss - Returns 0.5 less than the standard normal cumulative distribution.

GeoMean - 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.

Growth - Calculates predicted exponential growth by using existing data. Growth returns the y-values for a series of new x-values that you specify by using existing x-values and y-values. You can also use the Growth worksheet function to fit an exponential curve to existing x-values and y-values.

HarMean - Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the arithmetic mean of reciprocals.

HypGeom_Dist - 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.

Intercept - 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.

Median - Returns the median of the given numbers. The median is the number in the middle of a set of numbers.

Min - Returns the smallest number in a set of values.

NegBinom_Dist - Returns the negative binomial distribution. NegBinom_Dist returns the probability that there will be number_f failures before the number_s-th success, when the constant probability of a success is probability_s. 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.

Norm_Dist - Returns the normal distribution for the specified mean and standard deviation. This function has a wide range of applications in statistics, including hypothesis testing.

Norm_Inv - Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.

Norm_S_Dist - Returns the standard normal cumulative distribution function. The distribution has a mean of 0 (zero) and a standard deviation of one. Use this function in place of a table of standard normal curve areas.

Norm_S_Inv - Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of 0 (zero) and a standard deviation of one.

Pearson - Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from -1.0 to 1.0 inclusive and reflects the extent of a linear relationship between two data sets.

Percentile_Exc - Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive.

Percentile_Inc - 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 examine candidates who score above the 90th percentile.

PercentRank_Exc - Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set.

PercentRank_Inc - Returns the rank of a value in a data set as a percentage (0..1, inclusive) of the data set. This function can be used to evaluate the relative standing of a value within a data set. For example, you can use PercentRank_Inc to evaluate the standing of an aptitude test score among all scores for the test.

Permut - Returns the number of permutations for a given number of objects that can be selected from number objects. A permutation is any set or subset of objects or events where internal order is significant. Permutations are different from combinations, for which the internal order is not significant. Use this function for lottery-style probability calculations.

Permutationa - Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects.

Phi - Returns the value of the density function for a standard normal distribution.

Poisson_Dist - 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 one minute.

Prob - Returns the probability that values in a range are between two limits. If upper_limit is not supplied, returns the probability that values in x_range are equal to lower_limit.

Quartile_Exc - Returns the quartile of the data set, based on percentile values from 0..1, exclusive.

Quartile_Inc - 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.

Rank_Avg - Returns the rank of a number in a list of numbers; that is, its size relative to other values in the list. If more than one value has the same rank, the average rank is returned.

Rank_Eq - Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. If you were to sort the list, the rank of the number would be its position.

RSq - 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 Pearson. The r-squared value can be interpreted as the proportion of the variance in y attributable to the variance in x.

Skew - Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry of a distribution around its mean.

Skew_p - Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean.

Slope - 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.

Small - 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.

Standardize - Returns a normalized value from a distribution characterized by mean and standard_dev.

StDev_P - Calculates standard deviation based on the entire population given as arguments. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).

StDev_S - Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).

StEyx - 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.

T_Dist - Returns a Student t-distribution where a numeric value (x) is a calculated value of t for which the Percentage Points are computed.

T_Dist_2T - Returns the two-tailed Student t-distribution.

T_Dist_RT - Returns the right-tailed Student t-distribution where a numeric value (x) is a calculated value of t for which the Percentage Points are to be computed. 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.

T_Inv - Returns the left-tailed inverse of the Student t-distribution.

T_Inv_2T - Returns the t-value of the Student t-distribution as a function of the probability and the degrees of freedom.

T_Test - Returns the probability associated with a Student t-Test. Use T_Test to determine whether two samples are likely to have come from the same two underlying populations that have the same mean.

Trend - Returns values along a linear trend. Fits a straight line (using the method of least squares) to the arrays known_y's and known_x's. Returns the y-values along that line for the array of new_x's that you specify.

TrimMean - Returns the mean of the interior of a data set. TrimMean calculates the mean taken by excluding a percentage of data points from the top and bottom tails of a data set. You can use this function when you wish to exclude outlying data from your analysis.

Var_P - Calculates variance based on the entire population.

Var_S - Estimates variance based on a sample.

Z_Test - Returns the one-tailed probability-value of a z-test. For a given hypothesized population mean, Z_Test returns the probability that the sample mean would be greater than the average of observations in the data set (array); that is, the observed sample mean.