Dashpivot offers formulas for analysing and interpreting data. These are mainly used for summarising data, calculating probabilities, making predictions, and understanding trends. These are the main uses of statistical formulas:
Descriptive Statistics
Interpretative Statistics
Data Analysis
Probability
Correlation Analysis
Summarising Data with Measures of Central Tendency
Make use of the statistical formulas in Dashpivot to streamline your forms and make processes much easier to track.
Function ID | Description | Syntax | Sample | Results | Notes |
AVEDEV | Returns the average deviation of the arguments. | AVEDEV(Number1, Number2, ...Number30) | =AVEDEV(A1:A5) *A1:A4 - Number field Given that, A1 = 5 A2 = 6 A3 = 7 A4 = 8 | Returns value, 1.00 |
|
AVERAGE | Returns the average of the arguments. | AVERAGE(Number1, Number2, ...Number30) | =AVERAGE(A1:A4) *A1:A4 - Number field Given that, A1 = 10 A2 = 20 A3 = 30 A4 = 40 | Returns value, 25.00 |
|
AVERAGEA | Returns the average of the arguments. | AVERAGEA(Value1, Value2, ... Value30) | =AVERAGEA(A1:A4) *A1:A4 - Text/number field Given that, A1 = 2 A2 = “three” A3 = “true” A4 = “false” A5 = ““ | Returns value, 0.75 (Becuse 2 + 0 + 1 + 0, and A5 is ignored because it’s an empty cell. 3/4 is 0.75.) | Unlike the AVERAGE() function, AVERAGEA() does not ignore text and logical values. Notes:
|
AVERAGEIF | Returns the arithmetic mean of all cells in a range that satisfy a given condition. | AVERAGEIF(Range, Criterion [, Average_Range ]) | =AVERAGEIF(A1:A5, “>=10”) *A1:A5 - Number field Given that, A1 = 9 A2 = 10 A3 = 11 A4 = 12 A5 = 13 | Returns value, 11.50 |
|
CORREL | Returns the correlation coefficient between two data sets. | CORREL(Data1, Data2) | =CORREL(A1:A3, B1:B3) *A1:B3 - Number field Given that, A1 = 1 A2 = 2 A3 = 3 B1 = 2 B2 = 3 B3 = 4 | Returns value, 1.00 | Results:
|
COUNT | Counts how many numbers are in the list of arguments. | COUNT(Value1, Value2, ... Value30) | =COUNT(A1:A4) *A1:A4 - Text/number field Given that, A1 = 10 A2 = 15 A3 = “Twenty” A4 = 25 A5 = ““ | Returns value, 3.00 (Because there are only 3 cells with numbers) |
|
COUNTA | Counts how many values are in the list of arguments. | COUNTA(Value1, Value2, ... Value30) | =COUNTA(A1:A4) *A1:A4 - Text/number field Given that, A1 = 10 A2 = 15 A3 = “Twenty” A4 = 25 A5 = ““ | Returns value, 4.00 (Because it counts cells with value) |
|
COUNTBLANK | Returns the number of empty cells. | COUNTBLANK(Range) | =COUNTBLANK(A1:A3) *A1:A3 - All fields except for prefilled Given that, A1 = ““ A2 = “Yes” A3 = ““ | Returns value, 2.00 |
|
COUNTIF | Returns the number of cells that meet with certain criteria within a cell range. | COUNTIF(Range, Criteria) | =COUNTIF(A1:A4, “Severe”) *A1:A4 - Number/Text/List fields Given that, A1 = “Severe” A2 = “Mild” A3 = “Low” | Returns value, 1.00 |
|
COUNTIFS | Returns the count of rows or columns that meet criteria in multiple ranges. | COUNTIFS(Range1, Criterion1 [, Range2, Criterion2 [, ...]]) | =COUNTIFS(A1:A3, “Engineer”, B1:B3, “Yes”) *A1:AB3 - Number/Text/List fields Given that, A1 = “Project Manager” A2 = “Engineer” A3 = “Engineer” B1 = “No” B2 = “Yes” B3 = “No” | Returns value, 1.00 (Because there’s only value that meets both conditions) |
|
COVARIANCE.P | Returns the covariance between two data sets, population normalized. | COVARIANCE.P(Data1, Data2) | =COVARIANCE.P(A1:A5, B1:B5) *A1:B5 - Number fields Given that, A = [2, 4, 6, 8, 10] B = [3, 5, 7, 9, 11] | Returns value, 8 |
|
COVARIANCE.S | Returns the covariance between two data sets, sample normalized. | COVARIANCE.S(Data1, Data2) | =COVARIANCE.S(A1:A5, B1:B5) *A1:B5 - Number fields Given that, A = [2, 4, 6, 8, 10] B = [3, 5, 7, 9, 11] | Returns value, 10 |
|
DEVSQ | Returns sum of squared deviations. | DEVSQ(Number1, Number2, ...Number30) | =DEVSQ(A1:A5) *A1:A5 - Number fields Given that, A = [2, 4, 6, 8, 10] | Returns value, 40 |
|
EXPON.DIST | Returns density of a exponential distribution. | EXPON.DIST(Number1, Number2, Boolean) | =EXPON.DIST(A1, B1, C1) *A1 & B1 - Number field *C1 - Text/List field Given that, A1 = 1 B1 = 2 C1 = “false” | Returns value, 0.27 | Boolean:
|
EXPONDIST | Returns density of a exponential distribution. | EXPONDIST(Number1, Number2, Boolean) | =EXPONDIST(A1, B1, C1) *A1 & B1 - Number field *C1 - Text/List field Given that, A1 = 1 B1 = 2 C1 = “false” | Returns value, 0.27 | Boolean:
|
GAUSS | Returns the probability of gaussian variable fall more than this many times standard deviation from mean. | GAUSS(Number) | =GAUSS(A1)+0.5 *A1 - Number field Given that, A1 = 1.5 | Returns value, 0.93 | Values returned is half the normal distribution. Add 0.5 for the entire normal disitribution. |
GEOMEAN | Returns the geometric average. | GEOMEAN(Number1, Number2, ...Number30) | =GEOMEAN(A1, A2) *A1 & A2 - Number fields Given that, A1 = 2 A2 = 2 | Returns value, 2.00 | A negative number does not work in DP in number fields. GEOMEAN() only works in number fields. |
HARMEAN | Returns the harmonic average. | HARMEAN(Number1, Number2, ...Number30) | =HARMEAN(A1:A4) *A1:A4 - Number field Given that, A = [2, 4, 6, 8] | Returns value, 4.00 |
|
LARGE | Returns k-th largest value in a range. | LARGE(Range, K) | =LARGE (A1:A5, 2) *A1:A5 - Number field Given that, A = [10 , 9, 15, 30, 50] | Returns value, 30.00 | K: K = 1 - largest K = 2 - 2nd largest K = n - nth largest |
MAX | Returns the maximum value in a list of arguments. | MAX(Number1, Number2, ...Number30) | =MAX(A1:A5) *A1:A5 - Number field Given that, A = [10 , 9, 15, 30, 50] | Returns value, 50.00 |
|
MAXA | Returns the maximum value in a list of arguments. | MAXA(Value1, Value2, ... Value30) | =MAXA(A1:A5) *A1:A4 - Number/Text/List fields Given that, A1 = “Two” A2 = “3” A3 = “TRUE” A4 = “FALSE” A5 = 10 | Returns value, 10.00 |
|
MEDIAN | Returns the median of a set of numbers. | MEDIAN(Number1, Number2, ...Number30) | =MEDIAN(A1:A5) *A1:A5 - Number field Given that, A = [3, 5, 7, 9, 11] | Returns the value, 7.00 |
|
MIN | Returns the minimum value in a list of arguments. | MIN(Number1, Number2, ...Number30) | =MIN(A1:A5) *A1:A5 - Number field Given that, A = [3, 5, 7, 9, 11] | Returns the value, 3.00 |
|
MINA | Returns the minimum value in a list of arguments. | MINA(Value1, Value2, ... Value30) | =MINA(A1:A5) *A1:A5 - Number field Given that, A1 = “Two” A2 = “3” A3 = “TRUE” A4 = “FALSE” A5 = 10 | Returns the value, 0.00 |
|
SKEW | Returns skeweness of a sample. | SKEW(Number1, Number2, ...Number30) | =SKEW(A1:A5) *A1:A5 - Number field Given that, A = [2, 4, 6, 5, 1] | Returns the value, -0.24 |
|
SKEW.P | Returns skeweness of a population. | SKEW.P(Number1, Number2, ...Number30) | =SKEW.P(A1:A5) *A1:A5 - Number field Given that, A = [2, 4, 6, 5, 1] | Returns the value, -0.16 |
|
SKEWP | Returns skeweness of a population. | SKEWP(Number1, Number2, ...Number30) | =SKEWP(A1:A5) *A1:A5 - Number field Given that, A = [2, 4, 6, 5, 1] | Returns the value, -0.16 |
|
SLOPE | Returns the slope of a linear regression line. | SLOPE(Array1, Array2) | =SLOPE(A1:A3, B1:B3) *A1:B3 - Number field Given that, A = [1, 2, 3] B = [3, 6, 9] | Returns the value, 0.33 | Array1 - array of y values Array2 - array of x values |
SMALL | Returns k-th smallest value in a range. | SMALL(Range, K) | =SMALL(A1:A5, 2) *A1:A5 - Number field Given that, A = [10 , 9, 15, 30, 50] | Returns the value, 10 | K: K = 1 - smallest K = 2 - 2nd smallest K = n - nth smallest |
STDEV | Returns standard deviation of a sample. | STDEV(Value1, Value2, ... Value30) | =STDEV(A1:A5) *A1:A5 - Number field Given that, A = [1, 2, 3, 4, 5] | Returns the value, 1.58 |
|
STDEVA | Returns standard deviation of a sample. | STDEVA(Value1, Value2, ... Value30) | =STDEVA(A1:A5) *A1:A5 - Text field Given that, A = [one, 2, 3, true, false] | Returns the value, 1.30 |
|
STDEVP | Returns standard deviation of a population. | STDEVP(Value1, Value2, ... Value30) | =STDEVP(A1:A5) *A1:A5 - Number fields A = [1, 2, 3, 4, 5] | Returns the value, 1.41 |
|
STDEV.P | Returns standard deviation of a population. | STDEV.P(Value1, Value2, ... Value30) | =STDEV.P(A1:A5) *A1:A5 - Number fields A = [1, 2, 3, 4, 5] | Returns the value, 1.41 |
|
STDEVPA | Returns standard deviation of a population. | STDEVPA(Value1, Value2, ... Value30) | =STDEVPA(A1:A7) *A1:A7 - Number fields A = [1, 2, 3, 4, 5, “true”, “seven”] | Returns the value, 1.66 |
|
STDEV.S | Returns standard deviation of a sample. | STDEV.S(Value1, Value2, ... Value30) | =STDEV.S(A1:A5) *A1:A5 - Number fields A = [1, 2, 3, 4, 5] | Returns the value, 1.58 |
|
STDEVS | Returns standard deviation of a sample. | STDEVS(Value1, Value2, ... Value30) | =STDEVS(A1:A5) *A1:A5 - Number fields A = [1, 2, 3, 4, 5] | Returns the value, 1.58 |
|
VARA | Returns variance of a sample. | VARA(Value1, Value2, ... Value30) | =VARA(A1:A7) *A1:A7 - Number fields A = [1, 2, 3, 4, 5, “true”, “seven”] | Returns the value, 3.24 |
|
VARP | Returns variance of a population. | VARP(Value1, Value2, ... Value30) | =VARP(A1:A5) *A1:A5 - Number fields A = [1, 2, 3, 4, 5] | Returns the value 2 |
|
VAR.P | Returns variance of a population. | VAR.P(Value1, Value2, ... Value30) | =VAR.P(A1:A5) *A1:A5 - Number fields A = [1, 2, 3, 4, 5] | Returns the value 2 |
|
VARPA | Returns variance of a population. | VARPA(Value1, Value2, ... Value30) | =VARPA(A1:A7) *A1:A7 - Number fields A = [1, 2, 3, 4, 5, “true”, “seven”] | Returns the value, 2.77 |
|
VAR.S | Returns variance of a sample. | VAR.S(Value1, Value2, ... Value30) | =VAR.S(A1:A5) *A1:A5 - Number fields A = [1, 2, 3, 4, 5] | Returns the value, 2.5 |
|
VARS | Returns variance of a sample. | VARS(Value1, Value2, ... Value30) | =VARS(A1:A5) *A1:A5 - Number fields A = [1, 2, 3, 4, 5] | Returns the value, 2.5 |
|