Also, I discovered that LINEST only seems to work if the X and Y data are in columns (not rows), with no empty cells within the range, so be aware of that if you get a #VALUE error. I did notice that the three coefficients are very close but not quite identical to those derived by using the graphical trendline feature under the charts tab. The Input Y Range is the range of cells that contains the dependent variable. In the Data Analysis window, select Regression from the list and click OK. Then, in the Analysis group, select Data Analysis. It is not required to chart a linear regression line, but it makes creating statistics tables simpler. This plugin makes calculating a range of statistics very easy. Lastly, in the next cell over, again type the same formula but change the last number to a 3. Using the weight and calories spreadsheet as an example, you can perform a linear regression analysis in Excel as follows. The first step in running regression analysis in Excel is to double-check that the free Excel plugin Data Analysis ToolPak is installed. In an adjacent cell, type the same formula again but change the final 1 to a 2. of course you'd change these ranges appropriately to match your data). Using the formula: =LINEST(B2:B21, A2:A21^1.2)Įxcel's LINEST function can also calculate multiple regressions, with different exponents on x at the same time, e.g.: =LINEST(B2:B21,A2:A21^,TRUE,FALSE),1) (by the way, the B2:B21 and A2:A21 I used are just the same values the first poster who answered this used. Free download RegressIt is offered for free as a public service.

You can also use Excel to calculate a regression with a formula that uses an exponent for x different from 1, e.g.

Which returns a single value, the linear slope ( m) according to the formula: You need to use an undocumented trick with Excel's LINEST function: =LINEST(known_y's,, , )Ī regular linear regression is calculated (with your data) as: =LINEST(B2:B21,A2:A21)

Regression for excel mac