Multiple linear regression without intercept

LINEST does not have a function to force the trend through the origin. This problem can be solved using solver. It works on the principle of minimizing the sum of the squares of the differences between observed and desired values.

In the attached spreadsheet, Columns A & B are the observed results.

Column C has the formula =$H$1*A2+$H$2 (Y = mx+b). Where H1 is the slope and H2 is the intercept.
Column D is the difference =B2-C2
Column E is the square of the difference =D2^2

Cell H3 contains the sum of the squares of the difference.

Solver is an add-in that comes with Excel, but it must be "activated" - do a web search on how to do that. Solver causes some overhead, so you may want to turn it off when you don't need it.

The set up for this problem is shown in the picture: Minimize Cell H3 (sum of the squares of the differences) by changing cells H1 (slope) and H2 (intercept) with the constraint that H2 is held at zero.

You can use solver and this method to find the coefficients of any function. I've used it with mx + b + c*sin(dt +e).

dflak's response has me wondering if I am misunderstanding the question. According to the help file for the LINEST() function (https://support.office.com/en-us/art...a-fa7abf772b6d ), the third, optional [const] argument allows you to specify whether b should be 0 or calculated normally, and that applies to multiple regression applications, too. I have frequently used =LINEST(known-y's,known-x's,FALSE) to force b in my regressions to be 0.

Perhaps I am not understanding what it means to do a linear regression without the intercept. If "without the intercept" is equivalent to "b=0", then specifying the [const] argument as FALSE should do what you are asking.

MrShorty, You are correct. LINEST does have a b=0 option. It's been a while since I've read the actual documentation and probably have it confused with something else.

Which cells did you select as you entered the LINEST() function? It sounds like you only selected one cell, so you will only get the one result. In order to get all the coefficients, you need to select a horizontal array of cells. Did you confirm with ctrl-shift-enter (like all good array formulas)? If you want to output the additional statistics, you need to enter TRUE for the 4th argument, and select a 2D array of cells before entering the function and confirm with ctrl-shift-enter.