Solver? Maximum number of variables?

[TK2301]

Is there a maximum number of adjustable variables that Solver can work with
at any one time ... or do I have a memory limitation?

 

[Mike H]

Hi,

It's 200, have a look here

http://office.microsoft.com/en-us/excel/HP051992911033.aspx

Mike

 

[Martin Brown]

Hi,

It's 200, have a look here

http://office.microsoft.com/en-us/excel/HP051992911033.aspx

Mike

Although the people that programmed it for MickeySoft will sell you a
version that is a lot more capable - for a price.

http://www.solver.com/

BTW XL2007 chart polynomial fit for difficult ill conditioned data is
much worse than XL2003's version. The latter was programmed by someone
who understood what they were doing. I guess they must have moved on.

The "improved" XL2007 gets the same wrong answer as LINEST
(and certain other commonly used stats applications)

Regards,
Martin Brown

 

[Mike Middleton]

Martin Brown -

Please provide a data set for testing the polynomial fit and LINEST.

Also, please verify that you are using Excel 2003 SP3 and Excel 2007 SP2 for
your tests.

- Mike Middleton

Hi,

It's 200, have a look here

http://office.microsoft.com/en-us/excel/HP051992911033.aspx

Mike

Although the people that programmed it for MickeySoft will sell you a
version that is a lot more capable - for a price.

http://www.solver.com/

BTW XL2007 chart polynomial fit for difficult ill conditioned data is
much worse than XL2003's version. The latter was programmed by someone
who understood what they were doing. I guess they must have moved on.

The "improved" XL2007 gets the same wrong answer as LINEST
(and certain other commonly used stats applications)

Regards,
Martin Brown

 

[Martin Brown]

Martin Brown -

Please provide a data set for testing the polynomial fit and LINEST.

The following thread from 2000 has an example of fairly innocent looking
data that will cause old LINEST to misbehave. The chart polynomial fit
was much better and controlled the condition number of the matrix.

http://groups.google.co.uk/group/mi...+polynomial+fit+martin+brown#9870d960be2b208f
and
http://groups.google.co.uk/group/mi...+polynomial+fit+martin+brown#a52a00c8a72b21db

XL2007 SP2 has it seems fixed the most glaring numerical instability
error and it only took them 10 years.

Also, please verify that you are using Excel 2003 SP3 and Excel 2007 SP2 for
your tests.

Interesting. I last tested this on SP1 and it was still absolutely
hopeless. It shows that Mickeysoft do pay attention to reported faults
eventually. It has only taken a mere 10 years to get this glaring
numerical instability error in the XL LINEST fit routine fixed. Out of
the box XL2007 and SP1 they had "fixed" it to give the *wrong* answer in
both places and the previously excellent chart trendline polynomials
were degraded to give the same wrong answer as LINEST

It still allows users to overfit data with worse results. Chi-squared
for the best fit increases going from a 5th to 6th order polynomial, but
I expect it will take another 10 years before they sort that out.

This is regrettable as avoidable user errors involving overfitting of
too little data are rampant in industry and commerce.

Regards,
Martin Brown

- Mike Middleton




Although the people that programmed it for MickeySoft will sell you a
version that is a lot more capable - for a price.

http://www.solver.com/

BTW XL2007 chart polynomial fit for difficult ill conditioned data is
much worse than XL2003's version. The latter was programmed by someone
who understood what they were doing. I guess they must have moved on.

This has finally been fixed by XL2007 SP2. LINEST is no longer massively
inferior to the chart polynomial trendline fit and the right numerical
method has finally been used for both implementations!