The syntax and function of each function in the group of statistical functions in Excel

The following article summarizes the syntax and function of each function in the group of statistical functions in Excel spreadsheets. Please follow along.

The syntax and function of each function in the group of statistical functions in Excel Picture 1

The statistical function is divided into 3 subgroups: the statistical group, the probability distribution function group and the linear regression and correlation function group.

1. Function group on Statistics.

AVEDEV (number1, number2, .): The function calculates the average absolute deviation of data points from their average. The AVEDEV function is a measure of the variability of a data set.

AVERAGE (number1, number2, .): The function calculates the average of the input values.

AVERAGEA (number1, number2, .): The function calculates the average of the input argument values ​​including those containing the logical value.

AVERAGEIF (range, criteria, average_range): The function calculates the average of all selected cells that meet the specified condition.

AVERAGIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2], .): The function calculates the average of the values ​​in an array according to many pre-specified conditions.

COUNT (value1, value2, .): The function counts the number of cells containing numbers in the list of arguments.

COUNTA (value1, value2, .): The function counts the number of non-empty (empty) cells in the list of input arguments or a range of your choice.

COUNTBLANK (range): The function counts empty (empty) cells in the input data area.

COUNTIF (range, criteria): The function counts the number of cells in a range, a range that satisfies the condition that you specify.

COUNTIFS (range1, criteria1, [range2, criteria2], .): The function counts the number of cells that satisfy many different conditions together.

DEVSQ (number1, number2, .): The function calculates the square of deviations of data points from their sample mean and sums of squares.

FREQUENCY (data_array, bins_array): The function calculates the frequency of occurrences of values ​​in a range of values, then returns a vertical number array. The function is entered as an array formula.

GEOMEAN (number1, number2): The function returns the kernel average of a positive data range or range. You can use the function to calculate the average growth if you know the compound interest with variable interest rates.

HARMEAN (number1, number2, .): The function returns the harmonic mean (is the reciprocal of the arithmetic mean) of the input numbers.

KURT (number1, number2, .): The function returns the sharp coefficient of a data set. The kurtosis indicates the corresponding sharp or flat characteristics of a distribution compared to a normal distribution. Positive sharp points indicate a relatively sharp distribution, negative sharp points indicate a relatively flat distribution.

LARGE (array, k): The function returns the kth largest value of a data set. This function can be used to select a value based on its relative position.

MAX (number1, number2, .): The function returns the maximum value in the arguments or input data range.

MAXA (number1, number2, .): The function returns the maximum value of the input value set including both logical and text values.

MEDIAN (number1, number2 .): The function returns the median of the given numbers, the median is the number in the middle of a set of numbers.

MIN (number1, number2, .): The function returns the smallest value of the input number value set.

MINA (number1, number2…): The function returns the minimum value of a value set, including the logical and text values.

MODE (number1, number2 .): The function returns the most frequently repeated value in an array or data range.

PERCENTLE (array, k): The function finds the kth percentile of values ​​in a data array.

PERCENTRANK (array, x, singificance): The function returns the percentage rank of a value in a dataset.

PERMUT (number, number_chosen): The function returns the possible number of permutations of a collection of objects.

QUARTILE (array, quart): The function returns the quartile of a data set, the quartile is often used in survey and sales data to divide the set into groups.

RANK (number, ref, order): The function returns the rank of a number in a list of numbers.

SKEW (number1, number2, .): The function returns the deviation of a distribution, the deviation represents the asymmetry of the distribution around its mean.

SMALL (array, k): The function returns the kth smallest value of a data set, using this function to return values ​​with relatively specific rankings in the dataset.

STDEV (number1, number2, .): The function estimates the standard deviation based on a sample.

STDEVA (value1, value2, .): The function of estimating standard deviations based on a sample including logical values.

STDEV.P (number1, number2, .): The function calculates the standard deviation based on the entire set provided as an argument, ignoring logical and literal values.

STDEVPA (value1, value2, .): The function calculates the standard deviation based on the entire set of arguments, including text and logical values.

VAR (number1, number2, .): The function returns the variance based on a sample from a given set of data.

VARA (value1, value2, .): The function returns the variance based on a sample from the input data, including both literal and logical values.

VARP (number1, number2, .): The function returns variance based on the entire population.

VARPA (value1, value2, .): The function returns variance based on an entire population, including logical and literal values.

TRIMMEAN (array, percent): The function calculates the average of the inside of a set of values ​​by eliminating the percentage of data points at the beginning and at the end of the value set.

2. Function group on Probability Distribution.

BETADIST (x, alpha, beta, A, B): The function returns the value of the cumulative probability distribution beta density function.

BETAINV (probability, alpha, beta, A, B): The function returns the inverse of the beta cumulative probability density function for a specified beta distribution.

BINOMDIST (number_s, trials, probability_s, cumultive): The function returns the probability of successful attempts at the binomial distribution.

CHIDIST (x, degrees_freedom): The function returns the probability of one side of the chi-squared distribution.

CHIINV (probability, degrees_freedom): The function returns the inverse of the one-sided probability of the chi-squared distribution.

CHITEST (actual_range, expected_range): The function returns the value of probability from chi-squared distribution and the corresponding degrees of freedom.

CONFIDENCE (alpha, standard_dev, size): The function returns the confidence interval of the overall mean, using the normalized distribution.

CRITBINOM (trials, probability_s, alpha): The function returns the smallest value where the cumulative binomial distribution is greater than or equal to a standard value, the function often used for quality assurance applications.

EXPONDIST (x, lambda, cumulative): Function returns exponential distribution.

FDIST (x, degrees_freedom1, degrees_freedom2): The function returns the probability distribution F (top right) for two data sets.

FINV (probability, degrees_freedom1, degrees_freedom2): The function returns the inverse of the probability distribution F (right end).

FTEST (array1, array2): The function returns the result of an F-test. The F-test returns the two-headed probability that the variance in the data arrays is negligible.

FISHER (x): The function returns the Fisher transformation at x, which results in a distribution function rather than a symmetrical deviation. Functions are often used in testing hypotheses based on correlation coefficients.

FISHERINV (y): The function returns the inverse of the Fisher transformation, meaning if y = FISHER (x) then x = FISHERINV (y).

GAMMADIST (x, alpha, beta, cumulative): The function returns the probability of the gamma distribution, the function often used to study variables with skewed distributions.

GAMMAINV (probability, alpha, beta): The function returns the inverse of the gamma distribution.

GAMMLN (x): The function of calculating the natural logarite of the gamma function.

HYPGEOMDIST (sample_s, number_sample, population_s, number_pop): The function returns the probability of superinfection distribution (the probability of the number of sample successes known, knowing the sample size, the success of the population and the size of the population) .

LOGINV (probability, mean, standard_dev): The function returns the inverse of the cumulative logarithm distribution function of a given x value. Where ln (x) is usually distributed with mean parameters and standard deviations.

LOGNORMDIST (x, mean, standard_dev): The function 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): The function returns the negative binomial distribution (returns the probability that there will be failures (number_f) before there are successes (number_s) when the probability of success is constant. is probability_s).

NORMDIST (x, mean, standard_dev, cumulative): The function returns the normal distribution with the specified standard deviation and mean.

NORMINV (probability, mean, standard_dev): The function returns the inverse of the standard cumulative distribution with the specified standard deviation and mean.

NORMSDIST (z): The function that returns the result is a standard cumulative distribution function.

NORMSINV (probabitily): The function returns the inverse of the normal cumulative distribution function.

POISSON (x, mean, cumulative): The function returns the Poisson distribution.

PROB (x_range, prob_range, lower_limit, upper_limit): The function returns the probability that values ​​in a range are between two limits.

STANDARDIZE (x, mean, standard_dev): The function returns the normalized value from the distribution expressed by mean and standard_dev.

TDIST (x, degrees_freedom, tails): The function returns the probability of the Student t distribution, where the numerical value (x) is the calculated value of t and is used to calculate the probability.

TINV (probability, degrees_freedom): The function returns the inverse sides of the Student's t-distribution.

TTEST (array1, array2, tails, type): The function returns the probability associated with the Student's t test, using the function to determine if the two test samples derived from the two original sets have the same mean.

WEIBULL (x, alpha, beta, cumulative): The function returns the Weibull distribution, which is often used in the reliability analysis.

ZTEST (array, x, sigma): The function returns the one-sided probability value of the z test.

3. Function group on Correlation and Linear Regression.

CORREL (array1, array2): The function returns the correlation coefficient of the range of cells array1 and array2. Use the correlation coefficient to determine the relationship between two attributes.

COVAR (array1, array2): The function returns the variance and the product's average of deviations for each pair of data points in two different data sets.

FORECAST (x, known_y's, known_x's): The function calculates or estimates a future value using current values. The predicted value is a y value for a known x value. Known values ​​are existing x and y values, new values ​​are predicted using linear regression.

GROWTH (known_y's, known_x's, new_x's, const): Function that calculates exponential growth using existing data. The function returns the y value for a new string of x values ​​specified by you by using the existing x and y values.

INTERCEPT (known_y's, known_x's): The function calculates the point at which a line will intersect the y-axis using existing x and y values.

LINEST (known_y's, known_x's, const, stats): The function calculates statistics for a straight line by using the least squares method to calculate the line that best fits the data, then returns an array describing that line.

LOGEST (known_y's, known_x's, const, stats): The function used in regression analysis, this function calculates an exponential curve that fits the data and returns the array of data describing that curve. Because this function returns an array of values, it must be entered as an array formula.

PEARSON (array1, array2): The function returns Pearson product moment correlation coefficient r, a scalar index in the range of -1.0 to 1.0 including -1.0 and 1.0, which reflects the expansion of the linear relationship Calculated between two data sets.

RSQ (known_y's, known_x's): The function returns the square of the Pearson moment correlation coefficient using data points in known_y's and known_y's.

SLOPE (known_y's, known_x's): The function finds the slope of the regression line using data points in known_y's and known_x's.

STEYX (known_y's, known_x's): The function returns the standard error (the measure of the amount of error in the y prediction for an individual x value) of the predicted y value for each x value in the regression.

The above is a summary of the functions in the statistical function group in Excel worksheet. Hope the article will help you. Good luck!