Using Excel for statistics

Craig A. Stevens and UoPhx students):

http://www.bioss.sari.ac.uk/smart/unix/mbasexc/slides/frames.htm 
Basic statistics in Excel site.

http://www.mth.pdx.edu/ 
Pick stat 243 Excel tutorial, good on data analysis.

http://www.mathtools.net/ 
Pick "Excel" - then "Data Analysis" (have to register to use this site but has good content)

Excel Worksheets (these worksheet came for a number of different sources, I tried to recreate and simplify them to help you perform the appropriate calculations) CAS:

Descriptive Statistics

 

Probability

 

Discrete Probability

Continuous Probability

Confidence Intervals and Sampling

 

 

Regression Analysis

Nonparametric Test

 

 

Excel Advice

Here is how you use Excel to calculate the basic stats.

Step 1.     Find your original disk with The Excel Operation System on it, you may need it.

Step 2.    Open Excel (Go to help and look up Statistics).  Excel will walk you through the statistics information.  (as an example you may only be interested in doing Descriptive Statistics and Z test.)

Step 3.    You will find the appropriate Excel capabilities on the Main Toolbar, under Tools.

Step 4.     Look at Data Analysis

Step 5.     Select Descriptive Statistics and/or Z Test.  Play around with the functionality of the software, call your class mates, and ask some computer people you know to help you figure out how to manipulate the data.  It will be a great tool.

The following was written by Carla Drake (University of Phoenix, 2004 MBA Student)

Charts allow you to present data entered into the worksheet in a visual format using a variety of graph types. Before you can make a chart you must first enter data into a worksheet. This page explains how you can create simple charts from the data.

Chart Wizard [chart wizard]
The Chart Wizard brings you through the process of creating a chart by displaying a series of dialog boxes.

  1. Put the figures from the text in an Excel Grid like this:

 

    1. The cumulative frequency should be a running total of the frequency column.  This is a formula of taking the Frequency from each line and adding it to the prior total.  I’ve added the cumulative %; this isn’t necessary for the problem, though.

  1. Highlight both the # of Days Absent column (with the header) and the Cumulative Frequency column.  You do this by highlighting the first one, then holding “CTRL” and highlighting the second one. 

 

  1. Click the Chart Wizard button on the standard toolbar to view the first Chart Wizard dialog box.Chart Type - Choose the Chart type (Line in this case) and the Chart subtype if necessary. Click Next.  Note: you can always go back and change the chart type by right-clicking on the chart. 

  1. Chart Source Data - Select the data range (if different from the area highlighted in step 1) and click Next.

  1. Chart Options - Enter the name of the chart and titles for the X- and Y-axes. Other options for the axes, grid lines, legend, data labels, and data table can be changed by clicking on the tabs. Press Next to move to the next set of options.

  1. Chart Location - Click As new sheet if the chart should be placed on a new, blank worksheet or select As object in if the chart should be embedded in an existing sheet and select the worksheet from the drop-down menu.

 

 

  1. Click Finish to create the chart.

Resizing the Chart
To resize the chart, click on its border and drag any of the nine black handles to change the size. Handles on the corners will resize the chart proportionally while handles along the lines will stretch the chart.

 

Moving the Chart
Select the border of the chart, hold down the left mouse button, and drag the chart to a new location. Elements within the chart such as the title and labels may also be moved within the chart. Click on the element to activate it, and use the mouse to drag the element to move it.

 

Chart Formatting Toolbar

[chart toolbar]

Chart Objects List - To select an object on the chart to format, click the object on the chart or select the object from the Chart Objects List and click the Format button. A window containing the properties of that object will then appear to make formatting changes.

Chart Type - Click the arrowhead on the chart type button to select a different type of chart.

Legend Toggle - Show or hide the chart legend by clicking this toggle button.

Data Table view - Display the data table instead of the chart by clicking the Data Table toggle button.

Display Data by Column or Row - Charts the data by columns or rows according to the data sheet.

Angle Text - Select the category or value axis and click the Angle Downward or Angle Upward button to angle the the selected by +/- 45 degrees.

Copying the Chart to Microsoft Word
A finished chart can be copied into a Microsoft Word document. Select the chart and click Copy. Open the destination document in Word and click Paste.

 

Excel and Binomial Probability Distribution

by Vanessa Lacen (UoPhx 2008)

 

Probability distribution computations are used on a large scale to determine possible outcomes in business, medical and personal situations. The probability distributions will assess success or failure. The findings (success or failure) are based on the information one is searching for and not good or bad outcomes (Lind et al., pp 188).  This report will explain how to input binomial probability distributions in Excel 2007 in order to complete the computations.   Excel is a Microsoft Office spreadsheet program created by the Microsoft Office Corporation.

The binomial probability distribution is a discrete probability distribution. Binomial probability distributions have the following characteristics:

1.     An outcome on each trial of an experiment is classified into one of two mutually exclusive categories—a success or a failure. Mutually exclusive means if an item is in one category, it cannot be in the other. For example, one could not be male and female at the same time (Lind, pp 189).

2.     The random variable counts the number of successes in a fixed number of trials (Lind, pp 189).

3.     The probability of success and failure stay the same for each trial (Lind, pp189).

4.     The trials are independent, meaning that the outcome of one trial does not affect the outcome of any other trial. (Lind et al., pp189).

 

Computations for the binomial probability distribution are found by the following formula in the book Statistical Techniques in Business and Economics, 2004, page 189.   

Where:

C denotes a combination.

n is the number of trials.

x is the random variable defined as the number of successes

π is the probability of a success on each trial.

 

The Excel program will perform the calculations using the insert function. 

To get started click on the start menu, highlight the programs folder, go over and  highlight the Microsoft Office folder, highlight Excel and left click on it to open the program. 

After the program is open you are ready to input the binomial probability distribution. The following steps will guide you through this process.

The open Excel page will appear similar to the image shown.

http://gator.dt.uh.edu/~hodgess/Stat1/test2.jpg

In order to complete a trial, select a cell and input the following data in the spread sheet start in cell 1A for this purpose.

A1   X     Probability of Distribution

A2  0

A3  1

A4  2

A5  3

A6  4

A7  5

A8  6

  Compute the probability for the following problem:

     QUOTE  

    n = 6

 

The following steps can be found in the book Basic Statistics Using Excel for Office XP 2005, chapter six by Lind. This method can also be use in Excel 2007. Some of the icons/functions are in different locations in Excel 2007 than earlier versions.

1.     Click on the insert function button       this is found on the formula bar or click the formulas tab and click the insert function button.  

The insert function dialog box will open.

                                                                                                                       

2.     Click the drop down menu arrow of the Or select a category and select Statistical.

3.     Choose the BINOMIST in the Select a function list box and click OK

The BINOMIDST dialog box has the following text boxes to fill:

The Number_s text box is for the number of observes success, referred to as x.

Trials text box is for the number of trials, referred to as n.

Probability_s text box is for the probability of success on each trial, referred to as π.

Cumulative text box is used to indicate whether x, the number of observed successes, is cumulative or not cumulative. In the Cumulative box, key in 1 for true if  you want the cumulative probability that includes the numbers up to and including the value of x. You would key 0 for false (or not cumulative) if x is the number of observed successes only.

 

When you click on BINOMIDST the function arguments box will open.

           

4.     Input the information as follows:

Number_s 0 or corresponding cell such as D4.

Trials 6

Probability_s .10

Cumulative 0 or False or not cumulative in this case.

 

5.     Click Ok. With the trials computed the value is .531441 in cell B2.

 

6.     Select cell B2 and position the cursor at the bottom right corner of the cell until it becomes a thick black plus sign. When the cursor becomes a plus sign, click, hold and drag the cursor to cell B8. The probability amounts will automatically fill in the cells. After this is complete click on the AutoSum icon on the upper left home tab or under the formula tab. The values should always equal 1 or 100 percent.

 

The table above should be completed as follows:

           X                                                                             

 Probability of                                                                                        Occurrence

0

0.531441

 

 

1

0.354294

 

 

2

0.098415

 

 

3

0.01458

 

 

4

0.001215

 

 

5

5.4E-05

 

 

6

0.000001

 

 

 

1

 

 

 

 

 

 

 

In conclusion, this is an example of how to input a binomial distribution into the Excel program. For further information, consult the Microsoft web page as well as the help function (F1) in Excel.

Microsoft Office web page office.microsoft.com/en-us/default.aspx has helpful information and tutorials for the Excel program.

This web site has a tutorial for earlier versions of Excel. I t can also be followed for Excel 2007.http://gator.dt.uh.edu/~hodgess/Stat1/prob1.html.

 


 

 

References

Lind, D. A. & Marchal, W. G. & Wathen, S. A. (2004). Statistical techniques in business and economics, 12e: Chapter 6: Binomial Probability Distribution. New York: The McGraw-Hill Companies.

Lind, D. A. & Marchal, W. G. & Wathen, S. A. (2005). Basic Statistics Using Excel for Office XP for use with Statistical techniques in business and economics, 12e: Chapter 6: Discrete Probability Distribution. New York: The McGraw-Hill Companies.