Excel offers a wide range of statistical functions to analyse data and derive meaningful insights. Here are some of the most commonly used statistical functions:
Basic Statistical Functions:
Statistical Analysis Functions
Probability Distribution Function
Data Analysis Tool Pak:
Using Statistical Functions Effectively:
By effectively utilizing Excel’s statistical functions and the Data Analysis Toolpak, you can gain valuable insights from your data and make informed decisions
| Function | Version | Purpose | Arguments |
|---|---|---|---|
| AVEDEV | Excel 2003 | Get sum of squared deviations | number1 number2 … |
| AVERAGE | Excel 2003 | Get the average of a group of numbers | number1 number2 … |
| AVERAGEA | Excel 2003 | Get the average of a group of numbers and text | value1 value2 … |
| AVERAGEIF | Excel 2007 | Get the average of numbers that meet criteria. | range criteria average_range |
| AVERAGEIFS | Excel 2007 | Average cells that match multiple criteria | avg_rng range1 criteria1 range2 criteria2 … |
| BINOM.DIST | Excel 2010 | Get binomial distribution probability | number_s trials probability_s cumulative |
| BINOMDIST | Excel 2003 | Get binomial distribution probability | number_s trials probability_s cumulative |
| COUNT | Excel 2003 | Count numbers | value1 value2 … |
| COUNTA | Excel 2003 | Count the number of non-blank cells | value1 value2 … |
| COUNTBLANK | Excel 2003 | Count cells that are blank | range |
| COUNTIF | Excel 2003 | Count cells that match criteria | range criteria |
| COUNTIFS | Excel 2007 | Count cells that match multiple criteria | range1 criteria1 range2 criteria2 … |
| DEVSQ | Excel 2003 | Get sum of squared deviations | number1 number2 … |
| FORECAST | Excel 2003 | Predict value along a linear trend | x known_ys known_xs |
| FORECAST.ETS | Excel 2016 | Predict value with a seasonal trend | target_date values timeline seasonality data_completion aggregation |
| FORECAST.ETS.CONFINT | Excel 2016 | Get confidence interval for forecast value at given date | target_date values timeline confidence_level seasonality data_completion aggregation |
| FORECAST.ETS.SEASONALITY | Excel 2016 | Get length of the seasonal pattern | values timeline data_completion aggregation |
| FORECAST.ETS.STAT | Excel 2016 | Get statistical value related to forecasting | values timeline statistic_type seasonality data_completion aggregation |
| FORECAST.LINEAR | Excel 2016 | Predict value along a linear trend | x known_ys known_xs |
| FREQUENCY | Excel 2003 | Get the frequency of values in a data set | data_array bins_array |
| GEOMEAN | Excel 2003 | Calculate geometric mean | number1 number2 … |
| HARMEAN | Excel 2003 | Calculate harmonic mean | number1 number2 … |
| INTERCEPT | Excel 2003 | Get intercept of linear regression line | known_ys known_xs |
| LARGE | Excel 2003 | Get nth largest value | array k |
| LINEST | Excel 2003 | Get parameters of linear trend | known_ys known_xs const stats |
| MAX | Excel 2003 | Get the largest value | number1 number2 … |
| MAXA | Excel 2003 | Return largest value. | value1 value2 … |
| MAXIFS | Excel 2019 | Get maximum value with criteria | max_range range1 criteria1 range2 criteria2 … |
| MEDIAN | Excel 2003 | Get the median of a group of numbers | number1 number2 … |
| MIN | Excel 2003 | Get the smallest value. | number1 number2 … |
| MINA | Excel 2003 | Return smallest value. | value1 value2 … |
| MINIFS | Excel 2019 | Get minimum value with criteria | min_range range1 criteria1 range2 criteria2 … |
| MODE | Excel 2003 | Get most frequently occurring number | number1 number2 … |
| MODE.MULT | Excel 2010 | Get most frequently occurring numbers | number1 number2 … |
| MODE.SNGL | Excel 2010 | Get most frequently occurring number | number1 number2 … |
| NORM.DIST | Excel 2010 | Get values and areas for the normal distribution | x mean standard_dev cumulative |
| NORM.INV | Excel 2010 | Get the inverse of normal cumulative distribution | probability mean standard_dev |
| NORM.S.DIST | Excel 2010 | Get the standard normal CDF and PDF. | z cumulative |
| NORM.S.INV | Excel 2010 | Get inverse of the standard normal cumulative distribution | probability |
| PERCENTILE | Excel 2003 | Get kth percentile | array k |
| PERCENTILE.EXC | Excel 2010 | Get kth percentile | array k |
| PERCENTILE.INC | Excel 2010 | Get kth percentile | array k |
| PERCENTRANK | Excel 2003 | Get percentile rank, inclusive | array x significance |
| PERCENTRANK.EXC | Excel 2010 | Get percentile rank, exclusive | array x significance |
| PERCENTRANK.INC | Excel 2010 | Get percentile rank, inclusive | array x significance |
| PERMUT | Excel 2003 | Get number of permutations without repetitions | number number_chosen |
| PERMUTATIONA | Excel 2013 | Get number of permutations with repetitions | number number_chosen |
| QUARTILE | Excel 2003 | Get the quartile in a data set | array quart |
| QUARTILE.EXC | Excel 2010 | Get the quartile in a data set | array quart |
| QUARTILE.INC | Excel 2010 | Get the quartile in a data set | array quart |
| RANK | Excel 2003 | Rank a number against a range of numbers | number ref order |
| RANK.AVG | Excel 2010 | Rank a number against a range of numbers | number ref order |
| RANK.EQ | Excel 2010 | Rank a number against a range of numbers | number ref order |
| SKEW | Excel 2003 | Get skewness of a distribution | number1 number2 … |
| SKEW.P | Excel 2013 | Get skewness of a distribution based on population | number1 number2 … |
| SLOPE | Excel 2003 | Get slope of linear regression line | known_ys known_xs |
| SMALL | Excel 2003 | Get nth smallest value | array k |
| STANDARDIZE | Excel 2003 | Calculate a normalized value (z-score) | x mean standard_dev |
| STDEV | Excel 2003 | Get the standard deviation in a sample | number1 number2 … |
| STDEV.P | Excel 2010 | Get standard deviation of population | number1 number2 … |
| STDEV.S | Excel 2010 | Get the standard deviation in a sample | number1 number2 … |
| STDEVA | Excel 2003 | Get standard deviation in a sample | number1 number2 … |
| STDEVP | Excel 2003 | Get standard deviation of population | number1 number2 … |
| STDEVPA | Excel 2003 | Get standard deviation for a population | number1 number2 … |
| TRIMMEAN | Excel 2003 | Calculate mean excluding outliers | array percent |
| VAR | Excel 2003 | Get variation of a sample | number1 number2 … |
| VAR.P | Excel 2010 | Get variation of population | number1 number2 … |
| VAR.S | Excel 2010 | Get variation of a sample | number1 number2 … |
| VARA | Excel 2003 | Get variation of a sample | number1 number2 … |
| VARP | Excel 2003 | Get variation of a population | number1 number2 … |
| VARPA | Excel 2003 | Get variation of a population | number1 number2 … |