Using Excel for Data Analysis: Simple Linear Regression

Name: Using Excel for Data Analysis: Simple Linear Regression
Author: Cliff Brozo

Learn to perform simple linear regression analysis to find trends within your data.

Preview the first lesson free — get full access to all 3 lessons.

Course: On-Demand

Welcome back to Knowledge City's course on using Excel for data analysis. I'm Cliff Brozo, I'm your instructor. And in today's lesson we're going to talk about the difference between linear and non-linear functions. First, a little explanation. Linear functions are when decision variables, anything that we're going to change, is multiplied by a constant. Now, if we see this in a chart, we would see a straight line and that's where the term linear comes from. If I put some numbers in a chart and I see that X changes from 3 to 6 to 9 to 27 to 33, I can calculate the difference of changes in X. As I can see, it starts out at 3. If it goes to 6, it's increased by 3. If it goes to 9, it's increased by another 3. If it goes to 27, it's increased by 18. If we have another variable called Y and that changes, we can calculate whether the change is proportional to the change in X, and by that I mean Y is going up by 9, again by 9. Then it goes up by 54, and then it goes up by 18. So how can we tell when all of these numbers are different? Well, we can calculate the change in Y divided by the change in X and see that the number is 3. The change in Y divided by the change in X is 3 again. 54, the change in Y divided by 18. The change in X is again 3. So if all of these numbers are the same then we're looking at a linear function. Non-linear functions are when the change in input does not proportionally change the output, and we care about non-linear functions because most systems are inherently non-linear. For example, we tell stories all the time out of chronological order. We flash forward, we flashback, there are dream sequences, foreshadowing, and these are non-linear stories. Let's see what this looks like in Excel. We have a spreadsheet that shows bicycles and tires and questions whether there's a relationship between the two. As we can see, the number of bicycles sold changes, and we call that value X. So it goes from 3 to 9, and increase of 6. Another increase of 6 brings it to 15. Another increase of 12 brings it to 27. There are also sales of tires, and those increases go from 12, 12, 24, and 12. Once again, if we do some division and we take Y divided by X, the result produces the same number, 2, 2, 2, 2. All of these Y divided by X values end up in 2. If we chart this information, that gives us a straight line. And while the lines don't have to be parallel straight because the changes are different, they are proportionally straight and the fact that this is a straight line tells us that this is a linear equation. When we look at non-linear equations, we have the same number of bicycles sold and all of those numbers stay the same. The tires, however, have different values. And when we calculate the tires divided by bicycles you can see we get different numbers. And what that does is it produces a line that is not straight. Both the tires and the bicycles are not straight lines. And if I change one of these values, let's say I change the tires to something like 73, watch the orange line, it changes the slope of the line because these lines are not straight. We have a non-linear function. Now you know the difference between linear and non-linear functions and you can create a simple graph that shows how these numbers get charted. All I need to do is take a look at the bicycles, and the tires, change any of the values, and see whether you get a straight line or a line that slopes. I'll see you soon.

Intermediate Provider : Cliff Brozo 3 Lessons · 20m in Arabic, German, English, Spanish, French, Portuguese, Chinese

Course Description

In these lessons, you’ll jump into the world of statistics by learning about linear and nonlinear regression. We’ll go over how Excel uses linear and nonlinear functions to do all the math for you, and how you can use such functions to create linear trendlines to illustrate any correlations within a data set.

Using Excel’s built-in functions for linear regression, you will learn how to access the data analysis section to perform linear regression to learn more about the results. You will also learn about strong and weak relationships and how to plot points that fall on a regression line.

What You'll Learn

Distinguish between linear and nonlinear functions
Use Excel's built-in functions to perform the math for linear and nonlinear regression
Create linear trendlines to illustrate correlations within a data set
Access Excel's data analysis section to run linear regression
Run and interpret linear regression summaries
Identify strong and weak relationships and plot points that fall on a regression line

Key Takeaways

Excel uses linear and nonlinear functions to perform the regression math for you.
Linear trendlines can be created in Excel to illustrate correlations within a data set.
Excel's data analysis section can be used to perform linear regression and review the results.
The course covers strong and weak relationships and how to plot points that fall on a regression line.

Frequently Asked Questions

What will I learn in this course?

You will learn the difference between linear and nonlinear functions, how to create a linear trendline, and how to run linear regression summaries, using Excel's built-in functions to do the math.

Which topics does this course cover?

The course covers linear and nonlinear functions, linear trendlines, and linear regression, including accessing Excel's data analysis section and understanding strong and weak relationships.

What skills does this course help me develop?

It helps you develop skills in simple linear regression, regression analysis, linear and nonlinear regression, linear models, and ordinary least squares (regression analysis).

How are the lessons structured?

The course is organized into three lessons: Linear and Non-Linear Functions, Linear Trendlines, and Linear Regression.