Project

HOW TO MAKE A SCATTER PLOT, GRAPH THE REGRESSION LINE, AND FIND THE CORRELATION COEFFICIENT,

By Dr. Feliks KOSTANYAN

HOW TO MAKE A SCATTER PLOT, GRAPH THE REGRESSION LINE, AND FIND THE CORRELATION COEFFICIENT, By Dr. Feliks Kostanyan

Page 1 of 5

In this handout we will learn how to make a scatter plot, given the data set, and find the

regression line equation. We do not focus here on how to interpret the results.

Open the Excel book and enter the data in the columns.

We can find the correlation coefficient immediately if we use the “CORREL” function on Excel.

For that, we first enter the words “Correlation Coefficient” somewhere in the book:

In the cell next to it start typing CORREL, and you will get the function with the parentheses

opened that suggest the two sets of data: (array 1, array 2

What you need to do now is to highlight the first set of data (which in our case is in column B

from B3 to B10 - do not highlight the name of the column!), and when you do so, the array 1

will be replaced byB3:B10:

HOW TO MAKE A SCATTER PLOT, GRAPH THE REGRESSION LINE, AND FIND THE CORRELATION COEFFICIENT,

By Dr. Feliks KOSTANYAN

HOW TO MAKE A SCATTER PLOT, GRAPH THE REGRESSION LINE, AND FIND THE CORRELATION COEFFICIENT, By Dr. Feliks Kostanyan

Page 2 of 5

Then you willl need to enter the comma , after B10, and repeat the highlighting of the second column of data:

Now you need to click “Enter”, or close the parentheses and then “Enter”, and you will get this

picture:

Note that in the window f(x) you can see what formula is being used, provided you have the cell

highlighted (Number of cell is shown in the left window).

HOW TO MAKE A SCATTER PLOT, GRAPH THE REGRESSION LINE, AND FIND THE CORRELATION COEFFICIENT,

By Dr. Feliks KOSTANYAN

HOW TO MAKE A SCATTER PLOT, GRAPH THE REGRESSION LINE, AND FIND THE CORRELATION COEFFICIENT, By Dr. Feliks Kostanyan

Page 3 of 5

Next, we will graph the scatter plot of this pairs of data.

For that, we highlight the both columns with the data, and then click on “INSERT” tab on the

top, then find and click on the “Scatter” tab.

Choose the first type and click on it:

HOW TO MAKE A SCATTER PLOT, GRAPH THE REGRESSION LINE, AND FIND THE CORRELATION COEFFICIENT,

By Dr. Feliks KOSTANYAN

HOW TO MAKE A SCATTER PLOT, GRAPH THE REGRESSION LINE, AND FIND THE CORRELATION COEFFICIENT, By Dr. Feliks Kostanyan

Page 4 of 5

Open the “Chart layouts” box:

Choose the one with f(x) on it (it is the 9 th

one in this version of Excel) and you will get the full

graph with the regression line formula on it:

All you need to do now is:

1) type in the Chart Title,

2) titles of the axes (x – stands for the expenses, y stands for sales) , and

3) move the regression line equation so it can be visible, and label it.

HOW TO MAKE A SCATTER PLOT, GRAPH THE REGRESSION LINE, AND FIND THE CORRELATION COEFFICIENT,

By Dr. Feliks KOSTANYAN

HOW TO MAKE A SCATTER PLOT, GRAPH THE REGRESSION LINE, AND FIND THE CORRELATION COEFFICIENT, By Dr. Feliks Kostanyan

Page 5 of 5

Note that the chosen version gives us also the value of R^2, which exactly matches the value of

R we got in the very beginning. (0.91291^2=0.8383)