one or more independent ones (X)<\/strong>.<\/p>\n\n\n\nExcel has a great built-in tool for linear regression, which is called \u201cRegression\u201d<\/strong>. In the example below, we will show how to do it.<\/p>\n\n\n\nPreparing Data Analysis<\/h2>\n\n\n\n Several steps need to be taken to conduct a linear regression analysis in Excel:<\/p>\n\n\n\n
\nPrepare the Data<\/strong><\/li>\n<\/ol>\n\n\n\nFirst thing first, we need to have our data. This means that we have a dependent variable (Y) and one or more independent variables (X) <\/strong>in our columns. Every row that we have should represent an individual data point.<\/p>\n\n\n\n\nEnable Data Analysis ToolPak (if not already enabled)<\/strong><\/li>\n<\/ol>\n\n\n\nTo make sure that you can do the proper Data Analysis<\/strong>, you need to enable Data Analysis ToolPak<\/strong>. To do this, you need to go to File >> Options >> Add-ins<\/strong>. There, in the Add-ins window<\/strong>, we will select Excel Add-ins and click Go:<\/strong><\/p>\n\n\n\n <\/figure>\n\n\n\nOnce we do, the Add-ins window<\/strong> will appear. There, we will click on the Analysis ToolPak box<\/strong>, and click OK:<\/strong><\/p>\n\n\n\n <\/figure>\n\n\n\n\nAccess Data Analysis ToolPak<\/strong><\/li>\n<\/ol>\n\n\n\nOnce we have the TookPak enabled<\/strong>, we should be able to see the Data Analysis option<\/strong> located in the Data tab on the Excel ribbon<\/strong>:<\/p>\n\n\n\n <\/figure>\n\n\n\n\nPerform Regression analysis and interpret the results<\/strong><\/li>\n<\/ol>\n\n\n\nFor the final part, you do the regression and then see what you got.<\/p>\n\n\n\n
Excel Data Analysis with Linear Regression in Example<\/h2>\n\n\n\n For our purposes, we will create the table with hours spent on learning and student\u2019s exam scores<\/strong>:<\/p>\n\n\n\n <\/figure>\n\n\n\nSince we all did steps 1 to 3, we will go to the Data tab, and then click on the Data Analysis<\/strong>. Once we do, we will have a new window on which we will choose Regression as an option<\/strong>:<\/p>\n\n\n\n <\/figure>\n\n\n\nWe will click OK<\/strong> and will be presented with a Regression window<\/strong>. There, we will click on the Input Y_Range<\/strong> and then choose range B2:B7 (exam scores)<\/strong> as our Y Range<\/strong>, and on Input X_Range<\/strong> choose range A2:A7 (study hours) as our X range<\/strong>.<\/p>\n\n\n\nWe will also click on the Labels checkbox as we have headers<\/strong>:<\/p>\n\n\n\n <\/figure>\n\n\n\nWhen we click OK, Excel will create another sheet with the regression analysis done<\/strong>:<\/p>\n\n\n\n <\/figure>\n\n\n\nWe will have information such as coefficients, standard errors, t-values, p-values, and the R-squared value<\/strong>.<\/p>\n\n\n\nIn our example, we wanted to see if there was any correlation between the hours that were put into work, and the scores that students achieved<\/strong>.<\/p>\n\n\n\nThe coefficient<\/strong> represents the change in the exam score for every additional hour that students put to work. The p-value<\/strong> is associated with the coefficient and will tell us if the relationship is statistically significant.<\/p>\n\n\n\nR-squared value<\/strong> can give us a good idea about the fit of the model and the data. A value that is close to 1 indicated a good fit, and a value that is close to 0 indicates a poor fit.<\/p>\n\n\n\nIn our example, we have a coefficient of 5<\/strong>, which means that, on average, for each additional study hour, the exam score increases by 5 points<\/strong>. Our p-value is smaller than 0.05<\/strong>, meaning that the relationship between the score that students can achieve, and study hours is significant<\/strong>.<\/p>\n\n\n\nThis example is fairly simple, and the correlation can be seen by looking at the original table. In real-world scenarios, datasets would be larger, and the models would be more complex. However, the steps that were described above can be applied to these as well.<\/p>\n","protected":false},"excerpt":{"rendered":"
There are a lot of things that Excel is an amazing tool to use for. One of these things is data analysis, and…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\n
Data Analysis with Linear Regression in Excel<\/title>\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\t \n\t \n\t \n