EnterpriseSheet formulas

Document -> EnterpriseSheet formula -> Statistical Functions

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]) |