You know there are many statistical functions, such as COUNTIFS, RANK, COUNT …. Each of these functions has different functions and tasks. If you do not remember the formula of statistical functions, let’s refer to the list of functions in Excel below.
The Statistical function in Excel or the calculation function, the date function supports the work and learning of all ages in the most effective way. Not only do you know the name, but also the syntax and application of practical problems depends on each of you today.
Summary of statistical functions in Excel 2016, 2013, 2010 and 2007
Functions in Excel often have relationships with each other, for example, the Logic function can also be combined with the statistical function to list data as required by the problem, in the previous article, Taimienphi.vn introduced. to the user summing up logical functions that are very useful to the user. In this article, we will continue sending to the statistical functions in Excel for readers
Summary of statistical functions in Excel
1. FUNCTION GROUP ON STATS
AVEDEV (number1, number2, …): Calculate the average of the absolute deviations of data points by their average. Often used as a measure of change in a dataset.
AVERAGE (number1, number2, …): Calculate the average.
AVERAGEA (number1, number2, …): Calculate the average of the values, including the logical values.
AVERAGEIF (range, criteria1): Calculates the average of the values in an array under a condition.
AVERAGEIFS (range, criteria1, criteria2, …): Calculates the average of the values in an array under multiple conditions.
COUNT (value1, value2, …): Count the number of cells in the list.
COUNTA (value1, value2, …): Counts the number of cells containing the (non-empty) value in the list.
COUNTBLANK (range): Count empty cells in an area.
COUNTIF (range, criteria): Counts the number of cells that meet a given condition within a range. In fact, the COUNTIF statistical function is one of the conditional statistical functions that are used a lot in Excel so interested readers can find and refer to the information, instructions, tips for using this function. Article COUNTIF function count has electricity in Excel that Taimienphi.vn has done before.
COUNTIFS (range1, criteria1, range2, criteria2, …): Counts the number of cells that meet the given conditions.
DEVSQ (number1, number2, …): Calculate the square of deviations of data points from their sample mean, and then add those squares.
FREQUENCY (data_array, bins_array): Calculates how many values frequently appear inside a range of values, and then returns an array of numbers. Always use this function as an array formula.
GEOMEAN (number1, number2, …): Returns the geometric mean of a sequence of positive numbers. Often used to calculate the average growth, in which compound interest has variable rates of interest given…
HARMEAN (number1, number2, …): Returns the harmonic mean (the reciprocal of the arithmetic mean) of the numbers.
KURT (number1, number2, …): Calculates the sharpness of the dataset, indicating the sharp or flat level of a distribution compared to the normal distribution.
LARGE (array, k): Returns the kth largest value in a dataset.
MAX (number1, number2, …): Returns the maximum value of a value set. Along with calculating the maximum value, we can use the MIN function to calculate the minimum value. Refer to the article Max Min function in Excel to support calculating the maximum and minimum values in Excel for more information.
GHOST AWAY (number1, number2, …): Returns the maximum value of a value set, including the logical and text values.
MEDIAN (number1, number2, …): Calculates the average of the numbers.
MIN (number1, number2, …): Returns the minimum value of a value set.
MINA (number1, number2, …): Returns the minimum value of a value set, including the logical and text values.
MODE (number1, number2, …): Returns the value that appears the most in an array of values.
PERCENTILE (array, k): Find the kth percentile of values in an array.
PERCENTRANK (array, x, significance): Returns the rank (relative position) of a value in an array of data, which is the percentage of that data array.
PERMUT (number, number_chosen): Returns the permutation of the objects.
QUARTILE (array, quart): Calculate the quartile point of a data set. Often used in data surveys to divide collections into groups …
RANK (number, ref, order): Calculates the rank of a number in a list of numbers; RANK function is used a lot when users need to rank for their data, refer to writing RANK function to support rankings in Excel.
SKEW (number1, number2, …): Returns the deviation of the distribution, describing the asymmetry of the distribution around its mean.
SMALL (array, k): Returns the kth smallest value in a set.
STDEV (number1, number2, …): Estimate the standard deviation on a sample basis.
STDEVA (value1, value2, …): Estimate the standard deviation on a sample basis, including logical values.
STDEVP (number1, number2, …): Calculates the standard deviation for the entire population.
STDEVPA (value1, value2, …): Calculates the standard deviation for the entire population, including text and logical values.
VAR (number1, number2, …): Returns the variance based on a sample.
VARA (value1, value2, …): Returns variance based on a sample, including both logical and text values.
VARP (number1, number2, …): Returns the variance based on the entire population.
VARPA (value1, value2, …): Returns variance based on the entire population, including logical values and text.
TRIMMEAN (array, percent): Calculates the average of the interior of a data set, by eliminating the percentage of data points at the beginning and at the end of the data set.
2. GROUP OF FUNCTIONS ON DIFFERENTIAL DISTRIBUTION
BETADIST (x, alpha, beta, A, B): Returns the value of the beta cumulative distribution density function.
BETAINV (probability, alpha, beta, A, B): Returns the inverse of the cumulative probability distribution beta density function.
BINOMDIST (number_s, trials, probability_s, cumulative): Returns the probability of successful attempts at binary distribution.
CHIDIST (x, degrees_freedom): Returns the probability of one side of the chi-squared distribution.
CHIINV (probability, degrees_freedom): Returns the inverse of the probability of one side of the chi-squared distribution. In fact, not everyone uses the CHIINV function, but when we need to calculate the inverse value of the probability we actually need the CHIINV function, it’s an excellent calculator.
CHITEST (actual_range, expected_range): Returns the value of the probability from the chi-squared distribution and the corresponding degrees of freedom.
CONFIDENCE (alpha, standard_dev, size): Calculates the confidence interval for a theoretical expectation
CRITBINOM (trials, probability_s, alpha): Returns the smallest value such that the cumulative binomial distribution is greater than or equal to the standard value. Often used to ensure quality applications …
Statistical function over time EXPONDIST (x, lambda, cumulative): Calculate the exponential distribution. Often used to simulate the time between events …
FDIST (x, degrees_freedom1, degrees_freedom2): Calculate probability distribution F. Often used to find out whether two sets of data have different degrees …
FINV (probability, degrees_freedom1, degrees_freedom2): The reciprocal of probability distribution F. Often used to compare the variability in two data sets.
FTEST (array1, array2): Returns the result of an F. Test Often used to determine whether two samples have different variances …
FISHERINV (y): Inverse Fisher transformation. Often used to analyze the correlation between data arrays…
GAMMADIST (x, alpha, beta, cumulative): Returns the cumulative gamma distribution. Can be used to study deviations
GAMMAINV (probability, alpha, beta): Returns the inverse of the gamma cumulative distribution.
GAMMLN (x): Calculate the natural logarithm of the gamma function
HYPGEOMDIST (number1, number2, …): Returns the hyperinfection distribution (probability of some success …)
LOGINV (probability, mean, standard_dev): The inverse of the lognormal cumulative distribution function of x (LOGNORMDIST). When we need to calculate the cumulative logarithmic distribution function we will use the LOGINV function because this is the best tool to do so.
LOGNORMDIST (x, mean, standard_dev): Returns the lognormal cumulative distribution of x, where the natural logarithm of x is normally distributed with the mean and standard_dev parameters.
NEGBINOMDIST (number_f, number_s, probability_s): Returns the negative binomial distribution (returns the probability that there will be number_f failures before there are number_s successes, when the constant probability of one success is probability_s)
NORMDIST (x, mean, standard_dev, cumulative): Returns the normal distribution. Often used in statistics, including hypothesis testing
NORMINV (probability, mean, standard_dev): Calculates the standard cumulative distribution inverse
NORMSDIST (z): Returns the standard normal cumulative distribution function, which is a distribution whose average value is zero (0) and the standard deviation is 1
NORMSINV (probability): The reciprocal of the normal cumulative distribution function
POISSON (x, mean, cumulative): Returns the poisson distribution. A time-based quantity statistics function that is used to estimate the number of events that will occur in a given time period
PROB (x_range, prob_range, lower_limit, upper_limit): Calculates the probability of values in the range between the two limits
STANDARDIZE (x, mean, standard_dev): Returns the normalized value from the distribution denoted by mean and standard_dev
TDIST (x, degrees_freedom, tails): Returns the probability of the Student distribution (distribution t), where x is the value from t and is used to calculate probability.
TINV (probability, degrees_freedom): Returns the t value of the Student distribution.
TTEST (array1, array2, tails, type): Probability associated with the Student test.
WEIBULL (x, alpha, beta, cumulative): Returns the Weibull distribution. Often used in reliability analysis, like calculating the average life of a device.
ZTEST (array, x, sigma): Returns the probability of one side of the z test.
Above are the statistical functions in Excel, hope this article will help you in the process of using Microsoft Excel office tools. I wish you study and use Excel effectively!
Microsoft has released the 2016 version of Microsoft Office. This is an updated office suite that adds a lot of useful features. Help create tables, calculations, statistics calculations, revenue and expenditure balance for businesses, agencies, or even large companies.