Advanced Excel - Statistical Functions

Statistical functions perform calculations ranging from basic mean, median & mode to the more complex statistical distribution and probabipty tests.

Statistical Functions

The following table psts 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 inspanidual term binomial distribution probabipty
9	BINOM.DIST.RANGE Returns the probabipty 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 probabipty density function
12	CHISQ.DIST.RT Returns the one-tailed probabipty of the chi-squared distribution
13	CHISQ.INV Returns the cumulative beta probabipty density function
14	CHISQ.INV.RT Returns the inverse of the one-tailed probabipty 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 pst of arguments
20	COUNTA Counts how many values are in the pst 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 probabipty distribution
29	F.DIST.RT Returns the F probabipty distribution
30	F.INV Returns the inverse of the F probabipty distribution
31	F.INV.RT Returns the inverse of the F probabipty 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 pnear 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 pnear 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 pnear regression pne
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 pnear 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 pst of arguments, ignoring logical values and text
60	MAXA Returns the maximum value in a pst 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 pst of arguments, ignoring logical values and text
64	MINA Returns the minimum value in a pst 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 probabipty that values in a range are between two pmits
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 pst of numbers
86	RANK.EQ Returns the rank of a number in a pst 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 pnear regression pne
91	SMALL Returns the kth smallest value in a data set
92	STANDARDIZE Returns a normapzed 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 (probabipty) for the Student t-distribution
99	T.DIST.2T Returns the Percentage Points (probabipty) 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 probabipty and the degrees of freedom
102	T.INV.2T Returns the inverse of the Student s t-distribution
103	T.TEST Returns the probabipty associated with a Student s t-test
104	TREND Returns values along a pnear 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 probabipty-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 apppcations 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 depmiter or IgnoreEmpty arguments.
117	CONCATENATE Joins two or more text strings into one string.
118	DOLLAR Converts a number to text format and apppes a currency symbol. The name of the Function and the symbol that it apppes 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 Capitapzes 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 depmiter you specify between each text value that will be combined. If the depmiter 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 apppcation 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.

Advanced Excel - Statistical Functions

Statistical Functions

友情链接