Dashpivot supports Excel-style statistical functions inside Default Tables and Prefilled Tables. These formulas are commonly used for:
Descriptive statistics
Trend and correlation analysis
Probability calculations
Data summarisation
Measures of central tendency and variation
Averages & central tendency
AVERAGE()
Purpose: Returns the arithmetic mean of a range.
Formula to use:
=AVERAGE(A1:A5)
Example:
If A1:A5 = 10, 20, 30, 40, 50
=AVERAGE(A1:A5) returns 30
AVERAGEA()
Purpose: Returns the average including logical values and text (TRUE=1, FALSE=0, text=0).
Formula to use:
=AVERAGEA(A1:A5)
Example:
If A1:A5 = 10, TRUE, FALSE, 20, "Text"
=AVERAGEA(A1:A5) returns 6.2
AVERAGEIF()
Purpose: Returns the average of values that meet a condition.
Formula to use:
=AVERAGEIF(A1:A5,">10")
Example:
If A1:A5 = 5, 15, 25, 8, 12
=AVERAGEIF(A1:A5,">10") returns 17.33
MEDIAN()
Purpose: Returns the middle value in a range.
Formula to use:
=MEDIAN(A1:A5)
Example:
If A1:A5 = 10, 20, 30, 40, 100
=MEDIAN(A1:A5) returns 30
GEOMEAN()
Purpose: Returns the geometric mean of a range.
Formula to use:
=GEOMEAN(A1:A3)
Example:
If A1:A3 = 2, 8, 4
=GEOMEAN(A1:A3) returns 4
HARMEAN()
Purpose: Returns the harmonic mean of a range.
Formula to use:
=HARMEAN(A1:A3)
Example:
If A1:A3 = 2, 4, 8
=HARMEAN(A1:A3) returns 3.43
AVEDEV()
Purpose: Returns the average of absolute deviations from the mean.
Formula to use:
=AVEDEV(A1:A5)
Example:
If A1:A5 = 10, 20, 30, 40, 50
=AVEDEV(A1:A5) returns the average deviation value.
Counting functions
COUNT()
Purpose: Counts numeric values only.
Formula to use:
=COUNT(A1:A5)
Example:
If A1:A5 = 10, "Text", 20, blank, 30
=COUNT(A1:A5) returns 3
COUNTA()
Purpose: Counts all non-empty cells.
Formula to use:
=COUNTA(A1:A5)
Example:
If A1:A5 = 10, "Text", 20, blank, 30
=COUNTA(A1:A5) returns 4
COUNTBLANK()
Purpose: Counts empty cells.
Formula to use:
=COUNTBLANK(A1:A5)
Example:
If A1:A5 contains 2 empty cells
=COUNTBLANK(A1:A5) returns 2
COUNTIF()
Purpose: Counts cells meeting one condition.
Formula to use:
=COUNTIF(A1:A5,">10")
Example:
If A1:A5 = 5, 15, 25, 8, 12
=COUNTIF(A1:A5,">10") returns 3
COUNTIFS()
Purpose: Counts cells meeting multiple conditions.
Formula to use:
=COUNTIFS(A1:A5,">10",B1:B5,"Yes")
Example:
Returns the count of rows where A > 10 and B = "Yes".
Maximum & minimum
MAX() / MIN()
Purpose: Returns the largest or smallest value in a range.
Formula to use:
=MAX(A1:A5)
=MIN(A1:A5)
Example:
If A1:A5 = 5, 15, 25, 8, 12
=MAX(A1:A5) returns 25
=MIN(A1:A5) returns 5
LARGE()
Purpose: Returns the k-th largest value.
Formula to use:
=LARGE(A1:A5,2)
Example:
Returns the 2nd largest value in the range.
SMALL()
Purpose: Returns the k-th smallest value.
Formula to use:
=SMALL(A1:A5,2)
Example:
Returns the 2nd smallest value in the range.
Variance & standard deviation
STDEV.S()
Purpose: Returns standard deviation for a sample.
Formula to use:
=STDEV.S(A1:A5)
STDEV.P()
Purpose: Returns standard deviation for a population.
Formula to use:
=STDEV.P(A1:A5)
VAR.S()
Purpose: Returns variance for a sample.
Formula to use:
=VAR.S(A1:A5)
VAR.P()
Purpose: Returns variance for a population.
Formula to use:
=VAR.P(A1:A5)
Correlation & regression
CORREL()
Purpose: Returns the correlation coefficient between two ranges (-1 to 1).
Formula to use:
=CORREL(A1:A5,B1:B5)
COVARIANCE.S()
Purpose: Returns sample covariance between two ranges.
Formula to use:
=COVARIANCE.S(A1:A5,B1:B5)
SLOPE()
Purpose: Returns the slope of the regression line.
Formula to use:
=SLOPE(A1:A5,B1:B5)
Distribution & probability
EXPON.DIST()
Purpose: Returns the exponential distribution value (PDF or CDF).
Formula to use:
=EXPON.DIST(A1,B1,TRUE)
GAUSS()
Purpose: Returns the probability that a standard normal random variable is between 0 and a given value.
Formula to use:
=GAUSS(A1)
Shape of distribution
SKEW.S()
Purpose: Returns skewness of a sample.
Formula to use:
=SKEW.S(A1:A5)
SKEW.P()
Purpose: Returns skewness of a population.
Formula to use:
=SKEW.P(A1:A5)
Other statistical tools
DEVSQ()
Purpose: Returns the sum of squared deviations from the mean.
Formula to use:
=DEVSQ(A1:A5)
Using statistical formulas in Dashpivot helps automate reporting, performance analysis, forecasting, and data-driven decision-making.