Module 1.6 Notes
Useful Excel Functions

* all functions in Excel start with '=' sign

=AVERAGE(array)--- Mean value

=STDEV(array)--- Standard deviation of a sample

=QUARTILE (array,1)---where 1 is for Q1; 3 for Q3. Quartiles.

=PERCENTILE(array,k)--- kith percentile in decimal

=STANDARDIZE(x,mean,stdev)--- compute Z score

=NORMSINV(probability)--- returns Z score, negative or positive, for a given probability

=NORMSINV(confidence level + alpha/2)--- returns Z score for a desired confidence level

=NORMSDIST(Z score)--- probability of less than Z occur

=NORMDIST(x,mean,stdev,TRUE)--- probability of less than x occur

=1-NORMSDIST(Z)--- probability of a score to exceed a given Z score

=CONFIDENCE(alpha,stdev,sample size)--- sampling error computed for a Normal dist.

=TINV(alpha,n-1)--- computes t value for a given confidence level and sample size n

P-values in Excel

for a Z test

Lower (Left) tail test:

=NORMSDIST(Z)

Upper (Right) tail test:

=1-NORMSDIST(Z)

2-tail test:

=2*(1-NORMSDIST(ABS(Z))

for a t test

Lower (Left) tail test AND:

t-value is positive =1-TDIST(ABS(t-value),n-1*,number of tails**)

t-value is negative =TDIST(ABS(t-value),n-1,number of tails**)

Upper (Right) tail test AND:

t-value is positive =TDIST(ABS(t-value),n-1,number of tails**)

t-value is negative =1-TDIST(ABS(t-value),n-1,number of tails**)

2-tail test:

=TDIST(ABS(t-value),n-1, number of tails***)

*where n = sample size and n-1 = degrees of freedom

** number of tails = 1

*** number of tails = 2

Click here to view a Standard Normal Distribution Table

| Return to Module Overview | Return to top of page |