Statistical Functions
| Formulas | Description | Examples |
|---|---|---|
| AVEDEV | Syntax: AVEDEV(number1, [number2], ...) Returns the average of the absolute deviations of data points from their mean. AVEDEV is a measure of the variability in a data set. |
=AVEDEV(A1,A2,A3) =AVEDEV(A1:A3) |
| AVERAGE | Syntax: AVERAGE(number1, number2, ...) Returns the average of the arguments. Number1, number2, ... are numerical values or ranges. Text is ignored. |
=AVERAGE(A1,A2,A3) =AVERAGE(A1:A3) |
| AVERAGEIF | Syntax: AVERAGEIF(range, criteria, average_range) Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria. |
=AVERAGEIF(B2:B5,"<23000") =AVERAGEIF(A2:A6,"=*West",B2:B6) |
| AVERAGEIFS | Syntax: AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2, ....]) Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria. |
=AVERAGEIFS(B2:B5,B2:B5,">70",B2:B5,"<90") =AVERAGEIFS(D2:D5,D2:D5, "<>Incomplete", D2:D5, ">80") |
| BINOM.DIST | Syntax: BINOM.DIST(number_s,trials,probability_s,cumulative) Returns the individual term binomial distribution probability. |
=BINOM.DIST(6, 10, 0.5, FALSE) |
| CORREL | Syntax: CORREL(array1, array2) Returns the correlation coefficient of the array1 and array2 cell ranges. Use the correlation coefficient to determine the relationship between two properties. |
=CORREL(a1:a10, b1:b10) |
| COUNT | Syntax: COUNT(value1, value2, ...) Counts how many numbers are in the list of arguments. Text entries are ignored. Value1, value2, ... value30 are values or ranges which are to be counted. |
=COUNT(a1:a10) =COUNT(a1,a2,a3) |
| COUNTA | Syntax: COUNTA(value1, [value2], ...) Counts the number of cells that are not empty in a range. |
=COUNTA(a1,a2,a3,a4) |
| COUNTIF | Syntax: COUNTIF(range,criteria) Counts the number of cells within a range that meet the given criteria. range is the range of cells that you want to count based on the criteria. criteria is used to determine which cells to count. |
=COUNTIF(A2:A25,"temp") |
| COUNTIFS | Syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) Applies criteria to cells across multiple ranges and counts the number of times all criteria are met. |
=COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes") |
| COUNTBLANK | Syntax: COUNTBLANK(range) Counts empty cells in a specified range of cells. |
=COUNTBLANK(A1:B25) |
| DEVSQ | Syntax: DEVSQ(number1, [number2], ...) Returns the sum of squares of deviations of data points from their sample mean. |
=DEVSQ(A1,A2,A3) =DEVSQ(A1:A3) |
| EXPON.DIST | Syntax:EXPON.DIST(x,lambda,cumulative) Returns the exponential distribution. Use EXPON.DIST to model the time between events, such as how long an automated bank teller takes to deliver cash. |
=EXPON.DIST(x,lambda,cumulative) |
| FISHER | Syntax:FISHER(x) Returns the Fisher transformation at x. |
=FISHER(x) |
| FISHERINV | Syntax:FISHERINV(x) Returns the inverse of the Fisher transformation. |
=FISHERINV(x) |
| GAMMA | Syntax: GAMMA(number) Return the gamma function value. |
=GAMMA(2.5) |
| GAMMALN | Syntax: GAMMALN(number) Returns the natural logarithm of the gamma function. |
=GAMMALN(2.5) |
| GEOMEAN | Syntax: GEOMEAN(number1, [number2], ...) Returns the geometric mean of an array or range of positive data. |
=GEOMEAN(A1:B25) |
| HARMEAN | Syntax: HARMEAN(number1, [number2], ...) Returns the harmonic mean of an array or range of positive data. |
=HARMEAN(A1:B25) |
| LARGE | Syntax: LARGE(array, nth_position) Returns the nth largest value from a set of values. |
=LARGE(A1:A5, 1) |
| MAX | Syntax: MAX(number1, number2, ...) Returns the maximum value in a list of arguments. Ignores logic values and text. |
=MAX(a1:a5) =MAX(a1,a2,a3,10) |
| MEDIAN | Syntax: MEDIAN(number1, number2, ...) Median function returns the median of the numbers provided. |
=MEDIAN(a1, a2, a3) =MEDIAN(a1:a3) |
| MIN | Syntax: MIN(number1, number2, ...) Returns the minimum value in a list of arguments. Ignores logic values and text. |
=MIN(a1:a5) =MIN(a1,a2,a3,10) |
| MODE | Syntax: MODE(number1, number2, ...) Mode return the most frequently occurring value in a list of numbers. |
=MODE(A1, A2) |
| MODE.SNGL | Syntax: MODE.SNGL(number1, number2, ...) Mode return the most frequently occurring value in a list of numbers. |
=MODE.SNGL(A1, A2) |
| PEARSON | Syntax: PEARSON(array1, array2) Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from -1.0 to 1.0 inclusive and reflects the extent of a linear relationship between two data sets. |
=PEARSON(A2:A6,B2:B6) |
| PERCENTILE.EXC | Syntax: PERCENTILE.EXC(array,k) Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive. |
=PERCENTILE.EXC(A1:A5,0.5) |
| PERCENTILE.INC | Syntax: PERCENTILE.INC(array,k) Returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive. |
=PERCENTILE.INC(A1:A5,0.5) |
| QUARTILE.EXC | Syntax: QUARTILE.EXC(array,quart) Returns the quartile of the data set, based on percentile values from 0..1, exclusive. |
=QUARTILE.EXC(A1:A5,1) |
| QUARTILE.INC | Syntax: QUARTILE.INC(array,quart) Returns the quartile of a data set, based on percentile values from 0..1, inclusive. |
=QUARTILE.INC(A1:A5,1) |
| RANK.AVG | Syntax: RANK.AVG(number,ref,[order]) Returns the rank of a number in a list of numbers: its size relative to other values in the list; if more than one value has the same rank, the average rank is returned. |
=RANK.AVG(1, B1:B5) |
| RANK.EQ | Syntax: RANK.EQ(number,ref,[order]) Returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned. |
=RANK.EQ(1, B1:B5) |
| RSQ | Syntax: RSQ(known_y's, known_x's) Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's. |
=RSQ(A1:A5, B1:B5) |
| SLOPE | Syntax: SLOPE(known_y's, known_x's) Returns the slope of the linear regression line through data points in known_y's and known_x's. The slope is the vertical distance divided by the horizontal distance between any two points on the line, which is the rate of change along the regression line. |
=SLOPE(A1:A5, B1:B5) |
| SMALL | Syntax: SMALL(array, nth_position) Returns the nth smallest value from a set of values. |
=SMALL(A1:A5, 2) =SMALL(A1,A2,A3,A4...) |
| STDEV.S | Syntax: STDEV.S(number1,[number2],...]) Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean). |
=STDEV.S(A1:A5]) |
| STDEV.P | Syntax: STDEV.P(number1,[number2],...]) Calculates standard deviation based on the entire population given as arguments (ignores logical values and text). |
=STDEV.P(A1:A5]) |
| VAR.S | Syntax: VAR.S(number1,[number2],...]) Estimates variance based on a sample (ignores logical values and text in the sample). |
=VAR.S(A1:A5]) |
| VAR.P | Syntax: VAR.P(number1,[number2],...]) Calculates standard deviation based on the entire population given as arguments (ignores logical values and text). |
=VAR.P(A1:A5]) |