Advanced Excel - Statistical Functions


Advertisements


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.



Advertisements
E-Books Store