Selected annual average carbon dioxide concentrations since 1959 (see Table 1 for data). Data from NOAA. We’ll start with a data set that we want to match. The is a convenient dataset. ![]() It’s also an important data set to model because matching a function to the data will allow us to predict the change in CO 2 over the next 100 years, and those predictions are the ones the uses in their estimates of the impact of global climate change around the world. Billions, if not trillions of dollars depend on those predictions (and how governments decide to respond). For ease of demonstration, I’ve picked a few of the annual average CO 2 concentration measurements at random to work with: Table 1: CO 2 Data Year ( x) CO 2 concentration (ppm) ( y) 1959 3 3 3 3 3 3 375.77 Now, looking at the data, we can see there is some sort of trend. Our first decision is about how to model it. Correlation and Regression with just Excel. This video shows you how to get the correlation coe cient, scatterplot, regression line, and regression equation. These tasks do not require the Analysis ToolPak or StatPlus:mac LE. Everything can be done easily with the out-of-the-package copy of Excel. Excel produces the following Summary Output (rounded to 3 decimal places). R Square equals 0.962, which is a very good fit. 96% of the variation in Quantity Sold is explained by the independent variables Price and Advertising. Free download music for mac. The closer to 1, the better the regression line (read on) fits the data. Straight Line Model The simplest approach, and the one we’ll try first is to fit a straight line through the data. (The final spreadsheet is ). ![]() The equation of a straight line is: [math] y = mx + b [/math] In this case, as we can see from the graph (Figure 1), the y axis is CO 2 concentration, and the x axis is the year. M and b are our two unknown constants that set the slope of the line (m) and move it up and down (b). If you need to get a better feel for what this means, try changing the slope and intercept in the (I used this in class to demonstrate). To match our straight line model to the data we need to determine the values of m and b that give the best fit. The formula ('=C$3*$A8+C$4') for our straight line model uses the year (column A) and the m and b coefficients (cells C3 and C4 respectively). You'll note the $ signs in the C3 and C4 references in the formula (i.e. $C$3 and $C$4); these tell Excel to always refer to these specific cells when the formula is copied and pasted down the entire column. So we create a spreadsheet with the data and in the adjacent column set up the straight line function by setting two cells to the values of the constants ( m and b) and using those values to calculate the modeled CO 2 concentration. The initial values for m and b (1 and -1600 respectively) don't match the data very well. You’ll notice that I have values of m = 1 and b = -1600. These are just my initial estimates of these values. The initial values are not crucial, as you’ll see, but are just there for me to check that my formula is in right. Once I have the correct formulas in, I can play around with these values until my line matches the data. However, this is where Solver comes in to save a lot of time.
0 Comments
Leave a Reply. |