SCM homework.

© The Pennsylvania State University

Excel Homework 6 Tutorial Written by Peter Chamberlain

Directions for Making a Scatterplot 1. Select the range by highlighting the two columns you wish to analyze. The independent variable

must be located to the left of the dependent variable. If necessary, you may need to move a column in order to make the column on the left the independent variable. To make things easier, you can put the columns next to each other and click on the Column Letter for the independent variable and drag to the Column Letter for the dependent variable. This will highlight both columns.

2. Click Insert and then on the image for the Scatterplot in the chart section (circled below). Click the upper left scatterplot when given the option.

3. Excel will default to titling the chart to the name of your independent variable. Click in the title so that you can edit the title and call the chart Scatterplot.

4. Clicking in the chart will cause the plus sign, paint brush, and filter on the right-hand side to

appear. Click on the plus sign and a menu for Chart Elements such as grid lines, titles, axis titles, etc., will appear. Name your X axis after your independent variable and your Y axis after your dependent variable.

5. If your Scatterplot has a Legend or Gridlines, you can remove those by clicking Legend or Gridlines as appropriate from the plus sign and unchecking any checked boxes for those.

0.000

0.100

0.200

0.300

0.400

0.500

0.600

0.700

0.000 0.100 0.200 0.300 0.400 0.500

Scatterplot

SLG

© The Pennsylvania State University

Installing the Data Analysis ToolPak (in case it isn’t already installed in your Excel) – see Excel Tutorial 1 Directions for Getting Correlation 1. Open the data set you will need to use for finding correlation. Make sure that your columns are

next to each other and that the independent variable column is immediately to the left of the dependent variable column.

2. Highlight both columns by clicking on the Column Letter for the independent variable and dragging to highlight the Column Letter for the dependent variable.

3. Click on Data.

4. Click on Data Analysis.

5. Click on Correlation and click OK.

6. Confirm that the Input Range is the same as the columns you have selected.

7. Check the box for Labels in First Row.

8. Click OK. Directions for Running a Regression using Data Analysis 1. Open the data set you will need to use for running regression.

2. Click on Data.

3. Click on Data Analysis.

4. Click on Regression and click OK. The following dialogue box will appear.

© The Pennsylvania State University

5. Choose your input and output ranges. The range can only go as far as you have data and the number of rows in your input range must equal the number of rows in your output range. You will select each range individually. You can start at the top of the column and scroll down or you can start at the top of the column and use CTRL, Shift, and the Down arrow together to get to the bottom of the column.

6. Check the box for labels if your columns have Labels (they probably will). You can ignore the Confidence Level section.

7. Click OK. If you chose the option to have the output on a New Worksheet Ply, it will show your output on that new ply (or tab if you want to call it that). That option is generally preferred for a cleaner look and also because you may have massively large sets of data that you can work with.

8. Do not check the boxes for Residuals and Normal Probability. Checking these boxes will give you a significant amount of additional output that you will not need.

Excel Function: STDEV.S Reminder: Functions start with an equals sign and contain arguments. Functions can be typed into any empty cell. STDEV.S is the function to find the standard deviation of a sample. P-Values in Scientific Notation Very small p-values are often written in scientific notation in Excel output. For example, the p-value .000000000043 would be written as 4.3E-11.