Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Category: Statistical Functions

Excel provides a variety of Statistical Functions that helps in analyzing data within a worksheet

AVEDEV function: Description, Usage, Syntax, Examples and Explanation

What is AVEDEV function in Excel? AVEDEV function is one of Statistical functions in Microsoft Excel that returns the average of the absolute deviations of data points from their mean. AVEDEV is a measure of the variability in a data set. Syntax of AVEDEV function AVEDEV(number1, [number2], …) The AVEDEV function syntax has the following arguments: Number1, number2, …    Number1 is required,…

AVERAGE function: Description, Usage, Syntax, Examples and Explanation

What is AVERAGE function in Excel? MODE.SNGL function is one of Statistical functions in Microsoft Excel that 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. Syntax of AVERAGE function AVERAGE(number1, [number2], …) The AVERAGE function syntax has the following arguments: Number1    Required. The first number, cell reference,…

AVERAGEA function: Description, Usage, Syntax, Examples and Explanation

What is AVERAGEA function in Excel? AVERAGEA function is one of Statistical functions in Microsoft Excel that calculates the average (arithmetic mean) of the values in the list of arguments. Syntax of AVERAGEA function AVERAGEA(value1, [value2], …) The AVERAGEA function syntax has the following arguments: Value1, value2, …    Value1 is required, subsequent values are optional. 1 to 255 cells, ranges of cells,…

AVERAGEIF function: Description, Usage, Syntax, Examples and Explanation

What is AVERAGEIF function in Excel? AVERAGEIF function is one of Statistical functions in Microsoft Excel that returns the average (arithmetic mean) of all the cells in a range that meet a given criteria. Syntax of AVERAGEIF function AVERAGEIF(range, criteria, [average_range]) The AVERAGEIF function syntax has the following arguments: Range    Required. One or more cells to average, including numbers or names, arrays,…

AVERAGEIFS function: Description, Usage, Syntax, Examples and Explanation

What is AVERAGEIFS function in Excel? AVERAGEIFS function is one of Statistical functions in Microsoft Excel that returns the average (arithmetic mean) of all cells that meet multiple criteria. Syntax of AVERAGEIFS function AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) The AVERAGEIFS function syntax has the following arguments: Average_range    Required. One or more cells to average, including numbers or names, arrays, or references…

BETA.INV function: Description, Usage, Syntax, Examples and Explanation

What is BETA.INV function in Excel? BETA.INV function is one of Statistical functions in Microsoft Excel that returns the inverse of the beta cumulative probability density function (BETA.DIST). If probability = BETA.DIST(x,…TRUE), then BETA.INV(probability,…) = x. The beta distribution can be used in project planning to model probable completion times given an expected completion time and variability. Syntax of BETA.INV function…

BETA.DIST function: Description, Usage, Syntax, Examples and Explanation

What is BETA.DIST function in Excel? BETA.DIST function is one of Statistical functions in Microsoft Excel that 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. Syntax of BETA.DIST function BETA.DIST(x,alpha,beta,cumulative,[A],[B]) The BETA.DIST function syntax has the following arguments:…

BINOM.DIST function: Description, Usage, Syntax, Examples and Explanation

What is BINOM.DIST function in Excel? BINOM.DIST function is one of Statistical functions in Microsoft Excel that returns the individual term binomial distribution probability. Use BINOM.DIST 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…

BINOM.DIST.RANGE function: Description, Usage, Syntax, Examples and Explanation

What is BINOM.DIST.RANGE function in Excel? BINOM.DIST.RANGE function is one of Statistical functions in Microsoft Excel that returns the probability of a trial result using a binomial distribution. Syntax of BINOM.DIST.RANGE function BINOM.DIST.RANGE(trials,probability_s,number_s,[number_s2]) The BINOM.DIST.RANGE function syntax has the following arguments. Trials    Required. The number of independent trials. Must be greater than or equal to 0. Probability_s    Required. The probability of…

BINOM.INV function: Description, Usage, Syntax, Examples and Explanation

What is BINOM.INV function in Excel? BINOM.INV function is one of Statistical functions in Microsoft Excel that returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value. Syntax of BINOM.INV function BINOM.INV(trials,probability_s,alpha) The BINOM.INV function syntax has the following arguments: Trials     Required. The number of Bernoulli trials. Probability_s     Required. The probability of…

COUNT function: Description, Usage, Syntax, Examples and Explanation

What is COUNT function in Excel? COUNT function is one of Statistical functions in Microsoft Excel that  is used counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can…

CORREL function: Description, Usage, Syntax, Examples and Explanation

What is CORREL function in Excel? CORREL function is one of Statistical functions in Microsoft Excel that returns the correlation coefficient of the Array1 and Array2 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. Syntax of CORREL function…

CONFIDENCE.T function: Description, Usage, Syntax, Examples and Explanation

What is CONFIDENCE.T function in Excel? CONFIDENCE.T function is one of Statistical functions in Microsoft Excel that returns the confidence interval for a population mean, using a Student’s t distribution. Syntax of CONFIDENCE.T function CONFIDENCE.T(alpha,standard_dev,size) The CONFIDENCE.T function syntax has the following arguments: Alpha     Required. The significance level used to compute the confidence level. The confidence level equals 100*(1 – alpha)%,…

CHISQ.TEST function: Description, Usage, Syntax, Examples and Explanation

What is CHISQ.TEST function in Excel? CHISQ.TEST function is one of Statistical functions in Microsoft Excel that returns the test for independence. CHISQ.TEST 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. Syntax of CHISQ.TEST function CHISQ.TEST(actual_range,expected_range) The…

CHISQ.INV.RT function: Description, Usage, Syntax, Examples and Explanation

What is CHISQ.INV.RT function in Excel? CHISQ.INV.RT function is one of Statistical functions in Microsoft Excel that 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. Syntax of CHISQ.INV.RT function CHISQ.INV.RT(probability,deg_freedom)…

CHISQ.INV function: Description, Usage, Syntax, Examples and Explanation

What is CHISQ.INV function in Excel? CHISQ.INV function is one of Statistical functions in Microsoft Excel that 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. Syntax of CHISQ.INV function CHISQ.INV(probability,deg_freedom) The CHISQ.INV…

CHISQ.DIST.RT function: Description, Usage, Syntax, Examples and Explanation

What is CHISQ.DIST.RT function in Excel? CHISQ.DIST.RT function is one of Statistical functions in Microsoft Excel that 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…

CHISQ.DIST function: Description, Usage, Syntax, Examples and Explanation

What is CHISQ.DIST function in Excel? CHISQ.DIST function is one of Statistical functions in Microsoft Excel that 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. Syntax of CHISQ.DIST function CHISQ.DIST(x,deg_freedom,cumulative) The CHISQ.DIST function syntax has the following arguments:…

F.INV function: Description, Usage, Syntax, Examples and Explanation

What is F.INV function in Excel? F.INV function is one of Statistical functions in Microsoft Excel that 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…

F.DIST.RT function: Description, Usage, Syntax, Examples and Explanation

What is F.DIST.RT function in Excel? F.DIST.RT function is one of Statistical functions in Microsoft Excel that 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…

F.DIST function: Description, Usage, Syntax, Examples and Explanation

What is F.DIST function in Excel? F.DIST function is one of Statistical functions in Microsoft Excel that 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…

EXPON.DIST function: Description, Usage, Syntax, Examples and Explanation

What is EXPON.DIST function in Excel? EXPON.DIST function is one of Statistical functions in Microsoft Excel that returns the exponential distribution. Use EXPON.DIST to model the time between events, such as how long an automated bank teller takes to deliver cash. For example, you can use EXPON.DIST to determine the probability that the process takes at most 1 minute. Syntax…

DEVSQ function: Description, Usage, Syntax, Examples and Explanation

What is DEVSQ function in Excel? DEVSQ function is one of Statistical functions in Microsoft Excel that returns the sum of squares of deviations of data points from their sample mean. Syntax of DEVSQ function DEVSQ(number1, [number2], …) The DEVSQ function syntax has the following arguments: Number1, number2, …    Number1 is required, subsequent numbers are optional. 1 to 255 arguments for which…

COVARIANCE.S function: Description, Usage, Syntax, Examples and Explanation

What is COVARIANCE.S function in Excel? COVARIANCE.S function is one of Statistical functions in Microsoft Excel that returns the sample covariance, the average of the products of deviations for each data point pair in two data sets. Syntax of COVARIANCE.S function COVARIANCE.S(array1,array2) The COVARIANCE.S function syntax has the following arguments: Array1     Required. The first cell range of integers. Array2     Required. The…

COVARIANCE.P function: Description, Usage, Syntax, Examples and Explanation

What is COVARIANCE.P function in Excel? COVARIANCE.P function is one of Statistical functions in Microsoft Excel that 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. For example, you can examine whether greater income accompanies greater levels of education. Syntax…

COUNTIFS function: Description, Usage, Syntax, Examples and Explanation

What is COUNTIFS function in Excel? COUNTIFS function is one of Statistical functions in Microsoft Excel applies criteria to cells across multiple ranges and counts the number of times all criteria are met. Syntax of COUNTIFS function COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) The COUNTIFS function syntax has the following arguments: criteria_range1    Required. The first range in which to evaluate the associated criteria. criteria1    Required.…

COUNTBLANK function: Description, Usage, Syntax, Examples and Explanation

What is COUNTBLANK function in Excel? COUNTBLANK function is one of Statistical functions in Microsoft Excel used to count the number of empty cells in a range of cells. Syntax of COUNTBLANK function COUNTBLANK(range) The COUNTBLANK function syntax has the following arguments: Range    Required. The range from which you want to count the blank cells. COUNTBLANK formula explanation Cells with formulas that return “” (empty text) are also…

FREQUENCY function: Description, Usage, Syntax, Examples and Explanation

What is FREQUENCY function in Excel? FREQUENCY function is one of Statistical functions in Microsoft Excel that 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…

FORECAST function: Description, Usage, Syntax, Examples and Explanation

What is FORECAST function in Excel? FORECAST function is one of Statistical functions in Microsoft Excel that 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…

FISHERINV function: Description, Usage, Syntax, Examples and Explanation

What is FISHERINV function in Excel? FISHERINV function is one of Statistical functions in Microsoft Excel that 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. Syntax of FISHERINV function FISHERINV(y) The FISHERINV function syntax has the following arguments: Y    Required. The value for…

FISHER function: Description, Usage, Syntax, Examples and Explanation

What is FISHER function in Excel? FISHER function is one of Statistical functions in Microsoft Excel that 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. Syntax of FISHER function FISHER(x) The FISHER function syntax has the following arguments: X: A…

F.TEST function: Description, Usage, Syntax, Examples and Explanation

What is F.TEST function in Excel? F.TEST function is one of Statistical functions in Microsoft Excel that returns the result of an F-test, 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…

GROWTH function: Description, Usage, Syntax, Examples and Explanation

What is GROWTH function in Excel? GROWTH function is one of Statistical functions in Microsoft Excel that 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…

GEOMEAN function: Description, Usage, Syntax, Examples and Explanation

What is GEOMEAN function in Excel? GEOMEAN function is one of Statistical functions in Microsoft Excel that 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. Syntax of GEOMEAN function GEOMEAN(number1, [number2], …) The GEOMEAN function syntax has the following arguments:…

GAUSS function: Description, Usage, Syntax, Examples and Explanation

What is GAUSS function in Excel? GAUSS function is one of Statistical functions in Microsoft Excel that calculates the probability that a member of a standard normal population will fall between the mean and z standard deviations from the mean. Syntax of GAUSS function GAUSS(z) The GAUSS function syntax has the following arguments. Z    Required. Returns a number. GAUSS formula explanation…

GAMMALN.PRECISE function: Description, Usage, Syntax, Examples and Explanation

What is GAMMALN.PRECISE function in Excel? GAMMALN.PRECISE function is one of Statistical functions in Microsoft Excel that returns the natural logarithm of the gamma function, Γ(x). Syntax of GAMMALN.PRECISE function GAMMALN.PRECISE(x) The GAMMALN.PRECISE function syntax has the following arguments: X    Required. The value for which you want to calculate GAMMALN.PRECISE. GAMMALN.PRECISE formula explanation If x is nonnumeric, GAMMALN.PRECISE returns the #VALUE! error…

GAMMALN function: Description, Usage, Syntax, Examples and Explanation

What is GAMMALN function in Excel? GAMMALN function is one of Statistical functions in Microsoft Excel that returns the natural logarithm of the gamma function, Γ(x). Syntax of GAMMALN function GAMMALN(x) The GAMMALN function syntax has the following arguments: X    Required. The value for which you want to calculate GAMMALN. GAMMALN formula explanation If x is nonnumeric, GAMMALN returns the #VALUE! error…

GAMMA.INV function: Description, Usage, Syntax, Examples and Explanation

What is GAMMA.INV function in Excel? GAMMA.INV function is one of Statistical functions in Microsoft Excel that 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. Syntax of GAMMA.INV function GAMMA.INV(probability,alpha,beta) The GAMMA.INV function syntax has the following arguments: Probability    …