manually calculating a linear regression

[Matthew]

I don't currently have access to the predictive tool suite.

So I'm trying to fit a linear regression to a data set using the formula tool, but i'm doing something wrong, is anyone familiar with the math behind linear regressions?

here is a plot of the input data

Here is a plot of the output.. the scale is waaayyy off, but i don't understand why

[Matthew · Accepted solution]

i found the solution.. i also may have rewritten the slope and intercept formulas.. but ultimately the problem was with the way i was numbering the rows.. as soon as there were multiple groups, the RecordID tool wouldnt do the job anymore, so i switched to the Tile tool

so i now have a working linear regression tool 

[Lawrence_W]

Hello! I was trying to retrace these steps and was curious what the purpose of calculating X2 and XY (first Formula tool)? Thanks! 

Also, any luck with multiple regressions? I have a ton of forecasting I'd like to do with time series data. 

[Matthew]

@Lawrence_W 

That's a great call-out regarding X2 and XY, it doesn't look like they're used at all.. Those are probably leftovers from other attempts at making the regression work.

Regarding linear regressions with multiple X variables, i moved away from trying to manually calculate it. Instead i started using the python tool. Attached is an example workflow that might help you get started.

 

[Matthew]

@Lawrence_W i just noticed that you asked about time series data.. That regression macro might be a good demonstration, but it's only a linrear regression.. Time series prediction is a different beast

[Lawrence_W]

The Original post was very helpful! I actually came to the same results as regression in Excel!