multiple regression solver

[koylita]

Hi,
I am trying to get the coefficients of a regression using Solver add in, but I get one in zero while them are all different to zero when I perform the data analysis add in. Solver gives the right results when the regression has just one explicative variable, but not in this case.
I set the objective in maximizing the r2 adjusted.
Please see attached.

Thanks

[MrShorty]

I cannot read the newer file format, but I'll make a suggestion anyway:

I set the objective in maximizing the r2 adjusted.

Personally, I don't use "maximize r2" as my objective function when I do a regression. If you are using Excel's built in RSQ() function, it is probably calculating r2 incorrectly for anything but a y=mx+b model.

1st suggestion - make sure your spreadsheet is calculating r2 correctly
2nd suggestion - try a different objective function. The usual O.F. for a least squares analysis is to minimize SUMXMY2(y_measured,y_calculated).
3rd suggestion - If you are trying to regress a "linear" or "linearizable" function using a least squares objective function, you might look at using the LINEST() function.

[koylita]

ok.. please see attached the file in old format.


the equation is y=a+bx1+cx2 (2 independent variables)

thanks

[MrShorty]

I don't see the problem. I can run Solver using different starting points, and it converges on the same parameters, which are the same parameters that the built in regression tool apparently came up with.

[koylita]

Are you sure???
The correct coefficients are Coeficientes: 306.5261933, -24.97508952, 74.13095749
while with solver i get 147.6992232, 0, 72.30858412

Do you get the first ones with solver????

[MrShorty]

Yes, I do. When I run Solver without any modification to the Solver model or spreadsheet, it converges on 306.5..., -24.97..., and 74.1...

If I add a constraint to the Solver model to force B=0, then I can get 147.7, 0, and 72.3.

It would appear to me that somewhere in your Solver model or spreadsheet, you are forcing B to equal 0. This condition does not appear in the "old" version spreadsheet you posted, so I'm not sure where it is coming from.