Essay on EXCEL Statistical Functions

Submitted By EvanLi
Words: 651
Pages: 3

Handy EXCEL statistical functions
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…