Linear Regression In Excel

1. Linear Regression In Excel

2. Linear Regression • In this presentation you will learn the following: • How to make a scatter plot in Excel (Click Here) • How to add the line of best fit (or trend line) to your scatter plot (Click Here) • How to put multiple best fit lines on the same graph (Click Here)

3. Linear Regression • Open an Excel file and type the data on page 41 of your workbook

4. Linear Regression • Click Insert, then Chart

5. Linear Regression Click Next

6. Linear Regression Click Next

7. Linear Regression Click

8. Linear Regression • And the results . . .

9. Linear Regression • Left-click on a data point. You’ll see that several of the data points are highlighted.

10. Linear Regression • Once you selected your data points, right-click, choose “Add Trendline”

11. Linear Regression • We want a linear trendline since the plotted data appears be linear Click Click

12. Linear Regression Click Click

13. Linear Regression • And the results . . .

14. Linear Regression • A word about the correlation coefficient (R): • Excel can only display the square the correlation coefficient • To find the correlation coefficient value you need to take the square root • Then, depending on whether the slope of of your trendline is positive or negative you’ll be able say whether R is positive or negative

15. Linear Regression • What if we wanted to add multiple trendlines to the same graph? • The U of A Factbook contains interesting information about the demographics of the university

16. Linear Regression • An excerpt of data is shown below:

17. Linear Regression • Let’s suppose we wanted to compare the trends of resident and non-resident tuition and fees from 1995 to 2004 • Go ahead and type in the data in a blank Excel worksheet

18. Linear Regression • Your data should look something like:

19. Linear Regression • Making the scatter plots Highlight the cells where the resident values are stored Click Click

20. Linear Regression Since we’re going to plot two sets of data we’ll want to title our plot of the residents Highlight cells where the years are stored Click add to so we can do the same thing for the non-residents

21. Linear Regression Title this plot of the non-residents Highlight the cells where the year values are stored Highlight Non-Resident Tuition & Fee values Click

22. Linear Regression • Title your plots as shown below: Click

23. Linear Regression • And the results . . .

24. Linear Regression • Now as before, click on the data points for each plot and add a trendline • The results of this are displayed on the next slide

25. Linear Regression • And the results . . .