Excel for regression analysis: What a surprise!
I wouldn’t normally consider Excel for analysis, but there are four reasons I’ll be using it sometimes for the next class I’m teaching. First of all, we start out with some pretty basic statistics, I’m not even sure I’d call them statistics, and Excel is good for that kind of stuff. Second, Excel now has data analysis tools available for the Mac – years ago, that was not the case. Since my students may have Mac or Windows, I need something that works on both. Third, many of the assignments in the course I will be teaching use small data sets – and this is real life. If you are at a clinic, you don’t have 300,000,000 records.Four, the number of functions and ease of use of functions in Excel has increased over the years.
For example,
TRANSPOSE AN ARRAY IN EXCEL
Select all of the data you want and select COPY
Click on the cell where you want the data copied and select PASTE SPECIAL from the edit menu. Click the bottom right button next to TRANSPOSE and click OK. Voila. Data transposed.
PERFORMING A REGRESSION ANALYSIS
Once you have your data in columns (and if it isn’t, see TRANSPOSE above), you just need to
- Add the Analysis Pack. You only need to do this once and it should be available with Excel forever more. To do that, go to TOOLS and select EXCEL ADD-INS. Then click the box next to Analysis ToolPak and click OK.
- Now, go to TOOLS, select DATA ANALYSIS and then pick REGRESSION ANALYSIS
You just need to select the range for the Y variables, probably one column, select the range for the X variables, probably a column adjacent to it, and click OK. You may also select confidence limits, fit plots, residuals and more.
So, yeah, for simple analyses, Excel can be super-simple.
Believe it or not, this is what I do for fun. In my day job, I make video games that teach math and social studies.
You can check out the games we make here.
