2007 EXCEL Statistical Functions
CONTENTS I. Getting Started with MS Excel II. Statistical Functions
I. Getting Started with MS Excel
Introduction: Microsoft Excel is a spreadsheet application and is a powerful computation and presentation tool used extensively in just about any desk job. The format is so easy to use and flexible, that everyone uses it daily from administrators to fluid dynamic engineers and yes, even by those who do data analysis and statistics. Below are instructions for one possible way to do things. In Excel there are many ways to do any one thing. Over time, you will become familiar with more and more ways. Use the Help Menu (click the white question mark in the blue circle on the top right of the screen or hit F1) if you need any additional information as this document only covers the very basic functions of Excel. Follow directions labeled Tutorial for a simple example. Open a File/Workbook: To open an Excel file in Excel, either double click on the file icon itself. Alternatively, you can start Excel then open the file. There are several ways to do this. There are two ways to open Excel: 1. Double click on the Excel 2007 icon 2. Go to the Start menu -> Programs -> Microsoft Office 2007 -> Excel Once Excel is open, you need to open the file. This can be done by one of several ways: 1. Click the Office Button (top left corner of the screen) -> Open to browse folders 2. Use the keystrokes Control+O 3. Select the open folder icon from the tool bar to the right of the office button (Warning: this may not be displayed on your copy). Tutorial: Start Excel. Create and Save a New File/Workbook: There are several ways to create a new file in Excel: 1. Click the Office Button -> New -> Blank Workbook. 2. Use the keystrokes Control+N 3. Select the new file icon from the tool bar to the right of the office button (Warning: this may not be displayed on your copy) You can save the new spreadsheet by: 1. Click the Office Button -> New 1
2. Use keystroke Control+S 3. Select the save file icon from the tool bar to the right of the office button. Tutorial: Create a new file (known as a spreadsheet) in Excel. Save the new file on your desktop as xtutorial. Selecting Cells: To select a cell in Excel, use the mouse to click on one. By holding the left mouse button down as you move your mouse, one can select many cells at once. For complicated non-connected cell selection, experiment with holding down the Shift key and the Control key as you select your cells. Cell selection can also be done without the mouse using the Shift key in concert with the arrow keys. Notice if you select the very first cell (in upper left hand corner of spreadsheet) the address of the cell is displayed in the name box (box on left on line above cells). The address of the very first cell is A1. Notice the labels for rows are numbered and the labels for the columns are lettered. These addresses are very important. You can also select whole rows or whole columns with a single mouse click by mouse clicking on the name of the row or column. You can also select groups of rows or groups of columns. You can also change the width of any row or column by moving the mouse curser in between two labels for either the rows or the columns. When you find the right spot, the curser will change to a double arrow. At that point, hold the mouse button down and more the mouse to specify what size you want your row or column. Tutorial: Select cells A1, A2, A3. Deselect them by clicking on a different cell. Select row 3. Now select column D. Deselect the column. Enter Values or Text Into a Cell: Simply select a cell and type numbers or words into a cell. The contents of the cell will display in the formula bar (to the right of the name box). To empty a cell, select the cell and type Backspace or Delete. To empty a group of cells, select the group, and right click to select Clear Contents. If you dont like where something you entered is located, you can select the cells you wish to move and carefully move the mouse curser over the border of the selected region. When the mouse curser turns to an arrow, press and hold the mouse button and simply move the mouse around until you fine where you want your section of cells to go! Sometimes the Format of a cell will change the visual appearance of cell contents. For example, if you enter 3/25/5, Excel may change it to March 25, 2005 automatically. To specify what Format you wish a cell to have, select the cell of group of cells and either right click on your selection to get to Format Cells or use the Menu Item: Home Tab -> Format (in second box from right on Home ribbon) -> Format Cells or use keystroke Control+1. Then specify your desired format use Text format to have Excel leave your character entry exactly as entered.
2
Tutorial: Enter into cell A1 the value 5, into cell A2 the value 7, and in cell A3 the value 9. In cell D1 type, This is my first Excel Spreadsheet. Notice the contents overrun the cell you typed in. Resize the column width for column D (hint: one you get the double arrow, you can simply double click and Excel automatically sizes the column to the largest entry in that column). Move the contents of cell D1 to cell D2. Perform Calculations: Excel is capable of doing just about any calculation you wish. Also, it is programmable and will dynamically update values as you change things in your spreadsheet. If you wish to add two numbers together such as 5 + 7, select a cell that is not used and type, =5+7 and then press Enter (a 12 will appear in the cell). Or instead of typing the 5 and 7 in your expression, you can replace them with a cell address where you may already have numbers stored. Tutorial: Select cell C2. Then enter =A1 + A2 and press Enter (a 12 will appear in cell C2). If you wish to calculate a value based on a column or row of numbers already entered in your spreadsheet (or any subset of numbers anywhere), simply select a cell where you wish your calculated value to go. Then either click the fx (left of the formula bar) or use the Menu Item: Formulas Tab -> Insert Function (large fx on left). Either type a search term in the first box or select a category (e.g. financial, statistical, etc.) and a function (e.g. sum, average, minimum, etc.) A dialogue box will most likely appear. Click on the blue and red button to define what cells to use for your function. Use the mouse to physically select the cells with the values you wish your calculation to use. Press Enter when selected. Notice the sequence of addresses of your selected cells (not the values of the cells) is recorded. Then click on OK and the calculation value will appear in the cell. If you know the exact typing needed for your function as you use it often, you dont need to use the menu at all. You can type it in directly. For example, to sum the first three values in the first column (A), select the fourth cell in column A (cell A4) or any unused cell and type,=sum(A1:A3). This will sum the values starting at cell A1 through A3. Tutorial: Select cell A4 and sum the first three values of column A into that cell. Automatic Propagation of Calculations: One of the most powerful aspects of Excel is its ability to automatically propagate your calculations. For example, lets say you teach a history course and you have 40 students whose names all appear in column A of your spreadsheet. In column B you have each students Homework 1 score, in column C you have all the Homework 2 scores, etc. up to column K for 10 assignments. You wish to use Excel to calculate each students score for the course overall. Since all the homeworks are scored as a percentage, you simply want to average each students scores to determine their final percentage score for the course. Lets say the first student information is listed in the second row since the first row has column heading information such as Name, HW1, HW2, HW3,, HW10, Final Score. You would select cell L2 which is at the end of
3
the row for the first student and type in, =average (B2:K2). Then press Enter. This will calculate the average score for the first student whose scores appear in cells B2 through K2 and display it in cell L2. Now, we wish to automatically propagate this average calculation to the other 39 students without programming for each and every student. To do this, simply select cell L2 as your cell to propagate. Notice the cell is indicated with a border around the cell as usual. However, notice that the lower right hand corner of the cell has a small square on it. Move the mouse curser over this point until it changes to a small line cross. Once the mouse curser is a line cross, hold the mouse button down and drag downward until the 40th student cell is reached (row 41). Release the mouse. Averages for each student are automatically calculated in one move! Tutorial: Select cell B1 and type, =A1 * 5. Press Enter. This will multiply the value in cell A1 by 5 and place it in cell B1 (should get 25). Propagate this new calculation by selecting the cell B1 again, moving the mouse curser until it is a line cross, and dragging down to cell B3 (should get 35, and 45). To calculate these new values times 5 again, select all three new values with the mouse, get the line cross, and drag one column to the right, and release. You should have the column 125, 175, 225. Now propagate the sum at the bottom by selecting cell A4 and propagating it two cells to the right (should get 105, 525). This is a very important function of Excel. Charts and Graphs: Another one of the most commonly used aspects of Excel is its ability to create all types of charts and graphs. They could be pie charts, bar charts, line charts, 3-D charts, etc. The complexity and variations possible are almost limitless. This document only shows how to make the simplest versions to get you started. To create a chart or graph of data, select the data using the mouse. The data should be arranged so that a single variable is in one column (or row). If you have multiple variables you wish to graph all at once, select all of them (but each variable should be in respective columns). Then choose the Menu Item: Insert Tab -> select chart type. For other chart options, you can either select Other Charts -> All Chart Types or click on the small arrow in a square that is located in the bottom right corner of the charts box. Once you select the type of chart you are interested in, you will be given opportunities to choose what type of chart you want, to add labeling to your axes, as well as a host of other options to use. Tutorial: To make a bar chart of the three columns of three values we created so far (not including the sums), select cells A1, A2, A3, B1, B2, B3, C1, C2, C3 all at once. We wish to create a chart, which treats each of the three columns as a different variable. Select the Menu Item: Insert Tab -> Column -> 2-D Clustered Column (first chart in the top row). You can move the chart location on the existing sheet by clicking on the chart, dragging to it a new location and dropping it there, or you can cut and paste it in a new location. To move it to a different sheet or a new sheet, click on Move Chart Location. This brings up a dialog box, and you can specify which sheet in which you would like it placed. Now you can add labels to your axes and select many other options (e.g. legend, data labels, grid lines, etc.). To do this, click on the Layout tab and the preferred option(s). Congratulations, you have created a chart.
4
To Install Analysis Toolpak: For the Statistics courses at Temple University, each student will have to install the Analysis Toolpak on their own computer at home or at work. The computers in the basement of Speakman Hall (available to all students) already have this Toolpak installed. This Toolpak allows for the complex analysis necessary for these courses. It is fortunate that Excel can also perform these high level analyses, so that when you are at work, you will have these techniques available to you. Not all work peers will be familiar with all the high level analyses you will be trained on from these two courses, so this is an important aspect of your skill set which will set you apart from others in the workforce. Learn the material in these courses well. To install the Analysis Toolpak: Office Button -> Excel Options (bottom of box) -> Add-Ins (3rd item from bottom on the menu on the left). If the analysis toolpak is listed under Active Applications, it is installed and you can hit Cancel. If not, it will be listed under Inactive Applications. Highlight it, and click Go. This brings up a dialog box with available Add-Ins. Put a checkmark next to Analysis Toolpak, and check OK. This should prompt a message indicating the Add-in is not currently installed and asking if you would like to install it. Click yes, and follow the directions on the screen. Depending on the original Excel installation, you may have to insert the original Microsoft Office installation disks if your computer requests them (so be prepared). If you are at work, you may need to get assistance from your IT representative (so work on this issue in advance of your needing the Analysis Toolpak). There you have it!
II Statistical Functions
This section presents detailed instructions for Excel for Windows to make it easier for instructors and students to use Excel. Data files are provided in Excel format in the CD for most of the examples, exercises and cases in the textbook. Histogram 1. Type or import the data into column A. 2. In column B type Bins in the first cell and then enter the upper limit of each class (optional). 3. Click Data tab -> Data Analysis -> Histogram. 4. Type the Input Range of the data. Include the cell containing the name of the variable (if applicable). 5. Type the Input Range of the bins. 6. If the names of the variables have been included in the first row of the ranges click Labels. 7. Click Chart Output and OK. This will create a chart with gaps between the rectangles. 8. To remove the gaps between the bars place the cursor over one of the rectangles and click the right button of the mouse. 9. Click (with the left button) Format Data Series 10. Click Options, move the pointer to Gap Width: and change the number from 150 to 0. Click OK.
5
Bar Chart There are two ways to make a bar chart in Excel: A. Proceed through the first seven steps in constructing a histogram as described above. Use the codes representing the categories as the upper limits of the bins. The result is a bar chart. B. If you have determined the frequency with which each value occurs follow the instructions below. 1. Type or import the frequencies into a column. 2. Highlight the frequencies, click the Insert tab -> Bar -> 2-D Clustered Bar (first chart in top row). 3. If you wish you can include the name of the category in a cell at the top of the column. Pie Chart 1. Type or import the frequencies into a column. 2. Highlight the frequencies, click the Insert tab -> Pie, -> Pie (first chart in top row). Scatter Diagram 1. Type or import the data into two adjacent columns. Store variable X in the first column and variable Y in the next column. 2. Highlight the data 3. Click the Insert tab -> Scatter -> Scatter Only with Markers (first chart in top row). 4. If you wish to label the chart and axis, click Layout tab -> Chart Title or Axis Titles (Primary Horizontal or Primary Vertical) and fill in the boxes. 5. To eliminate the horizontal lines that automatically appear, click Layout tab -> Gridlines -> Primary Horizontal Gridlines -> None. Descriptive Statistics 1. Type or import the data. 2. Click Data tab -> Data Analysis -> Descriptive Statistics. 3. Specify the Input Range. 4. Click Labels in First Row if applicable, check Summary Statistics and click OK. Covariance/Correlation 1. Type or import the data into two adjacent columns. 2. Click Data tab -> Data Analysis -> Covariance. 3. Type the Input Range. 4. Click Labels in First Row if applicable, and click OK. 5. For correlation, click Correlation instead of Covariance in the instructions above.
Binomial Probabilities 1. Click Formulas tab - > fx, and either enter binomial in the search box or pick Category: Statistical, and Function Name: BINOMDIST. Click OK.
6
2. Type the value of x (Number_s), the number of trials, n (Trials), the probability of success, p (Probability_s), and true (Cumulative) to yield a cumulative probability or false for an individual probability. The probability appears on the right side of the dialog box. Clicking OK will print the probability in the active cell. Normal Probabilities To compute cumulative normal probabilities P(X<x), proceed as follows: 1. Click Formulas tab - > fx, and either enter normal in the search box or pick Category: Statistical, and Function Name: NORMDIST. Click OK. (Note: do not confuse NORMDIST with NORMSDIST. They are two different functions). 2. Type the value of x (X), the mean (Mean), the standard deviation (Standard_dev), and true (Cumulative) to yield a cumulative probability. The probability appears on the right side of the dialog box. Clicking OK will print the probability in the active cell. To determine a value of X given a cumulative probability, follow these instructions: 1. Click Formulas tab - > fx, and either enter inverse normal in the search box or pick Category: Statistical, and Function Name: NORMINV. Click OK. 2. Type the cumulative probability (Probability), the mean (Mean), the standard deviation (Standard_dev). The value of x will appear in the dialog box. Clicking OK will print the probability in the active cell. Testing Two Independent Means 1) Retrieve and open data file from Folder or CD-ROM 2) Click: Data tab -> Data Analysis - > t-Test: Two-Sample Assuming Equal Variances 3) Input: Variable 1 Range 4) Input: Variable 2 Range 5) Enter Hypothesized Mean Difference (e.g., zero) 6) Check "labels" box if variable name used 7) Enter Desired Alpha Level (to obtain 1 & 2 tailed t critical values in output) 8) Output Display Options select location to display output: cell range (select staring point), new worksheet page (the default), or new workbook (i.e. a new Excel file) 9) Click "OK" Conduct F test for Homogeneity of Variance 1) Retrieve and open data file from Folder or CD-ROM 2) Click: Data tab -> Data Analysis - > F-Test Two-Sample For Variances 3) Input: Variable 1 Range 4) Input: Variable 2 Range 5) Check "labels" box if variable name used 6) Enter Desired Alpha Level (to obtain F critical value in output) 7) Output Display Options select location to display output: cell range (select staring point), new worksheet page (the default), or new workbook (i.e. a new Excel file) 8) Click "OK"
7
Testing Two Dependent Means (Matched-Pairs) 1) Retrieve and open data file from Folder or CD-ROM 2) Click: Data tab -> Data Analysis - > t-Test: Paired Two Sample for Means 3) Input: Variable 1 Range 4) Input: Variable 2 Range 5) Enter Hypothesized Mean Difference (e.g., zero) 6) Check "labels" box if variable name used 7) Enter Desired Alpha Level (to obtain 1 & 2 tailed t critical values in output) 8) Output Display Options select location to display output: cell range (select staring point), new worksheet page (the default), or new workbook (i.e. a new Excel file) 9) Click "OK" Testing ANOVA SINGLE FACTOR 1) Retrieve and open data file from Folder or CD-ROM 2) Click: Data tab -> Data Analysis - > Anova: Single Factor 3) Input: Range 6) Check "labels in first row" box if variable name used 7) Enter Desired Alpha Level (to obtain F critical value in output) 8) Output Display Options select location to display output: cell range (select staring point), new worksheet page (the default), or new workbook (i.e. a new Excel file) 9) Click "OK" Obtain Simple Regression Analysis and Plots 1) Retrieve and open data file from Folder or CD-ROM 2) Click: Data tab -> Data Analysis - > Regression 3) Input: Y Range 4) Input: X Range 5) Check "labels" box if variable names used 6) Enter degree of confidence (95% is default value) 7) Output Display Options select location to display output: cell range (select staring point), new worksheet page (the default), or new workbook (i.e. a new Excel file) 8) Other Output Options select from the following as desired: residuals, standardized residuals, residual plots, line fit plots, or normal probability plots 9) Click OK (provides Regression Statistics, ANOVA, etc.) Obtain Intercorrelations Among all Regression Variables (Correlation Matrix) 1) Retrieve and open data file from Folder or CD-ROM 2) Click: Data tab -> Data Analysis - > Correlation 3) Data Range: Select Y and all X variables simultaneously using click & drag method (Note: columns must be adjacent to each other.) 4) Check either column or row to indicate data grouping (column is the default). 5) Check "labels in first row" box if variable names used 6) Output Display Options select location to display output: cell range (select staring point), new worksheet page (the default), or new workbook (i.e. a new Excel file)
8
7) Click OK (note, does not provide p-value(s) or sample size) Obtain Multiple Regression Analysis and Plots 1) Retrieve and open data file from Folder or CD-ROM 2) Click: Data tab -> Data Analysis - > Regression 3) Input: Y Range 4) Input: X Range select all columns with predictors simultaneously (Note: columns must be adjacent to each other.) 5) Check "labels" box if variable names used 6) Enter degree of confidence (95% is default value) 7) Output Display Options select location to display output: cell range (select staring point), new worksheet page (the default), or new workbook (i.e. a new Excel file) 8) Other Output Options select from the following as desired: residuals, standardized residuals, residual plots, line fit plots, or normal probability plots 9) Click OK
9