Excel provides a variety of Statistical Functions that helps in analyzing data within a worksheet
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 …
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 …
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 …
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 …
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 …
What is COUNTIF function in Excel? COUNTIF function is one of Statistical functions in Microsoft Excel used to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.In its simplest form, COUNTIF says: =COUNTIF(Where do you want to look?, What …
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 …
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 …
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 …
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 …
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, …
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 …
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, …
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 …
What is GAMMA.DIST function in Excel? GAMMA.DIST function is one of Statistical functions in Microsoft Excel that 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. Syntax of GAMMA.DIST function GAMMA.DIST(x,alpha,beta,cumulative) The GAMMA.DIST function syntax has the following …
What is GAMMA function in Excel? GAMMA function is one of Statistical functions in Microsoft Excel that return the gamma function value. Syntax of GAMMA function GAMMA(number) The GAMMA function syntax has the following arguments. Number Required. Returns a number. GAMMA formula explanation GAMMA uses the following equation: Г(N+1) = N * Г(N) If Number is …
What is MAX function in Excel? MAX function is one of Statistical functions in Microsoft Excel that returns the largest value in a set of values. Syntax of MAX function MAX(number1, [number2], …) The MAX function syntax has the following arguments: Number1, number2, … Number1 is required, subsequent numbers are optional. 1 to 255 numbers for which …
What is LOGNORM.INV function in Excel? LOGNORM.INV function is one of Statistical functions in Microsoft Excel that 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. …
What is LOGNORM.DIST function in Excel? LOGNORM.DIST function is one of Statistical functions in Microsoft Excel that 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. Syntax of LOGNORM.DIST function LOGNORM.DIST(x,mean,standard_dev,cumulative) The LOGNORM.DIST function syntax has the following …
What is LOGEST function in Excel? LOGEST function is one of Statistical functions in Microsoft Excel that r 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.The …
What is LINEST function in Excel? 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 …
What is LARGE function in Excel? LARGE function is one of Statistical functions in Microsoft Excel that 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. Syntax …
What is KURT function in Excel? KURT function is one of Statistical functions in Microsoft Excel that 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. Syntax of KURT …
What is INTERCEPT function in Excel? INTERCEPT function is one of Statistical functions in Microsoft Excel that 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 …
What is HYPGEOM.DIST function in Excel? HYPGEOM.DIST function is one of Statistical functions in Microsoft Excel that 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 …
What is HARMEAN function in Excel? NORM.DIST function is one of Statistical functions in Microsoft Excel that returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the arithmetic mean of reciprocals. Syntax of HARMEAN function HARMEAN(number1, [number2], …) The HARMEAN function syntax has the following arguments: Number1, number2, … Number1 is required, …
What is NORM.S.INV function in Excel? NORM.S.INV function is one of Statistical functions in Microsoft Excel that returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one. Syntax of NORM.S.INV function NORM.S.INV(probability) The NORM.S.INV function syntax has the following arguments: Probability: A probability corresponding …
What is NORM.S.DIST function in Excel? NORM.S.DIST function is one of Statistical functions in Microsoft Excel that 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. Syntax of NORM.S.DIST function – Standard Normal Distribution NORM.S.DIST(z,cumulative) …
What is NORM.INV function in Excel? NORM.INV function is one of Statistical functions in Microsoft Excel that returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. Syntax of NORM.INV function NORM.INV(probability,mean,standard_dev) The NORM.INV function syntax has the following arguments: Probability: A probability corresponding to the normal distribution. Mean: The arithmetic …
What is NORM.DIST function in Excel? NORM.DIST function is one of Statistical functions in Microsoft Excel that 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. Syntax of NORM.DIST function NORM.DIST(x,mean,standard_dev,cumulative) The NORM.DIST function syntax has the following arguments: X …
What is NEGBINOM.DIST function in Excel? NEGBINOM.DIST function is one of Statistical functions in Microsoft Excel that 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 …
What is MODE.SNGL function in Excel? MODE.SNGL function is one of Statistical functions in Microsoft Excel that returns the most frequently occurring, or repetitive, value in an array or range of data. Syntax of MODE.SNGL function MODE.SNGL(number1,[number2],…) The MODE.SNGL function syntax has the following arguments: Number1: The first argument for which you want to calculate the …
What is MODE.MULT function in Excel? MODE.MULT function is one of Statistical functions in Microsoft Excel that returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data. For horizontal arrays, use TRANSPOSE(MODE.MULT(number1,number2,…)).This will return more than one result if there are multiple modes. Because this function …
To test if all values in a range are at least a certain threshold value, you can use the COUNTIF function together with the NOT function. Formula =NOT(COUNTIF(range,”<65″)) Explanation In the example shown, the formula in G5 is: =NOT(COUNTIF(B5:F5,”<65″)) How this formula works At the core, this formula uses the COUNTIF function to count any …
To get the last relative position (i.e. last row, last column) for mixed data that contains no empty cells, you can use the COUNTA function. See example below: Formula =COUNTA(range) Explanation In the example shown, the formula in D5 is: =COUNTA(B4:B100) Last *relative* position When constructing more advanced formulas, it’s often necessary to figure out …
To confirm that a range of cells all have the same value, you can use a formula based on the COUNTIF function. See illustration below: Formula =COUNTIF(range,”<>value”)=0 Explanation In the example shown, the formula in C9 is: =COUNTIF(C5:C8,”<>ok”)=0 Note: this formula is not case-sensitive, you can find a case-sensitive formula here. How this formula works This …
This tutorial shows b.asic attendance tracking formula in Excel One way to track attendance is with simple formulas based on the COUNTIF function. Formula =COUNTIF(range,”x”) Explanation In the example shown, the formula in M5 is: =COUNTIF(C5:L5,”x”) How this formula works This formula simply uses COUNTIF with a criteria of “x” (not quotation marks) to count …
To calculate the max or min change in a set of data as shown, without using a helper column, you can use an array formula. See basic array formula example below: Formula {=MAX(range1-range2)} Explanation In the example, the formula in G6 is: {=MAX(C5:C12-D5:D12)} Note: this is an array formula and myst be entered with control + shift + …
This tutorial shows how to calculate a percentage based value capped at a specific amount using MIN function. See illustration below: Formula =MIN(A1*percent,1000) Explanation In the example shown, the formula in D6 is: =MIN(C6*10%,1000) How this formula works This formula takes advantage of the fact that the MIN function works fine with small sets of data, …
What is COUNTA function in Excel? The COUNTA function is one of the Statistical functions in Microsoft Excel which counts the number of cells that are not empty in a range. Syntax of COUNTA function COUNTA(value1, [value2], …) The COUNTA function syntax has the following arguments: value1 Required. The first argument representing the values that you want to count. value2, … Optional. Additional arguments representing the …