# Notes on Using Microsoft Excel

Document Sample

```					                              Notes on Using Microsoft Excel

These are some general formulas and directions for using Excel consistent with the
material we will cover in class.

Formulas:

Frequency Distribution for qualitative data

=countif(select the range of cells you want excel to consider when counting, select the
variable you want it to count)

Note: When you click the lower-right hand corner of a cell and drag, excel will apply that
formula to the range you drag across. Please note that when excel applies the formula, it
will alter the input data in the same manner that you applied the formula. For example, if
you want to apply the formula down, excel will shift the input data down by the same
number of cells (likewise, if you apply the formula horizontally, excel will shift the input
data horizontally). Sometimes this is desirable, sometimes it is not. To ensure that excel
does not alter the input data, you will need to apply dollar signs before the variable you
wish to hold constant. e.g. \$A11 will hold column A constant if you apply the formula
horizontally (if you apply the formula vertically, this will be of no use.) e.g. A\$11 will
hold row 11 constant if you apply the formula vertically (if you apply the formula
horizontally, this will be of no use.) e.g. \$A\$11 will hold cell A11 constant. If you desire
this you can highlight cell All in your formula and press F4.

Frequency Distribution for quantitative data

First, select the entire range of cells where you want excel to display you frequency
distribution. Then click in the formula bar and enter the following:

=frequency(select the range of cells you want excel to consider when counting, {the
largest value of the first class, the largest value of the second class, etc.}). After entering
this formula, you must hold down the Shift Key and CTRL key while pressing Enter.

Mean

=average(select the range of cells from which you want Excel to calculate a mean)

The following formulas require to select the range of cells in a manner similar to the
mean.

Standard Deviation for a Population

=stdevp(range)
Standard Deviation for a Sample

=stdev(range)

Variance for a Population

=varp(range)

Variance for a Sample

=var(range)

Mode

=mode(range)

Median

=median(range)

Combination

=combin(N,n)

Permutation

=permut(N,n)

Exponentials

Use the caret symbol (^) to raise any number to a designated power. For example, to raise
10 to the 4th power, type: =10^4

Square Root

=sqrt(n)

Other Roots

It is easiest to simply use the exponential function here. The distinction is that the
exponent will be a fraction. Taking the cube root of 125 can be done by the following:
=125^(1/3).
Equation for Returning a Probability Mass Function
This essentially solves the probability density function for the normal distribution and
provides you a value for f(x) given a value of x, a mean, and a standard dev.

***This should be used when you want to ascertain the height of the curve given the
value on the x-axis. It will not tell you the probability that x is less than, greater than, or
equal to some value.

=normdist(value for x, mean, s.d., false)

Equation for Returning a Cumulative Probability Function

***This function will tell you the probability that x lies below some specified value (it is
a cumulative probability). It is useful for answering the question, “what is P(x<=a
particular value)

=normdist(value of x, mean, s.d., true)

Unfortunately, this equation is cumulative and only tells you the probability that x will be
equal to or less than some value. To find whether x is => some value, you need to take
advantage of the fact that the total area under the curve is equal to one. Therefore, the
probability that x is greater than some value can be obtained by the following:

=1-normdist(value of x, mean, s.d., true)

If we want the probability that x lies between two values, we need to take advantage of
the fact that x will lie between within the cumulative distribution of the upper bound
value (B), but X does lie within entire cumulative distribution. Also, we know that the
cumulative distribution at the upper bound value (B) will incorporate the cumulative
distribution of the lower bound value (A), but that X will not be included within the
cumulative distribution of the lower bound value (A). Therefore, we need to subtract the
cumulative probability at point A from the cumulative probability at point B.

=normdist(value for B, mean, s.d., true)-normdist(value for A, mean, s.d., true)

Note: Excel has many ways to skin a cat. You will find that you can arrive at these
answers in many ways with differing formulas or built-in excel features.

How to find a value of X given a probability.

Let’s say that you want to find what score will constitute the bottom 10% of the class.
You can use the following function:

=norminv(probability (e.g. .1, mean, s.d)
This will return a score like 68, which tells you that a 68 or below is in the bottom 10%.

If you want to find a score that reflects the top 10%, you must keep in mind that the
answer returns is cumulative, so you need to find a score that is greater than or equal to
90% of the other scores.

Calculating the Z-Score

To calculate a z-score- You are subtracting the x value from the mean and dividing the
result by the standard deviation.

=(value of x-mean)/s.d.

To obtain a cumulative probability for a zscore, simply enter the following function:

=normsdist(zscore)

Finding a probability that the z score is above a certain value or between values is similar
to the approach used for the normal distribution.

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 0 posted: 9/15/2012 language: English pages: 4
How are you planning on using Docstoc?