What is the alternative of 'LINEST' excel function in Alteryx?

[mayankagruce]

I need the alternate option to calculate data what the LINEST function does, i have y values and only want to use them

[jdunkerley79]

To perform a linear regression and fit results you can use the predictive tools:

- Linear Regression will create the model

- Score will fit other values to the model

Alternatively, I have a blog post on creating a Linear Regression macro which does it from first principles.

If you want it as a formula function that is a harder ask!

[mayankagruce]

Thanks for detailed reply. for my problem, i don't have both x and y values,

i have four values 

Total	5 Year	2 Year	1 Year
0.1425	0.2117	0.2417	0.4833
0.3333	0.4283	1.0000	1.0000

and in excel, in another column there is a formula =LINEST("these four columns as known y's") and it gives me result like below:

Total	5 Year	2 Year	1 Year	LINEST result
0.1425	0.2117	0.2417	0.4833	0.105
0.3333	0.4283	1.0000	1.0000	0.257

and then i use the result column in another calculation with  which i am familiar.

i don't know much too much statistics, so if you can propose some simple solution based on above problem, it would be great.

[jdunkerley79 · Accepted solution]

Excel uses 1,2,3,4 for X when not given one.

I threw together a macro which will compute the Slope for this. It takes whatever column set it is given and adds an additional column with the LinEst value (the slope for the row points).

As you had multiple rows I based this off doing sums and some stats.

Hopefully you can use this to get started.

[MichaelLaRose · Accepted solution]

Hi @mayankagruce 

You should use @jdunkerley79 's response with the following change.

LINEST assumes that you data has Y values, and if you do not provide an X value it assumes that the values are 1,2,3, etc. https://support.office.com/en-us/article/linest-function-84d7d0d9-6e50-4101-977a-fa7abf772b6d

So you should just need to add a second column with a row ID to get the same results as the LINEST function.

Best,

Michael

[mayankagruce]

Thanks a lot ! you just saved me from getting stuck at this point by helping here. I got the idea from your macro on how to create same result as LINEST. I just finished my project after that..again thanks a lot.