Tutorial on Excel Statistical Functions

In this tutorial we will learn the list of Excel Statistical functions and how to effectively utilize the statistical functions for the purpose of statistical operations that we require.

COUNT Function in Excel: COUNT Function in Excel returns the count of values in the cell which has only number or date values.

COUNTA Function in Excel: COUNTA Function in Excel returns the count of non-blanks cell. In other words, COUNTA Function count the cells only if the cells have some values in it.

COUNTBLANK Function in Excel: COUNTBLANK Function in Excel returns the number of blank cells. In other words, COUNTBLANK Function count the cells only if the cell have no values in it.

COUNTIF Function in Excel: COUNTIF Function in Excel can be used to count cells with dates, numbers, and text that match specific condition.

COUNTIFS Function in Excel: COUNTIFS function in Excel returns the count of cells that meet one or more conditions. COUNTIFS Function in Excel can be used to count cells with dates, numbers, and text that match one or more conditions.

FREQUENCY Function in Excel: Frequency Function in Excel returns the Frequency Distribution of the supplied data array according to the bins that we supply.

PERMUT Function in Excel: PERMUT function in Excel calculates the number of permutations of a specified number of objects. The general formula for permutation is

Tutorial on Excel Statistical Functions 1

PERMUTATIONA Function in Excel: PERMUTATIONA function in Excel calculates the number of permutations of a specified number of objects with repetitions.

The PERMUTATIONA Function of a,b,c returns the result as 9 (elements are abacbabccacb, aa, bb, cc).

 

PERCENTILE Function in Excel: PERCENTILE Function in Excel returns the value of Kth percentile for a supplied range of values and supplied k.

PERCENTILE.INC Function in Excel: PERCENTILE.INC Function in Excel returns the value of Kth percentile for a supplied range of values and supplied k. Where the value of k is between 0 and 1 and both the values are inclusive

PERCENTILE.EXC Function in Excel: PERCENTILE.EXC Function in Excel returns the value of Kth percentile for a supplied range of values and supplied k. Where the value of k is between 0 and 1 and both the values are excluded.

QUARTILE Function in Excel: QUARTILE Function in Excel returns the Quartile value for a supplied range of values. QUARTILE Functions in Excel returns minimum value, first quartile (25th Percentile), second quartile (50th Percentile), third quartile (75th Percentile) and max value (100th percentile).

QUARTILE.INC Function in Excel: QUARTILE.INC Function in Excel returns the Quartile value for a supplied range of values based on a percentile range of 0 to 1 (inclusive).

QUARTILE.EXC Function in Excel: QUARTILE.EXC Function in Excel returns the Quartile value for a supplied range of values based on a percentile range of 0 to 1 with both the values exclusive.

Rank Function in Excel: RANK Function in Excel returns the statistical rank of a given value, within a supplied array of values. If there are duplicate values in the list, these are given the same rank.

RANK.EQ Function in Excel: RANK.EQ Function in Excel calculates and returns the statistical rank of a given value. When the value occurs multiple time then same rank is allocated to that value. i.e. in case of tie same rank is allocated to that value

RANK.AVG Function in Excel: RANK.AVG Function in Excel calculates and returns the statistical rank of a given value. When the value occurs multiple time then average of the rank is allocated to that value. i.e. in case of tie average of rank is allocated to that value.

PERCENTRANK in Excel: PERCENTRANK Function in Excel calculates the relative position of a specified value i.e., rank of specified value in terms of percentage, within a set of values.

PERCENTRANK.INC Function in Excel: PERCENTRANK.INC Function in Excel calculates the relative position of a specified value i.e., rank of specified value in terms of percentage between 0 and 1 (inclusive), within a set of values.

PERCENTRANK.EXC Function in Excel: PERCENTRANK.EXC Function in Excel calculates the relative position of a specified value i.e., rank of specified value in terms of percentage between 0 and 1 with both values are exclusive, within a set of values.

AVERAGE Function in Excel: The AVERAGE function in Excel returns the arithmetic mean of a list of supplied numbers.

AVERAGEA Function in Excel: The AVERAGEA function in Excel returns the arithmetic mean of a list of supplied numbers.

AVERAGEIF Function in Excel: AVERAGEIF Function in Excel finds and returns the average of array that meets the specific condition.

AVERAGEIFS Function in Excel: AVERAGEIFS Function in Excel finds and returns the arithmetic mean or average of array that meets one or more conditions.

MEDIAN Function in Excel: MEDIAN function in Excel returns the statistical median (the middle value) of a list of supplied numbers.

MODE Function in Excel: MODE function in Excel returns the mode (most frequently occurring number) in a group of supplied arguments.

 

GEOMEAN Function in Excel: GEOMEAN Function in Excel calculates the geometric mean of a supplied set of values.

The geometric mean of a set of values x1, x2, …, xn is calculated by the formula

Tutorial on Excel Statistical Functions 2

 

HARMEAN Function in Excel: HARMEAN Function in Excel calculates the Harmonic mean of a supplied set of values.

The harmonic mean of a set of values x1, x2, …, xn is calculated by the formula

Tutorial on Excel Statistical Functions 3

 

TRIMMEAN Function in Excel: TRIMMEAN function in Excel calculates the trimmed mean or truncated mean of a supplied set of values.

SUMIF Function in Excel: SUMIF Function in Excel finds and returns the sum of supplied array that meets the specific condition.

SUMIFS Function in Excel: SUMIFS Function in Excel finds and returns the sum of array that meets one or more conditions.

AVEDEV Function in Excel: AVEDEV function in Excel calculates the average deviation of a supplied set of values. Average Deviation, measures the deviation of a set of points from their average.

The Average Deviation is calculated by the following equation:

Tutorial on Excel Statistical Functions 4

 

DEVSQ Function in Excel: DEVSQ function in Excel calculates the sum of the squared deviations from the supplied numbers.

The sum of squared Deviation is calculated by the following Formula

Tutorial on Excel Statistical Functions 5

 

STDEV Function in Excel: STDEV function in Excel calculates the sample standard deviation of a supplied set of values. Standard Deviation, measures the deviation of number from the average value.

Standard Deviation is calculated using formula

Tutorial on Excel Statistical Functions 6

 

STDEV.S Function in Excel: STDEV.S function in Excel calculates the sample standard deviation of a supplied set of values.

Sample Standard Deviation is calculated using formula
Tutorial on Excel Statistical Functions 7

 

STDEVA Function in Excel: STDEVA function in Excel calculates the sample standard deviation of a supplied set of values.

Sample Standard Deviation is calculated using formula

Tutorial on Excel Statistical Functions 8

 

STDEVP Function in Excel: STDEVP function in Excel calculates the population standard deviation of a supplied set of values. Standard Deviation, measures the deviation of number from the average value.

STDEV.P Function in Excel: STDEV.P function in Excel calculates the population standard deviation of a supplied set of values. Standard Deviation, measures the deviation of number from the average value.

STDEVPA Function in Excel: STDEVPA function in Excel calculates the population standard deviation of a supplied set of values.

Population Standard Deviation is calculated using formula

Tutorial on Excel Statistical Functions 9

 

VAR Function in Excel: VAR function in Excel calculates the sample variance of a supplied set of values. Variance, measures the deviation of number from the average value.

Sample Variance is calculated using formula

Tutorial on Excel Statistical Functions 10

 

VAR.S Function in Excel: VAR.S function in Excel calculates the sample variance of supplied set of values.

VARA Function in Excel: VARA function in Excel calculates the sample variance of a supplied set of values.

VARP Function in Excel: VARP function in Excel calculates the population variance of a supplied set of values. Variance, measures the deviation of number from the average value.

Population Variance is calculated using formula

Tutorial on Excel Statistical Functions 11

 

VAR.P Function in Excel: VAR.P function in Excel calculates the population variance of a supplied set of values. Variance, measures the deviation of number from the average value.

VARPA Function in Excel: VARPA function in Excel calculates the population variance of a supplied set of values.

COVAR Function in Excel: COVAR Function in Excel calculates the covariance of two supplied sets of values.

Covariance is the measure of correlation between two sets of variables. Covariance is calculated using formula

Tutorial on Excel Statistical Functions 12

 

COVARIANCE.P Function in Excel: COVARIANCE.P Function in Excel calculates the population covariance of two supplied sets of values.

 

COVARIANCE.S Function in Excel: COVARIANCE.S Function in Excel calculates the sample covariance of two supplied sets of values.

Covariance is the measure of correlation between two sets of variables. Sample Covariance is calculated using formula

Tutorial on Excel Statistical Functions 13

 

MAX Function in Excel: MAX function in Excel returns the largest value or maximum value from a supplied set of numeric values.

MAXA Function in Excel: MAXA function in Excel returns the largest value or maximum value from a supplied set of numeric values. Where Text value is considered as 0. Logical Value FALSE is considered as 0 and Logical Value TRUE is considered as 1.

MIN Function in Excel: MIN function in Excel returns the smallest value or minimum value from a supplied set of numeric values.

MINA Function in Excel: MINA function in Excel returns the smallest value or minimum value from a supplied set of numeric values. Where Text value is considered as 0. Logical Value FALSE is considered as 0 and Logical Value TRUE is considered as 1.

MAXIFS Function in Excel: MAXIFS function in Excel returns the Maximum value from the set of supplied numbers that meets some specific conditions.

MINIFS Function in Excel: MINIFS function in Excel returns the Minimum value from the set of supplied numbers that meets some specific conditions.

LARGE Function in Excel: LARGE Function in Excel returns the Kth largest value from an array of numeric values.

SMALL Function in Excel: SMALL Function in Excel returns the Kth Smallest value from an array of numeric values.

CONFIDENCE Function in Excel: Confidence Function in Excel returns the value that you can use to construct the confidence interval for a population mean.  Confidence Function uses a Normal Distribution to calculate a confidence value.

CONFIDENCE.NORM Function in Excel: CONFIDENCE.NORM Function in Excel returns the value that you can use to construct the confidence interval for a population mean.

CONFIDENCE.T Function in Excel: CONFIDENCE.T Function in Excel returns the value that you can use to construct the confidence interval for a population mean.  CONFIDENCE.T Function uses Student’s T-Distribution to calculate a confidence value.

BETADIST Function in Excel: BETADIST Function in Excel calculates the cumulative beta probability density function for a supplied set of parameters.

BETA.DIST Function in Excel: BETA.DIST Function in Excel calculates the cumulative beta probability distribution function or probability density function of beta distribution for a supplied set of parameters.

BETAINV function in Excel: BETAINV function in Excel calculates the inverse of the cumulative beta probability density function for a supplied probability.

BETA.INV function in Excel: BETA.INV function in Excel calculates the inverse of the cumulative beta probability density function for a supplied probability.

BINOMDIST function in Excel: BINOMDIST function in Excel returns the Binomial Distribution probability of a specified number of successes out of given number of trials. BINOMDIST function in Excel can be used to calculate Binomial Distribution Probability Mass Function and Binomial Cumulative Distribution Function.

Binomial Distribution Formula is shown below.

Tutorial on Excel Statistical Functions 14

 

BINOM.DIST Function in Excel: BINOM.DIST function in Excel returns the Binomial Distribution probability of a specified number of successes out of given number of trials.

BINOM.DIST.RANGE Function in Excel: BINOM.DIST.RANGE function in Excel returns the Binomial Distribution probability for the number of successes within a specified range from a specified number of trials.

NEGBINOMDIST Function in Excel: NEGBINOMDIST Function in Excel calculates the Negative Binomial Distribution for a given set of parameters. Negative binomial distribution gives the probability that there will be a specified number of failures before a required number of successes is achieved.

Negative Binomial Distribution Formula for rth success occurring on xth trial is shown below

Tutorial on Excel Statistical Functions 15

 

NEGBINOM.DIST Function in Excel: NEGBINOM.DIST Function in Excel calculates the Negative Binomial Distribution for a given set of parameters.it returns either probability mass function or the cumulative distribution function depending on input.

CORREL Function in Excel: CORREL Function in Excel calculates the Correlation Coefficient for two sets of values.

correlation coefficient for two sets of values, x and y, is given by the formula:

Tutorial on Excel Statistical Functions 16

 

PEARSON CORRELATION Function in Excel: PEARSON Function in Excel calculates the Correlation Coefficient for two sets of values.

KURT Function in Excel: KURT Function in Excel calculates the Kurtosis of supplied sets of values. The kurtosis is a measure of the peaked ness of the distribution of the data, relative to the normal distribution.

RSQ Function in Excel: RSQ Function in Excel calculates the R Square i.e. it calculates the square of the Pearson Product-Moment Correlation Coefficient for two supplied sets of values.

R SQUARE for two sets of values, x and y, is given by the formula:

Tutorial on Excel Statistical Functions 17

 

 

SKEW Function in Excel: SKEW Function in Excel calculates the skewness of the distribution of a supplied set of values.

A positive skew in which tail is extended towards right indicates that more values lie below the mean.

Tutorial on Excel Statistical Functions 18

A negative skew in which tail is extended towards left indicates that more values lie above the mean

 Tutorial on Excel Statistical Functions 19

 

SKEW.P Function in Excel: SKEW.P Function in Excel calculates the skewness of the population of a supplied set of values.

 

lets get started with Tutorial on Excel Statistical Functions

next Tutorial on Excel Statistical Functions