All right, you're going to learn all about trendlines in this screencast. Oftentimes, we want a mathematical equation for a set of data that is represented on a scatter plot. To remind yourselves of scatter plots, you can go into part one of the course in week four. Scatter plots are nice plots to visualize x, y data where you have paired data. This mathematical equation that we're trying to find can be simple, like a line or it can be quite complex, like polynomial equation, and I'm going to show you examples of both in this screencast. As an example, here we have the boiling point of water on the y-axis, on the x-axis, we have elevation. As you go up from sea level, the boiling point of water will actually decrease, and it's quite linear as shown here. We might want to have a mathematical equation that estimates the boiling point of water as a function of elevation. The data represented here, we've only collected a certain subset of data points, so we don't really have a complete picture. If we did have a mathematical equation, we could substitute in the elevation and we could determine the boiling point quite readily. And so obviously this is a line, so we might put together an equation of a line. The equation of a line is y = mx + b, y is the dependent variable here, so that would be the boiling point, x is the independent variable, that's the elevation in this example. And so what we want to do is, we want to be able to predict the boiling point as a function of the elevation, m represents the slope of this line and b represents the y-intercept. The y-intercept is where this line intersects the y-axis, and that means if we plug in 0 into the equation of the line, then the y value would just be b, which is the y-intercept. So in this example, the boiling point because that's the dependent variable is going to = m which is slope, times the elevation again, that's the x variable + b, which is our y-intercept. So the goal of what we're going to do next is to calculate m and b, and those are fitting parameters, that then we can put into our mathematical model. As an example, we might want to know what is the boiling point of water at 3,000 feet. So that's our x value 3,000 feet, if we plug that into our mathematical equation for this line, then what we'll be able to predict is the boiling point of water which here, it looks like about 206.5 degrees Fahrenheit. But we're going to create this mathematical equation that will enable us to predict boiling point as a function of elevation. An easy way to do this is in Excel. I've got the data here, this is in a file called boiling point of water. I've got it in feet, and meters, degrees Fahrenheit, and degrees Celsius. I am in the United States and we are a little outdated, we still use elevation in feet, and boiling point in degrees Fahrenheit. But if you want, you can plug it in with meters and degrees Celsius. A very easy way to create a line once you have a plot, is to right-click on the data series, and we can go down here to Add Trendline. When you do that, it brings up these options Format Trendline over on the right, and we can select from a couple of different types of trendlines. Now, this one clearly follows a line, so I'm going to click Linear. I'm going to scroll down, and I can Display the Equation on the chart, I can Display the R-squared value. The R-squared value is something known as the correlation coefficient. The closer R-squared is to 1, the better the fit is. Here, R-squared is 0.9999, which is very close to 1, which shows that a line is a very good fit for the data. If you want, you could experiment around with these different types of models here, these different types of trendlines. In the next example, I'll show you how we can apply a polynomial fit. But right now, we're just going to go with our line here. We've got the equation for that line, and this is what we're trying to find. This means that the slope is -0.0019, and the intercept is 212.13. Now, what we're trying to do again, is we're trying to predict the boiling point as a function of the elevation, and we've got that line here. So what I can do, is I can just easily type in this equation here, and I'm just going to manually transfer it over, 019 times the x value, the x value is our elevation, + 212.13. I'm just getting that from our equation, and I can press Enter, and it's telling us that the boiling point is about 206.4, based upon this equation that we got using the trendline tool. Let me show you another way that we can do this. So I'm going to go ahead and delete that. There is a SLOPE function that's built into Excel, the first argument is the known y's. So my y's, my y values, that's the dependent variable, are my boiling points in degrees Fahrenheit. I press comma, my x values are elevation in feet, and I can do that and press Enter, and it tells us the slope. This slope is a little bit better, it has more precision, more significant figures on there than the 0.0019 that the trendline tool gives you. There's also an intercept function, we can do known y's, known x's, and I can press Enter, and that's a little bit more precision there on the intercept than what we get here on the trendline tool. But regardless, they're about the same, and then what I'm going to do is I'm going to name slope up here, we name that m. I'm going to name the intercept b, and in our boiling point calculation, I can just put in = m times, and I'll reference my elevation, + b and I can press Enter, and it's predicting then a boiling point at 3,000 feet of 206.5. Now, the nice thing about this is I can always go back, and I can put in something else like 4,500. That means if I'm at 4,500 feet, the boiling point is about 203.7. Let's go ahead and reduce decimal there a little bit, that's kind of a lot of significant figures. So now this is presented a little more nicely. And let's just test a couple of other examples, then that'll tell us the boiling point in degrees Fahrenheit. Let me just show you a quick alternative to calculating the slope and the intercept here, and using those values of slope and intercept in this equation. If you have x, y data and you want to somehow be able to estimate the y value based upon a new x value, and that x value is not necessarily an x value of one of these experimental data points, you can use something known as the FORECAST function. So I'm going to go ahead and delete the formula in D16, and I'm just going to use the FORECAST function. There's a couple of different ones, but we're going to use the linear, so you can type in FORECAST.LINEAR. The first argument is the x value that you want to forecast or predict, and I'm going to click on my elevation there of 5,600, and then we have our known y's, that's the boiling point in degrees Fahrenheit, and our known x values is elevation in feet. And then when I press Enter, that's going to do the exact same thing as calculating the slope and intercept, and plugging that into cell D16 in a little bit different approach. So it's a little bit easier to just use the FORECAST.LINEAR function if you've got data, and you kind of predict a new y value based upon an x value.
