Advanced Excel - Statistical Functions
Statistical functions perform calculations ranging from basic mean, median & mode to the more complex statistical distribution and probability tests.
Statistical Functions
The following table lists all the Statistical functions −
S.No. | Function and Description |
---|---|
1 | AVEDEV
Returns the average of the absolute deviations of data points from their mean |
2 | AVERAGE
Returns the average of its arguments |
3 | AVERAGEA
Returns the average of its arguments and includes evaluation of text and logical values |
4 | AVERAGEIF
Returns the average for the cells specified by a given criterion |
5 | AVERAGEIFS
Returns the average for the cells specified by multiple criteria |
6 | BETA.DIST
Returns the beta cumulative distribution function |
7 | BETA.INV
Returns the inverse of the cumulative distribution function for a specified beta distribution |
8 | BINOM.DIST
Returns the individual term binomial distribution probability |
9 | BINOM.DIST.RANGE
Returns the probability of a trial result using a binomial distribution |
10 | BINOM.INV
Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value |
11 | CHISQ.DIST
Returns the cumulative beta probability density function |
12 | CHISQ.DIST.RT
Returns the one-tailed probability of the chi-squared distribution |
13 | CHISQ.INV
Returns the cumulative beta probability density function |
14 | CHISQ.INV.RT
Returns the inverse of the one-tailed probability of the chi-squared distribution |
15 | CHISQ.TEST
Returns the test for independence |
16 | CONFIDENCE.NORM
Returns the confidence interval for a population mean |
17 | CONFIDENCE.T
Returns the confidence interval for a population mean, using a Student's t distribution |
18 | CORREL
Returns the correlation coefficient between two data sets |
19 | COUNT
Counts how many numbers are in the list of arguments |
20 | COUNTA
Counts how many values are in the list of arguments |
21 | COUNTBLANK
Counts the number of blank cells in the argument range |
22 | COUNTIF
Counts the number of cells that meet the criteria you specify in the argument |
23 | COUNTIFS
Counts the number of cells that meet multiple criteria |
24 | COVARIANCE.P
Returns covariance, the average of the products of paired deviations |
25 | COVARIANCE.S
Returns the sample covariance, the average of the products deviations for each data point pair in two data sets |
26 | DEVSQ
Returns the sum of squares of deviations |
27 | EXPON.DIST
Returns the exponential distribution |
28 | F.DIST
Returns the F probability distribution |
29 | F.DIST.RT
Returns the F probability distribution |
30 | F.INV
Returns the inverse of the F probability distribution |
31 | F.INV.RT
Returns the inverse of the F probability distribution |
32 | F.TEST
Returns the result of an F-test |
33 | FISHER
Returns the Fisher transformation |
34 | FISHERINV
Returns the inverse of the Fisher transformation |
35 | FORECAST
Returns a value along a linear trend |
36 | FORECAST.ETS
Calculates a future value based on existing values using the Exponential Triple Smoothing (ETS) algorithm |
37 | FORECAST.ETS.CONFINT
Returns a confidence interval for the forecast value at the specified target date |
38 | FORECAST.ETS.SEASONALITY
Returns the length of the repetitive pattern detected for the specified time series |
39 | FORECAST.ETS.STAT
Returns a statistical value as a result of time series forecasting |
40 | FORECAST.LINEAR
Calculates a future value by using existing values, using linear regression. |
41 | FREQUENCY
Returns a frequency distribution as a vertical array |
42 | GAMMA
Returns the Gamma function value |
43 | GAMMA.DIST
Returns the gamma distribution |
44 | GAMMA.INV
Returns the inverse of the gamma cumulative distribution |
45 | GAMMALN
Returns the natural logarithm of the gamma function, G(x) |
46 | GAMMALN.PRECISE
Returns the natural logarithm of the gamma function, G(x) |
47 | GAUSS
Returns 0.5 less than the standard normal cumulative distribution |
48 | GEOMEAN
Returns the geometric mean |
49 | GROWTH
Returns values along an exponential trend |
50 | HARMEAN
Returns the harmonic mean |
51 | HYPGEOM.DIST
Returns the hypergeometric distribution |
52 | INTERCEPT
Returns the intercept of the linear regression line |
53 | KURT
Returns the kurtosis of a data set |
54 | LARGE
Returns the kth largest value in a data set |
55 | LINEST
Returns the parameters of a linear trend |
56 | LOGEST
Returns the parameters of an exponential trend |
57 | LOGNORM.DIST
Returns the cumulative lognormal distribution |
58 | LOGNORM.INV
Returns the inverse of the lognormal cumulative distribution |
59 | MAX
Returns the maximum value in a list of arguments, ignoring logical values and text |
60 | MAXA
Returns the maximum value in a list of arguments, including logical values and text |
61 | MAXIFS
Returns the maximum value among cells specified by a given set of conditions or criteria. |
62 | MEDIAN
Returns the median of the given numbers |
63 | MIN
Returns the minimum value in a list of arguments, ignoring logical values and text |
64 | MINA
Returns the minimum value in a list of arguments, including logical values and text |
65 | MINIFS
Returns the minimum value among cells specified by a given set of conditions or criteria. |
66 | MODE.MULT
Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data |
67 | MODE.SNGL
Returns the most common value in a data set |
68 | NEGBINOM.DIST
Returns the negative binomial distribution |
69 | NORM.DIST
Returns the normal cumulative distribution |
70 | NORM.INV
Returns the inverse of the normal cumulative distribution |
71 | NORM.S.DIST
Returns the standard normal cumulative distribution |
72 | NORM.S.INV
Returns the inverse of the standard normal cumulative distribution |
73 | PEARSON
Returns the Pearson product moment correlation coefficient |
74 | PERCENTILE.EXC
Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive |
75 | PERCENTILE.INC
Returns the k-th percentile of values in a range |
76 | PERCENTRANK.EXC
Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set |
77 | PERCENTRANK.INC
Returns the percentage rank of a value in a data set |
78 | PERMUT
Returns the number of permutations for a given number of objects |
79 | PERMUTATIONA
Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects |
80 | PHI
Returns the value of the density function for a standard normal distribution |
81 | POISSON.DIST
Returns the Poisson distribution |
82 | PROB
Returns the probability that values in a range are between two limits |
83 | QUARTILE.EXC
Returns the quartile of the data set, based on percentile values from 0..1, exclusive |
84 | QUARTILE.INC
Returns the quartile of a data set |
85 | RANK.AVG
Returns the rank of a number in a list of numbers |
86 | RANK.EQ
Returns the rank of a number in a list of numbers |
87 | RSQ
Returns the square of the Pearson product moment correlation coefficient |
88 | SKEW
Returns the skewness of a distribution |
89 | SKEW.P
Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean |
90 | SLOPE
Returns the slope of the linear regression line |
91 | SMALL
Returns the kth smallest value in a data set |
92 | STANDARDIZE
Returns a normalized value |
93 | STDEV.P
Calculates standard deviation based on the entire population |
94 | STDEV.S
Estimates standard deviation based on a sample |
95 | STDEVA
Estimates standard deviation based on a sample, including text and logical values |
96 | STDEVPA
Calculates standard deviation based on the entire population, including text and logical values |
97 | STEYX
Returns the standard error of the predicted y-value for each x in the regression |
98 | T.DIST
Returns the Percentage Points (probability) for the Student t-distribution |
99 | T.DIST.2T
Returns the Percentage Points (probability) for the Student t-distribution |
100 | T.DIST.RT
Returns the Student's t-distribution |
101 | T.INV
Returns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom |
102 | T.INV.2T
Returns the inverse of the Student's t-distribution |
103 | T.TEST
Returns the probability associated with a Student's t-test |
104 | TREND
Returns values along a linear trend |
105 | TRIMMEAN
Returns the mean of the interior of a data set |
106 | VAR.P
Calculates variance based on the entire population |
107 | VAR.S
Estimates variance based on a sample |
108 | VARA
Estimates variance based on a sample, including logical values and text |
109 | VARPA
Calculates variance based on the entire population, including logical values and text |
110 | WEIBULL.DIST
Returns the Weibull distribution |
111 | Z.TEST
Returns the one-tailed probability-value of a z-test |
112 | BAHTTEXT
Converts a number to Thai text and adds a suffix of "Baht." |
113 | CHAR
Returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters. |
114 | CLEAN
Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. |
115 | CODE
Returns a numeric code for the first character in a text string. The returned code corresponds to the ANSI character set for the Windows System. |
116 | CONCAT
Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments. |
117 | CONCATENATE
Joins two or more text strings into one string. |
118 | DOLLAR
Converts a number to text format and applies a currency symbol. The name of the Function and the symbol that it applies depend upon your language settings. |
119 | Exact
Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. |
120 | FIND
FIND and FINDB locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string. |
121 | FIXED
Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text. |
122 | LEFT
Returns the first character or characters in a text string, based on the number of characters you specify. |
123 | LEN
LEN returns the number of characters in a text string. LENB returns the number of bytes used to represent the characters in a text string. |
124 | LOWER
Converts all uppercase letters in a text string to lowercase. |
125 | MID
Returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify. |
126 | NUMBERVALUE
Converts text to a number, in a locale-independent way. |
127 | PROPER
Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters. |
128 | REPLACE
Replaces part of a text string, based on the number of characters you specify, with a different text string. |
129 | REPT
Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string. |
130 | RIGHT
Returns the last character or characters in a text string, based on the number of characters you specify. |
131 | SEARCH
The SEARCH and SEARCHB functions locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string. |
132 | SUBSTITUTE
Substitutes new_text for old_text in a text string. |
133 | T
The T function returns the text referred to by value. |
134 | TEXT
Converts a numeric value to text and lets you specify the display formatting by using special format strings. |
135 | TEXTJOIN
Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges. |
136 | TRIM
Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing. |
137 | UNICHAR
Returns the Unicode character that is referenced by the given numeric value. |
138 | UNICODE
Returns the number (code point) corresponding to the first character of the text. |
139 | UPPER
Converts text to uppercase. |
140 | VALUE
Converts a text string that represents a number to a number. |
141 | ENCODEURL
Returns a URL-encoded string. |
142 | FILTERXML
Returns specific data from the XML content by using the specified XPath. |
143 | WEBSERVICE
Returns data from a web service on the Internet or Intranet. |