Let's make a few scatterplots with Excel and what we're also going to do is. We're going to put some trend lines in there and trend lines. Sometimes help us visualize the relationship that might be in a scatterplot. Now we make scatter plots using two How To Create A Scatter Plot In Excel quantitative variables one on the x-axis and one on the y-axis. Let's just pick the first two variables here that are quantitative in this data set. That has a list of 93 different cars and the data here is really old from the Year1993. But it's data from Consumer Reports that has the minimum price. Horsepower's average price paid highway miles per gallon and some other things. So let’s look at a few of these scatterplots to see is there a relationship between some of these variables. So let’s look at the minimum price and the horsepower just because these are right next to each other. What Excel will normally do is put the variable that ‘son the left-hand side on the x-axis of the scatterplot.
The variable on the right-hand side on the y-axis and is the
minimum price in thousands of dollars. So let’s select these two variables and
then let’s go to insert and over here on the charts area. There's a little one
that has dots and it says insert How To
Create A Scatter Plot In Excel Scatter Chart. Now let's just pick the basic
kind of chart here and there's our scatterplot. Now what I wish Excel would do
is label the x and the y axis automatically. Now we can kind of tell and also I
told you that the general rule. That Excel uses is the variable on the left
goes on the x-axis and that's what it’s done variable on the right is on the
y-axis. But still, when you make a scatterplot you want to make
sure to label
these. So let's right-click is one way to do it another way to do it is that.
Sometimes is a little quicker is going to click on the design tab here and go
to add chart element and X's titles. We want to add a primary vertical and a
primary horizontal axis.
So the vertical axis is the y axis let’s click here and
delete the words axis title and tell people. What this is horsepower and we
probably want to move this graph over a little bit. So that we can actually
read the title their horsepower and then let's do the same thing. Let's add an
x-axis here, so add chart element axis title primary horizontal. Click here to
delete that How To Create A Scatter Plot
In Excel text and tell people that this is the minimum price in thousands.
So now let’s look and see what we have and we probably want to give a better
title here telling people. That this is a scatterplot and you know maybe a more
important title than that. But let’s just see what we have here it looks
generally like we have an upward sloping relationship where. Which we would
call an appositive relationship or a direct relationship. Where more expensive
cars, in general, seem to have higher horsepower.
Now if you ever have a scatterplot where it's a little hard
to judge or it's hard to see. What that relationship looks like then what we
can do is add a trend line. Some people will also call this a regression line.
So if you want to add adrenaline right-click on one of the dots in your
scatterplot and go to add a trend line. You see a lot of options here on the How To Create A Scatter Plot In Excel
right do you want to add a curved. You want to add a straight line in general
you want to stick with a straight line unless. You actually see a curved
relationship and then you can try that. But let's just add a straight-line
linear trend line here and that looks fine to me. So it added it as a dotted
line we can change the format of that. If we want but what this does is roughly
give us a line of best fit that. If we were to use a straight line to explain
this relationship between price and horsepower. That's what that line would
look like and what we see here is that the relationship is a pretty good one.
But the relationship looks like it's a much closer relationship meaning the
points are much more tightly grouped.
Where we can see that as the price goes up the horsepower
goes up at the bottom. But as we get to higher and higher horsepower vehicles
this relationship becomes less defined less clear. There’s a lot more spread
above and below the line let's look at
How To Create A Scatter Plot In Excel another example here to see. What
happens now let's this is an appositive relationship. Let's see if we can find
a couple of variables that have a negative relationship and let me cut this.
I'm going to move it to a separate sheet. So let's go back to the data
something that probably has a negative relationship would be perhaps horsepower
and miles per gallon. As you get a bigger engine the miles per gallon you get
probably goes down. We get out we could look at horsepower or we could look at
engine size and probably get the same relationship. Let's use horsepower so I'm
going to select the horsepower variable and now I'm on a Windows computer.
If I want to select another variable that's not adjacent
select one variable. Then let's hold down the control key and then select the
second variable and then release the control key. That way we can select two
variables that are not right next to each other. We do the same thing How To Create A Scatter Plot In Excel
in sir go to the scatterplot and again we probably want to label these axes. But
for now, we can kind of see what's going on this is horsepower on the x-axis
and this is miles per gallon on the y-axis and in this case. We see a negative
relationship in general as the horsepower gets higher. We see that these
vehicles get lower miles per gallon now this one kind of looks like a curve
though. Let's insert a trend line and let’s start with a straight line and just
see what that looks like. You see this line there's a lot of points that are
way above the line here. But not too many below it on the left most of the
points are above the trend line oral the points are above the trend line on the
right.
So that suggests that maybe curves relationship might be
something we want to look into here. So you can say right-click add a trend
line. just pick one of these other curve shapes exponential logarithmic power
etc. we can see. If we can get one and you see that it's inserting it as we go.
So that we can get an idea of what it will look like a polynomial might do How To Create A Scatter Plot In Excel job a power might do the job. Maybe we'll go with power let's delete the
straight-line there. So that we don't get confused between the two and we can
see that. That curve might give us an idea of what that relationship looks like
just a little bit better. Then the straight line dip now let me do one more
scatterplot and I’m going to do try to do one. Where we’re not going to see a
very clear relationship when we make it. So let me move this just in case we
want to look back at it at a future date or a future time. Let's make a
scatterplot between say the minimum price and then again. I'm going to hold
down the control here and let's use the turn radius.
Maybe those have a relationship and maybe they don't let's
see insert scatter plot and boy it sure kind of sure hard to see. If anything's
going on there now although when people when professionals make graphs.
Generally, we do have a preference that the origin is in the graph unless.
There's a really good reason not to sometimes that can distort the way How To Create A Scatter Plot In Excel
the graph looks here. Since most of this data the so here the price is on the
x-axis the U-turn radius is on the y axis. There's very little going on except
for between 30 and 50. Maybe we do want to zoom in on the y-axis and get rid of
this empty space. Let’s just see if that changes our perception of what's going
on here. So I’m right-clicking I went to the format axis let’s change the
minimum to 30 and just see.
If that reveals any kind of pattern that we weren't seeing.
Before we do see that others are a large cluster over here that seems to have a
positive relationship. But then we have all these other points over here on the
right that’s kind of lead us to How-To
Create A Scatter Plot In Excel believes. Maybe there isn't so much of a
positive relationship anymore again. If we want to get an idea of what Excel
thinks by doing some calculations. We can insert a trend line and the trend
line seems to tell us. That the majority of the data is suggesting an appositive
relationship. But it's very it’s not what I would call a very clear or very
strong relationship here. Between the price and the U-turn, in general, we
might expect more expense of cars to maybe be a little larger. That larger car
might require a larger distance to make a U-turn. But it’s not a very
clear result here so here we see three different graphs. That we’ve made using
scatter plots that give us sort of three somewhat different results. A fairly
clear positive relationship but a somewhat curved negative relationship. Then a
very unclear kind of muddy murky positive relationship between price and
U-turn. So, I hope you've learned something and found this a little bit
interesting about how to make scatterplots.
0 Comments