options to do multiple linear regression with constraints (coefficients >= 0 and sum=1)

There are probably multiple possible approaches. My first inclination, with those particular constraints is to use Solver. Being new to Excel, are you familiar with Solver? Here's how I would approach this:

1) Enter your best first guess values for the coefficients of the regression equation y=sum(Ai*xi)+B. You might try 0.1 for each, or use the LINEST() function to get these first estimates, or some other reasonable first guess. I put them in P1:Y1, with B in Z1
2) Add a column to compute yequation from the coefficients and the x's. This could be involve a SUMPRODUCT() function or whatever. Be sure to pay attention to relative and absolute references, to make this easy to copy. I used =$Z$1+sumproduct(B2:K2,$P$1:$Y$1) in M2 and copied down. (Help files for functions: https://support.office.com/en-us/art...6-c6d90033e188 )
3) Compute your desired objective function. For least squares, this might be =SUMXMY2(M2:M229,A2:A229) or whatever you deem the most appropriate obective.
4) In order to deal with the "sum(Ai)=1" constraint, I added a cell =SUM(P1:Y1) to compute the sum of the coefficients.
5) Call Solver and tell it to
5a) Set Target Cell: cell containing the SUMXMY2() or other objective function.
5b) To a minimum (or maximum or value as appropriate to the chosen OF).
5c) By changing: P1:Z1
5d) Subject to constraints that P1:Y1>=0 and cell with SUM(P1:Y1) function=1.

In trying a few different starting values, I noted that Solver converged on different solutions, depending on what I chose for the starting value. These kind of problems can frequently have multiple possible solutions, and you may need to further explore the solution space to see how to identify the absolute best fit according to those constraints.

Hey Mr.Shorty. While you were posting I was doing it. . I think we cover the same ground, but I suppose two views are better than one, so here goes:

Digging this one up from when Professor Newton taught me math.

The least squares fit method depends upon minimizing the sum of the squares of the difference between a computed value and its actual value.

The Add-In solver can do this. I’ve used it on complex equations like A*SIN(B*t+C) + D*Ln(t) + E. Try solving that algebraically. This is an equation for growth adjusted for seasonality.

First, go to options and enable the solver Add-in. I suggest that you take it out when not using it as it seems to slow down Excel from launching and may also crash it at times. At least in my at work copy of Excel 2013.

The key to using solver is to set up the problem properly. I did the setup on Sheet 2. In Cells I1 and I2 I set up the coefficients M (slope) and B (intercept) and populated them with an initial guess of 0.5 each.

I set cell I3 equal to the sum of I1 and I2.

In column C I set up a formula to compute Y using these coefficients =$I$1*B2+$I$2.

Column D contains the difference between observed and computed: =A2-C2.

Column E contains the square of the difference: =D2^2

Cell J6 contains the sum of the square of the differences.

Now we can set up solver (see picture).

What we want to do is minimize the value of Cell J6 by changing the values in Cells I1:I2. With the constraints that I1 and I2 are non-negative and that the value in Cell I3 is 1.

Click on solve and solver will go to work.

I did this for the first set, and it came out with a slope of 1 and an intercept of zero. It does seem like a trivial solution.

I copied and pasted some of the other X ranges in and they also came out 1 and zero. There is a difference in the slope in the 9th decimal place.

Yes, it's possible to do this via VBA. I'll have to record a macro for running Solver.

I have some questions that might be germane: will you always have 229 rows of data (not a big deal) and will you always have 10 X-values (again, not a big deal). It will tell me how flexible I have to make the code.

I'll report the answers in a table Xn | M | B. So you'll have a list from 1 to 10 (or whatever) with the associated M and B.

I have some other projects in front of this one, so I may not get to it until tomorrow.

A few examples, while dflak prepares his example:

Some basics about using Solver in VBA: http://peltiertech.com/Excel/SolverVBA.html
Here's one with Solver in a loop: http://www.excelforum.com/showthread.php?t=1166949
Here's on from stackoverflow showing the basic structure of a change event procedure: http://stackoverflow.com/questions/1...e-sheet-change Once you have the basic Solver code, you can place it inside of a change event procedure.
I would be more inclined to use a calculate event procedure. This one shows Goal Seek called from a calculate event, replacing the goal seek stuff with Solver stuff should be straightforward: http://excel.kingofmath.com/?p=267

I had to look up a couple of things and that's why I participate in the forum; it forces me to learn stuff.

Here it is.

1. With what code in macro I can just STOP appearance of 'Solver Results' on the window screen? However in doing so, I still want the results to get pasted by solver in the intended cells of worksheet. If it is Not possible to stop the appearance, with what code in macro, I can achieve automatic click on 'ok' button of 'Solver Results' window? Please help.

As explained in the first link (about 5th "paragraph" down below the first sample recorded code):

Originally Posted by Jon Peltier

SolverSolve has an optional UserFinish argument; if UserFinish is False or omitted, the second dialog shown above will ask the user whether to save the optimization, but if UserFinish is True, Solver will end without the dialog.

2. Is there any way I can increase no. of iterations solver considers (by default it is 5) before throwing out 'Solver Results' window showing convergence achieved mesg?

I'm not sure I understand your question. The message you posted suggests trying a smaller convergence setting. What is your current convergence setting, and have you tried a smaller setting? What is the deeper concern here?

It is always a good idea to post your own thread. Copy and paste a link back to this thread if you think it will help. Also include a sample workbook with representative data and if it is appropriate, mock up a sheet showing what you want the results to look like.