All EXCEL functions begin with an equal sign and all EXCEL descriptive statistical functions have at least one range of cells as its argument.

Example: Numerical data is in cells C3:C209. This is the cell range.

=average(C3:C209) will compute the average of all the numbers in C3:C209.

The cell range in this example is C3:C209. The colon is used to denote the cell range.

Univariate descriptive

=average( cell range )

=geomean( cell range)

=median( cell range )

=stdev( cell range )

=count( cell range )

=quartile( cell range, q)

=percentile( cell range, p)

=max( cell range)

=min(cell range)

computes the arithmetic mean computes the geometric mean -antilog of log data mean computes the median computes the standard deviation computes the sample size (n) computes 1st, 2nd, 3rd or 4th quartile (q=1,2,3 or 4). will not work for q > 4.

Gives the 100pth percentile for the data in cell range

0 <= p <= 1 gives the maximum in the cell range gives the minimum in the cell range

Bivariate descriptive

=correl( y cell range, x cell range) computes the Pearson correlation (r) between y & x

=slope( y cell range, x cell range) computes the slope (b) for y = a + b X

=intercept( y cell range, x cell range) computes the intercept (a) for y = a + b X

Gaussian

=normsdist(cell)

=normsinv(cell)

gives the percentile (as a decimal) for a Z score. cell=Z score

Ex: normsdist(1.96)= 0.975. gives the Z score for a percentile. Cell=percentile (as a decimal)

Ex: normsinv(0.975) = 1.96

=normdist(y, mean, SD, cumulative indicator) If cumulative indicator equals 1, gives the percentile (as a decimal) for y where y has a Gaussian distribution with mean and SD. If cumulative indicator equals 0, gives the Gaussian density for y, f(y).

EXCEL mathematics functions

=log10( cell) computes the base 10 log value of cell

=ln(cell)

computes the base e log value of cell

=exp(cell)

computes the antilog (base e) of cell ( ecell )

=10^cell

computes the antilog (base 10) of cell (10cell)

= sqrt(cell) computes the square root of cell

There are many other functions in EXCEL – this is only a small sample.

*functions that produce p values

=TDIST( t, df, 2) – two sided p value for t statistic where df is the degrees of freedom

=CHIDIST( χ2, df ) Chi-Square Distribution, where χ2 is the chi square value and df is the degrees of freedom.

=CHITEST (observed freq range, expected freq range) returns p-value for testing observed versus expected frequencies. Does NOT compute expected frequencies

=FDIST(F, numerator df , denominator df ) - Gives p