Question

Discussion

How to use Excel to compute the descriptive statistics and/or calculate probabilities for a Normal/Binomial distribution?

Answer 1

Descriptive statistics are one of the fundamental “must knows” with any set of data. It gives you a general idea of trends in your data including:

• The mean, mode, median and range.
• Variance and standard deviation.
• Skewness.
• Count, maximum and minimum.

Step 1: Type your data into Excel, in a single column. For example, if you have ten items in your data set, type them into cells A1 through A10.

Step 2: Click the “Data” tab and then click “Data Analysis” in the Analysis group.

Step 3: Highlight “Descriptive Statistics” in the pop-up Data Analysis window.

Step 4: Type an input range into the “Input Range” text box. For this example, type “A1:A10” into the box.

Step 5: Check the “Labels in first row” check box if you have titled the column in row 1, otherwise leave the box unchecked.

Step 6: Type a cell location into the “Output Range” box. For example, type “C1.” Make sure that two adjacent columns do not have data in them.

Step 7: Click the “Summary Statistics” check box and then click “OK” to display Excel descriptive statistics. A list of descriptive statistics will be returned in the column you selected as the Output Range.

PROBABILITIES AND INVERSE PROBABILITIES

We consider the standard normal distribution as an example.

Let X be random variable, x be a value of the random variable, and p be a probability. Then:

• A probability such as Pr(X <= x) is given by the cumulative distribution function.
  So the Excel command includes "DIST"
  e.g. TDIST for the T distribution
  e.g. NORMSDIST for the standard normal distribution
  e.g. NORMDIST for the normal distribution

• A value of x such that Pr(X <= x) = p for some specified value of p is called the inverse of the cumulative distribution function.
  So the Excel command includes "INV"
  e.g. TINV for the T distribution
  e.g. NORMSINV for the standard normal distribution
  e.g. NORMINV for the normal distributio

STANDARD NORMAL PROBABILITIES AND INVERSE-PROBABILITIES

These are less used than the t-distribution in statistical analysis of economics data.
These use the NORMDIST and NORMINV functions.

IMPORTANT: The format and results of these commands differ from those for the normal.
NORMDIST directly gives the cumulative distribution function i.e. Pr(X <= x), whereas TDIST instead gives the right tail, i.e. Pr(X > x) !!
NORMINV considers the inverse of the probability of being in both tails, similar to TINV.

1. Find Pr(X <= 1.9) when x is standard normal (i.e. normal with mean=0 and variance=1).

Choose Formulas Tab | Function Library Group | More Functions | Statistical | NORMDIST.
Fill in the Function Arguments Tab with Z value of 1.9.
This gives result that Pr(X <= 1.9) = 0.9713.

Much simpler is to directly type in the cell =NORMSDIST(1.9) and hit <enter> to get Pr(X <= 1.9) = 0.9713.

2. Find the value x* such that Pr(X <= x*) = 0.9 when x is standard normal.

Choose Formulas Tab | Function Library Group | More Functions | Statistical | NORMDIST.
Fill in the Function Arguments Tab with probability value of 0.9.
This gives result that x* = 1.2816, i.e. Pr(X <= 1.2816) = 0.9.

Much simpler is to directly type in the cell = NORMSINV(0.9) and hit <enter> to get x* = 1.2816.

Calculate Normal Distribution Probability in Excel: Less than:

Step 1: Click an empty cell.

Step 2: Click “Insert Formula”.

Step 3: Type “Normdist” into the search box and then click “Go.”

Step 4: Select “NORMDIST” from the list and then click “OK” to open the Function Arguments window.

Step 5: Enter your data into the box. For this example, type “600” in the X box, “500” in the Mean box, “100” in the Standard Deviation box and “true” in the cumulative box..

Step 6: Click “OK.”. This returns 0.84134474 in the cell you clicked in Step 1, which is the probability of getting under 600 ppm.

Calculate Normal Distribution Probability in Excel: More than

Step 7: Subtract your answer from Step 7 (above) from 1:
1-0.84134474= 0.158653.

Calculate Normal Distribution Probability in Excel: Between

Step 8: Repeat Steps 1 through 8 for the second value, which for this example is 400. The probability is 0.15865526.
Step 9: Subtract the larger probability from the smaller probability. In this example:
0.84134474 – 0.15865526 = 0.68269