LINEST - Non-Zero Y-Interception

I'm wondering if it is possible to have a fixed constant (y-interception) in LINEST ()?

If it does, where should I cast that magic spell in the function?

Solved: See Post #2 by MrShorty

It is not a built in option in the LINEST() function. You need to reformulate your regression equation, then feed that reformulated equation into LINEST(). It looks something like:

1) normal regression equation for a straight line: y=m*x+b
2) subtract b from both sides: y-b=m*x
3) If needed to see how this works, substitute u=y-b: u=m*x

It should be obvious that u is a straight line function through the origin. By inputting your desired intercept as b into the above equation, you can then create a regression that can go through whatever y intercept you specify.

In Excel, since I am not like everyone else on these forums, I like to set this up with a helper column:

Please Login or Register  to view this content.

Then feed column C as the known_y's parameter and column A as the known_x's parameter of the LINEST() function, being sure to make the constant function FALSE.

That is correct. And there may be other variations, too, for other conditions.