Excel really isn't designed with this in mind and doesn't have the functions for this. I am a little concerned that your model may be fragile for the purposes of making a prediction. Is a road rated as a 10 actually 10 times better than one whose rating is a 1 and is one rated a 10 exactly 2 times better than one rated a 5? Your model also does not consider changes in weather or the comparability of building materials from twenty years ago to today.įinally, your dependent variable is probably ranked data. Although this loosely implies a quadratic-like model, the maximum value should always be for a new road and it is not under your specification. It seems like roads remain quite good for a long time, but once they begin deteriorating they begin to come apart faster and faster. It may be the case that no pre-built Excel model will be a good predictive fit. For example, I am assuming that roads cannot spontaneously improve themselves. 24.5).Ĭlick here to learn more about Real Statistics capabilities that support polynomial regression.My concern with your model is that you may be ignoring important mathematical properties and get bad predictions. 83.5%) and the standard error is lower (13.2 vs. That the quadratic model is a better fit for the data is apparent from the fact that the adjusted R-square value is higher (95.2% vs. The linear model is generated by using only columns I and K from Figure 1. We can also run the Regression data analysis tool on the original data to compare the above results with the linear model studied in Regression Analysis. Thus to predict the number of hours that a particular senior will use the Internet after 3 months, we plug 3 into the model (or use the TREND function) to get 20.8 hours of use. In comparison, all of the other types of trendlines that Excel produces match the data fairly well. Problem: Its clear to me just by looking at the graph that the exponential trendline does not match the data. Hours of Use = 21.92 – 24.55 * Month + 8.06 * Month 2 The black line is a regression trendline that has been auto-generated by Excel. (To display the quadratic trend line select Layout > Analysis|Trendline and then More Trendline Options… On the display box which appears choose Polynomial trendline of Order 2.)įigure 2 also shows that the regression quadratic that best fits the data is This is further confirmed by looking at the scatter diagram in Figure 1, which shows that the quadratic trend line is a better bit for the data than the linear trend line. The fact that the p-value for the MonSq variable is near 0 also confirms that the quadratic coefficient is significant. The Adjusted R Square value of 95% and p-value (Significance F) close to 0 shows that the model is a good fit for the data. Specifying the correct model is an iterative process where you fit a model, check the results, and possibly modify it. In the Data Analysis popup, choose Regression, and then follow the steps below. In Excel, click Data Analysis on the Data tab, as shown above.
#Data regression excel 2010 download#
We now run the Regression data analysis tool using the table on the right (quadratic model) in columns I, J and K as the input. Download the Excel file that contains the data for this example: MultipleRegression.
We next create the table on the right in Figure 1 from this data, adding a second independent variable (MonSq) which is equal to the square of the month. Determine whether a quadratic regression line is a good fit for the data.įigure 1 – Data for polynomial regression in Example 1 This requires the Data Analysis Add-in: see Excel 2007: Access and Activating the Data Analysis Add-in The data used are in carsdata. MULTIPLE REGRESSION USING THE DATA ANALYSIS ADD-IN. The main addition is the F-test for overall fit. A sample of 5 people is chosen at random and the number of hours of Internet use is recorded for 6 months, as shown in the table on the upper left side of Figure 1. There is little extra to know beyond regression with one explanatory variable. Įxample 1: A group of senior citizens who have never used the Internet before are given training.
Studied in Multiple Regression Analysis where. This is equivalent to the usual multiple regression model We look at a quadratic model, although it is straightforward to extend this to any higher-order polynomial. Click here to learn more about Real Statistics capabilities that support polynomial regression.
#Data regression excel 2010 how to#
On this webpage, we explore how to construct polynomial regression models using standard Excel capabilities. Sometimes data fits better with a polynomial curve. In Method of Least Squares for Multiple Regression, we review how to fit data to a straight line.