| Functions |
Excel Formulas |
Description |
| AVERAGE |
=AVERAGE(number1,number2,…) |
Returns the average of its arguments |
| AVERAGEIF |
=AVERAGEIF(range,criteria,[average_range]) |
Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria |
| COUNT |
=COUNT(value1,value2,…) |
Counts how many numbers are in the list of arguments |
| COUNTA |
=COUNTA(value1,value2,…) |
Counts how many values are in the list of arguments |
| COUNTBLANK |
=COUNTBLANK(range) |
Counts the number of blank cells within a range |
| COUNTIF |
=COUNTIF(range,criteria) |
Counts the number of cells within a range that meet the given criteria |
| COUNTIFS |
=COUNTIFS(criteria_range,criteria,…) |
Counts the number of cells within a range that meet multiple criteria |
| MAX |
=MAX(number1,number2,…) |
Returns the maximum value in a list of arguments |
| MEDIAN |
=MEDIAN(number1,number2,…) |
Returns the median of the given numbers |
| MIN |
=MIN(number1,number2,…) |
Returns the minimum value in a list of arguments |
| TEXT |
=TEXT(value,format_text) |
Formats a number and converts it to text |
| AVERAGEA |
=AVERAGEA(value1,value2,…) |
Returns the average of its arguments, including numbers, text, and logical values |
| AVERAGEIFS |
=AVERAGEIFS(average_range,criteria_range,criteria,…) |
Returns the average (arithmetic mean) of all cells that meet multiple criteria |
| GEOMEAN |
=GEOMEAN(number1,number2,…) |
Returns the geometric mean |
| INTERCEPT |
=INTERCEPT(known_y’s,known_x’s) |
Returns the intercept of the linear regression line |
| LARGE |
=LARGE(array,k) |
Returns the k-th largest value in a data set |
| LINEST |
=LINEST(known_y’s,known_x’s,const,stats) |
Returns the parameters of a linear trend |
| LOGEST |
=LOGEST(known_y’s,known_x’s,const,stats) |
Returns the parameters of an exponential trend |
| MAXA |
=MAXA(value1,value2,…) |
Returns the maximum value in a list of arguments, including numbers, text, and logical values |
| MINA |
=MINA(value1,value2,…) |
Returns the smallest value in a list of arguments, including numbers, text, and logical values |
| MODE.MULT |
=MODE.MULT(number1,number2,…)
|
Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data |
| MODE.SNGL |
=MODE.SNGL(number1,number2,…) |
Returns the most common value in a data set |
| PROB |
=PROB(x_range,prob_range,lower_limit,upper_limit) |
Returns the probability that values in a range are between two limits |
| RANK.AVG |
=RANK.AVG(number,ref,order) |
Returns the rank of a number in a list of numbers |
| RANK.EQ |
=RANK.EQ(number,ref,order) |
Returns the rank of a number in a list of numbers |
| SKEW |
=SKEW(number1,number2,…) |
Returns the skewness of a distribution |
| SLOPE |
=SLOPE(known_y’s,known_x’s) |
Returns the slope of the linear regression line |
| SMALL |
=SMALL(array,k) |
Returns the k-th smallest value in a data set |
| STANDARDIZE |
=STANDARDIZE(x,mean,standard_dev) |
Returns a normalized value |
| TREND |
=TREND(known_y’s,known_x’s,new_x’s,const) |
Returns values along a linear trend |
| NORM.S.INV |
=NORM.S.INV(probability) |
Returns the inverse of the standard normal cumulative distribution |
| AVEDEV |
=AVEDEV(number1,number2,…) |
Returns the average of the absolute deviations of data points from their mean |
| BETA.DIST |
=BETA.DIST(x,alpha,beta,cumulative,A,B) |
Returns the beta cumulative distribution function |
| BETA.INV |
=BETA.INV(probability,alpha,beta,A,B) |
Returns the inverse of the cumulative distribution function for a specified beta distribution |
| BINOM.DIST |
=BINOM.DIST(number_s,trials,probability_s,cumulative) |
Returns the individual term binomial distribution probability |
| BINOM.INV |
=BINOM.INV(trials,probability_s,alpha) |
Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value |
| CHISQ.DIST |
=CHISQ.DIST(x,deg_freedom,cumulative) |
Returns the cumulative beta probability density function |
| CHISQ.DIST.RT |
=CHISQ.DIST.RT(x,deg_freedom) |
Returns the one-tailed probability of the chi-squared distribution |
| CHISQ.INV |
=CHISQ.INV(probability,deg_freedom) |
Returns the cumulative beta probability density function |
| CHISQ.INV.RT |
=CHISQ.INV.RT(probability,deg_freedom) |
Returns the inverse of the one-tailed probability of the chi-squared distribution |
| CHISQ.TEST |
=CHISQ.TEST(actual_range,expected_range) |
Returns the test for independence |
| CONFIDENCE.NORM |
=CONFIDENCE.NORM(alpha,standard_dev,size) |
Returns the confidence interval for a population mean |
| CONFIDENCE.T |
=CONFIDENCE.T(alpha,standard_dev,size) |
Returns the confidence interval for a population mean, using a Student’s t distribution |
| CORREL |
=CORREL(array1,array2) |
Returns the correlation coefficient between two data sets |
| COVARIANCE.P |
=COVARIANCE.P(array1,array2) |
Returns covariance, the average of the products of paired deviations |
| COVARIANCE.S |
=COVARIANCE.S(array1,array2) |
Returns the sample covariance, the average of the products deviations for each data point pair in two data sets |
| DEVSQ |
=DEVSQ(number1,number2,…) |
Returns the sum of squares of deviations |
| EXPON.DIST |
=EXPON.DIST(x,lambda,cumulative) |
Returns the exponential distribution |
| F.DIST |
=F.DIST(x,deg_freedom1,deg_freedom2,cumulative) |
Returns the F probability distribution |
| F.DIST.RT |
=F.DIST.RT(x,deg_freedom1,deg_freedom2) |
Returns the F probability distribution |
| F.INV |
=F.INV(probability,deg_freedom1,deg_freedom2) |
Returns the inverse of the F probability distribution |
| F.INV.RT |
=F.INV.RT(probability,deg_freedom1,deg_freedom2) |
Returns the inverse of the F probability distribution |
| F.TEST |
=F.TEST(array1,array2) |
Returns the result of an F-test |
| FISHER |
=FISHER(x) |
Returns the Fisher transformation |
| FISHERINV |
=FISHERINV(y) |
Returns the inverse of the Fisher transformation |
| FREQUENCY |
=FREQUENCY(data_array,bins_array) |
Returns a frequency distribution as a vertical array |
| GAMMA.DIST |
=GAMMA.DIST(x,alpha,beta,cumulative) |
Returns the gamma distribution |
| GAMMA.INV |
=GAMMA.INV(probability,alpha,beta) |
Returns the inverse of the gamma cumulative distribution |
| GAMMALN |
=GAMMALN(x) |
Returns the natural logarithm of the gamma function, G(x) |
| GAMMALN.PRECISE |
=GAMMALN.PRECISE(x) |
Returns the natural logarithm of the gamma function, G(x) |
| GROWTH |
=GROWTH(known_y’s,known_x’s,new_x’s,const) |
Returns values along an exponential trend |
| HARMEAN |
=HARMEAN(number1,number2,…) |
Returns the harmonic mean |
| HYPGEOM.DIST |
=HYPGEOM.DIST(sample_s,number_sample,population_s,number_pop,cumulative) |
Returns the hypergeometric distribution |
| KURT |
=KURT(number1,number2,…) |
Returns the kurtosis of a data set |
| LOGNORM.DIST |
=LOGNORM.DIST(x,mean,standard_dev,cumulative) |
Returns the cumulative lognormal distribution |
| LOGNORM.INV |
=LOGNORM.INV(probability,mean,standard_dev) |
Returns the inverse of the lognormal cumulative distribution |
| NEGBINOM.DIST |
=NEGBINOM.DIST(number_f,number_s,probability_s,cumulative) |
Returns the negative binomial distribution |
| NORM.DIST |
=NORM.DIST(x,mean,standard_dev,cumulative) |
Returns the normal cumulative distribution |
| NORM.INV |
=NORM.INV(probability,mean,standard_dev) |
Returns the inverse of the normal cumulative distribution |
| NORM.S.DIST |
=NORM.S.DIST(z,cumulative) |
Returns the standard normal cumulative distribution |
| PEARSON |
=PEARSON(array1,array2) |
Returns the Pearson product moment correlation coefficient |
| PERCENTILE.EXC |
=PERCENTILE.EXC(array,k) |
Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive |
| PERCENTILE.INC |
=PERCENTILE.INC(array,k) |
Returns the k-th percentile of values in a range |
| PERCENTRANK.EXC |
=PERCENTRANK.EXC(array,x,significance) |
Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set |
| PERCENTRANK.INC |
=PERCENTRANK.INC(array,x,significance) |
Returns the percentage rank of a value in a data set |
| PERMUT |
=PERMUT(number,number_chosen) |
Returns the number of permutations for a given number of objects |
| POISSON.DIST |
=POISSON.DIST(x,mean,cumulative) |
Returns the Poisson distribution |
| QUARTILE.EXC |
=QUARTILE.EXC(array,quart) |
Returns the quartile of the data set, based on percentile values from 0..1, exclusive |
| QUARTILE.INC |
=QUARTILE.INC(array,quart) |
Returns the quartile of a data set |
| RSQ |
=RSQ(known_y’s,known_x’s) |
Returns the square of the Pearson product moment correlation coefficient |
| STDEV.P |
=STDEV.P(number1,number2,…) |
Calculates standard deviation based on the entire population |
| STDEV.S |
=STDEV.S(number1,number2,…) |
Estimates standard deviation based on a sample |
| STDEVA |
=STDEVA(value1,value2,…) |
Estimates standard deviation based on a sample, including numbers, text, and logical values |
| STDEVPA |
=STDEVPA(value1,value2,…) |
Calculates standard deviation based on the entire population, including numbers, text, and logical values |
| STEYX |
=STEYX(known_y’s,known_x’s) |
Returns the standard error of the predicted y-value for each x in the regression |
| T.DIST |
=T.DIST(x,deg_freedom,cumulative) |
Returns the Percentage Points (probability) for the Student t-distribution |
| T.DIST.2T |
=T.DIST.2T(x,deg_freedom) |
Returns the Percentage Points (probability) for the Student t-distribution |
| T.DIST.RT |
=T.DIST.RT(x,deg_freedom) |
Returns the Student’s t-distribution |
| T.INV |
=T.INV(probability,deg_freedom) |
Returns the t-value of the Student’s t-distribution as a function of the probability and the degrees of freedom |
| T.INV.2T |
=T.INV.2T(probability,deg_freedom) |
Returns the inverse of the Student’s t-distribution |
| T.TEST |
=T.TEST(array1,array2,tails,type) |
Returns the probability associated with a Student’s t-test |
| TRIMMEAN |
=TRIMMEAN(array,percent) |
Returns the mean of the interior of a data set |
| VAR.P |
=VAR.P(number1,number2,…) |
Calculates variance based on the entire population |
| VAR.S |
=VAR.S(number1,number2,…) |
Estimates variance based on a sample |
| VARA |
=VARA(value1,value2,…) |
Estimates variance based on a sample, including numbers, text, and logical values |
| VARPA |
=VARPA(value1,value2,…) |
Calculates variance based on the entire population, including numbers, text, and logical values |
| WEIBULL.DIST |
=WEIBULL.DIST(x,alpha,beta,cumulative) |
Returns the Weibull distribution |
| Z.TEST |
=Z.TEST(array,x,sigma) |
Returns the one-tailed probability-value of a z-test |
Source: https://yodalearning.com/tutorials/excel-formulas-pdf/
