53 functions in this category · showing 111 available
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.
Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values. The intercept point is based on a best-fit regression line plotted through the known x-values and known y-values. Use the INTERCEPT function when you want to determine the value of the dependent variable when the independent variable is 0 (zero). For example, you can use the INTERCEPT function to predict a metal's electrical resistance at 0°C when your data points were taken at room temperature and higher.
Returns the largest value in a set of values.
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.
How to use the COUNTIFS function in Excel to apply criteria to cells, across multiple ranges, and count the number of times all criteria are met.
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.
BINOM.DIST returns the individual term binomial distribution probability.
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.
The MAXIFS function returns the maximum value among cells specified by a given set of conditions or criteria: numbers, dates, text, etc. This feature is available on Windows or Mac if you have Office 2019, or if you have a Microsoft 365 subscription. If you are a Microsoft 365 subscriber, make sure you have the latest version of Office.
Returns the smallest number in a set of values.
PERCENTILE.INC returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive. You can use this function to establish a threshold of acceptance.
Calculates standard deviation based on the entire population given as arguments, including text and logical values. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).
Returns the Weibull distribution. Use this distribution in reliability analysis, such as calculating a device's mean time to failure.
Returns the probability of a trial result using a binomial distribution.
Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.
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.
Returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.
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.
Returns the inverse of the right-tailed probability of the chi-squared distribution. If probability = CHISQ.DIST.RT(x,...), then CHISQ.INV.RT(probability,...) = x. Use this function to compare observed results with expected ones in order to decide whether your original hypothesis is valid.
Calculates the probability that a member of a standard normal population will fall between the mean and z standard deviations from the mean.
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 = LOGNORM.DIST(x,...) then LOGNORM.INV(p,...) = x. Use the lognormal distribution to analyze logarithmically transformed data.
Returns the probability associated with a Student's 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. Results from the test shows if the difference is statistically significant or from chance.
Returns the right-tailed probability of the chi-squared distribution. The χ2 distribution is associated with a χ2 test. Use the χ2 test to compare observed and expected values. For example, a genetic experiment might hypothesize that the next generation of plants will exhibit a certain set of colors. By comparing the observed results with the expected ones, you can decide whether your original hypothesis is valid.
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.
Returns the length of the repetitive pattern Excel detects for the specified time series.
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. NEGBINOM.DIST calculates the probability that you will interview a certain number of unqualified candidates before finding all 10 qualified candidates.
NORM.S.DIST returns the standard normal distribution (has a mean of zero and a standard deviation of one). Use this function in place of a table of standard normal curve areas.
Calculates standard deviation based on the entire population given as arguments (ignores logical values and text).
Returns the left-tailed inverse of the Student's t-distribution.
Calculates the average (arithmetic mean) of the values in the list of arguments.
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.
Returns the exponential distribution.
rticle describes the formula syntax and usage of the F.DIST.RT function in Microsoft Excel. Returns the (right-tailed) F probability distribution (degree of diversity) for two data sets. 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.
Returns a confidence interval for the forecast value at the specified target date.
The LINEST function calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits your data, and then returns an array that describes the line. 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.
Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data.
Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects.
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.
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.
Estimates variance based on a sample.
Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value.
The CHISQ.TEXT function returns the test for independence. It returns the value from the chi-squared (χ2) distribution for the statistic and the appropriate degrees of freedom. You can use χ2 tests to determine whether hypothesized results are verified by an experiment.
Use the COUNTBLANK function, one of the Statistical functions, to count the number of empty cells in a range of cells.
Conditional count (a single condition)
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.
Returns the inverse of the gamma cumulative distribution. If p = GAMMA.DIST(x,...), then GAMMA.INV(p,...) = x. You can use this function to study a variable whose distribution may be skewed.
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.
Returns the largest value in a list of arguments. MAXA is similar to MINA. For more information, see the examples for the MINA function.
Returns the median of the given numbers. The median is the number in the middle of a set of numbers.
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. If mean or standard_dev is nonnumeric, NORM.DIST returns the #VALUE! error value.
Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one.
The PERCENTILE.EXC function returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive.
Returns the two-tailed Student's t-distribution. The Student's t-distribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the t-distribution.
Returns the right-tailed Student's 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.
Returns the two-tailed inverse of the Student's t-distribution.
.DIST(x,alpha,beta,cumulative,[A],[B])
The COUNTA function counts the number of cells that are not empty in a range.
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.
Return the gamma function value.
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.
Returns the probability of a given number of sample successes, given the sample size, population successes, and population size.
Returns the most frequently occurring, or repetitive, value in an array or range of data.
Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. This function is provided for backward compatibility; consider using the NORM.INV function instead.
Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry of a distribution around its mean. Positive skewness indicates a distribution with an asymmetric tail extending toward more positive values. Negative skewness indicates a distribution with an asymmetric tail extending toward more negative values.
1, number 2,… Number 1 is , subsequent numbers are Number 1, number 2,… are 1 to 254 numbers or names, arrays, or reference that contain numbers for which you want the population skewness.
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.
Returns a normalized value from a distribution characterized by mean and standard_dev.
Estimates standard deviation based on a sample (ignores logical values and text in the sample).
Returns the average of the absolute deviations of data points from their mean. AVEDEV is a measure of the variability in a data set.
Returns the average (arithmetic mean) of all cells that meet multiple criteria.
.INV(probability,alpha,beta,[A],[B])
Returns the confidence interval for a population mean, using a Student's t distribution.
How to use the COUNT function in Excel to count the number of cells that contain numbers, and to count numbers within a list of arguments.
Returns the inverse of the F probability distribution. If p = F.DIST(x,...), then F.INV(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.
Use this function to determine whether two samples have different variances.
The FREQUENCY function 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.
Returns the natural logarithm of the gamma function, Γ(x).
Returns the value of the density function for a standard normal distribution.
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.
QUARTLE.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.
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.
Returns the sample covariance, the average of the products of deviations for each data point pair in two data sets.
Returns the natural logarithm of the gamma function, Γ(x).
Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the arithmetic mean of reciprocals.
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}.
Calculates variance based on the entire population.
This article describes the formula syntax and usage of the Z.TEST, which returns the one-tailed P-value of a z-test.
Returns population covariance, the average of the products of deviations for each data point pair in two data sets. Use covariance to determine the relationship between two data sets.
Returns the sum of squares of deviations of data points from their sample mean.
Returns the inverse of the (right-tailed) F probability distribution. If p = F.DIST.RT(x,...), then F.INV.RT(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.
Calculates or predicts a future value based on existing (historical) values by using the AAA version of the Exponential Smoothing (ETS) algorithm. The predicted value is a continuation of the historical values in the specified target date, which should be a continuation of the timeline. You can use this function to predict future sales, inventory requirements, or consumer trends. This function requires the timeline to be organized with a constant step between the different points.
Returns the smallest value in the list of arguments.
Returns the rank of a value in a data set as a percentage (0..1, inclusive) of the data set.
Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.
Predicts a future value based on the linear trend of past data
QUARTILE.EXC returns the quartile of the data set, based on percentile values from 0..1, exclusive.
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.
The confidence interval is a range of values. Your sample mean, x, is at the center of this range and the range is x ± CONFIDENCE.NORM. For example, if x is the sample mean of delivery times for products ordered through the mail, x ± CONFIDENCE.NORM is a range of population means. For any population mean, μ0, in this range, the probability of obtaining a sample mean further from μ0 than x is greater than alpha; for any population mean, μ0, not in this range, the probability of obtaining a sample mean further from μ0 than x is less than alpha. In other words, assume that we use x, standard_dev, and size to construct a two-tailed test at significance level alpha of the hypothesis that the population mean is μ0. Then we will not reject that hypothesis if μ0 is in the confidence interval and will reject that hypothesis if μ0 is not in the confidence interval. The confidence interval does not allow us to infer that there is probability 1 – alpha that our next package will take a delivery time that is in the confidence interval.
Returns a statistical value as a result of time series forecasting.
Returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned. If you were to sort the list, the rank of the number would be its position.
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).
The TREND function returns values along a linear trend. It fits a straight line (using the method of least squares) to the array's known_y's and known_x's. TREND returns the y-values along that line for the array of new_x's that you specify.
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.
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.
Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set.
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.
Returns the rank of a number in a list of numbers: its size relative to other values in the list. If more than one value has the same rank, the average rank is returned.
Calculates variance based on the entire population (ignores logical values and text in the population).
The VAR.S function estimates variance based on a sample (ignores logical values and text in the sample).
The CORREL 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.
The MINIFS function returns the minimum value among cells specified by a given set of conditions or criteria.