This video is part 8 in a series of videos on graphing in excel 2010. This video covers
the use of trendline with excel scatter plots. Trendline is a tool to find a line that matches
the trend in the data. This can be to help identify what the trend is or extrapolate
how the trend will continue beyond the given data. For this example I will be using a graph
created in part 6 of this series. This graph covers the resident, non resident and fee
increases from1980 to the current year of 2011. Since there are multiple data sets I
will be working with multiple trend lines. I will start with the non resident students.
That is this red line at the top here. To go to trendline I need to go to layout, analysis
and trendline. I can use one of these drop down menu options but I prefer using the trendline
dialog box since it has more options. To get to that I am going to select more trendline
options. As I said I will be using non-resident. This is the trendline dialog box. First I
am going to set the line color to be red which is the same as the data information for the
non-resident students. Second I am going to start with a linear trendline.because you
want to get a trendline that has the lowest power that matches the data the best. That
way the extrapolation will be more accurate without having too many wobbles in between
your data points. I also want to display the equation and the "R" squared value on the
chart. This is extremely important because without these you may have a trendline but
you do not know how well it fits aside from what you can see by looking at it. Here I
have my equation and my "R" squared value for the trendline associated with the non-resident
students. As you can see the "R" squared value is 0.978. This is pretty close to 1. The closer
your "R" squared value is to 1 the more accurate the trendline is to your data. Even though
by looking at this we can see that the data moves in ways that the trendline doesn't we
still have a pretty accurate trendline. What I am going to do however is I am going to
try another trendline to see if it fits better. If I go back to layout, analysis, trendline
and this time I am going to do a logarithmic just to see. You can see the "R" squared value
is slightly less and the line is almost exactly the same as the trend line for the linear.
I am just going to get rid of that and try another one. This time let's try the exponential.
Here you can see that the trendline has an "R" squared value of 0.975 which is still
less than the linear so I am going to go ahead and get rid of this trendline as well. I can
do that by selecting the trendline itself and pressing delete. Now I am going to try
another one here and this time I am going to do a polynomial of order 2. This will allow the trendline
to have a little wave in it to see if it matches the data better. As you can see I forgot to
add the equation and "R" squared. I can just double click on the line to give myself the
option to add those in. Here I now have an "R" squared value of 0.995 which is much better
than the linear one. Since I have a better match I am going to delete the linear trendline
and keep this one. I am going to try one more and that is going to be a power 3. This one
matches it eve better with an "R" squared of 0.996. Even though that this shows it is
more accurate since I am using a higher power for just a slight bit more accuracy there
is a possibility that if I were to use this equation to extrapolate further I would end
up with information that would be inaccurate to the current trend. Since I think that 0.995
is close enough to 1 I am going to go ahead and get rid of this trendline. Now the trendline
I am using is going to be the polynomial of order 2. I am going to change some of this
information here like the color to red. I am just going to save it as "Poly #2 (Non-Resident)".
That way the trendline name gives a little bit more information. Now I need to create
the trendline for the resident students. I can go back to the analysis trendline, more
trendline option and select Resident or I can simply select the series and when I go
to more trendline options it will automatically go into the dialog box. Once again I am going
to try the linear trendline. This gives me an "R" squared value of 0.80 which is pretty
bad and I am not going to even bother keeping that one here. I am going to try again and
I am going to use exponential. This has an "R" squared of 0.95. That is much better than
the 0.8. If I double click on this instead of creating a whole new trendline I can just
go ahead and change it to try logarithmic which is 0.8 again. If I do polynomial of
order 2 I have an "R" squared of 0.94. Unfortunately since I just replaced the exponential that
I had before I have to go back and see what that one was again. Instead I am going to
leave that there and I am going to create another exponential. The exponential trendline
has a better fit than my polynomial of order 2. I am now going to change that to polynomial
of order 3 to see if I can improve the fit. It does just still not as well as the exponential.
I am going to just keep the exponential trendline but I am going to change it to be blue. That
way it will match the data. The lat one is the fees. I am going to try linear again for
this and have it display the equation and the "R" squared value. Now my "R" squared
value comes out to 0.888 and this is a good example of just looking at it and having excel
analyze the "R" squared because if you just look at this it looks like it is a pretty
good fit for the line but the "R" squared is telling me that it is not that great. Here
I am going to edit this and try exponential which give 0.9883. Since this is pretty good
the next one I make is going to be a new trendline. Logarithmic I am not even going to try at
this time. I am just going to go straight to a polynomial of order 2. This has an "R"
squared of 0.9804 which is not as good. If I go up to order 3 I have 0.987. It is still
not as good as the exponential. I am just going to keep that exponential and changes
its color to be green to match the data. Those are the trendlines for those three sets of
data. Now I am going to set it up on the spreadsheet so we can try to extrapolate how this is going
to change over the next 10-15 year. Here I have used the trendline equations to extrapolate
the data out an extra 10 years. It shows that for the resident students if the trend continues
the new tuition will be 73% higher than what it is now. For the non resident students it
will be 49% higher and for the fees in general it will be 99% higher or double what it is
currently.