Are there limits on Solver's capabilities

**kjwx109** · 03-15-2024, 04:04 AM

Hello,
I am new to Excel Solver and this forum. I am looking at a test case where I know 4 variables but I am trying to retrospectively calculate them to assess whether Solver will have general use for my application (fitting chromatographic relative response factors). Fitting 3 of the 4, and setting the fourth as the true value gives accurate results for the other three. Fitting all four, starting from an arbitrary start point, is minimising SSE but is producing results that don't tie in with reality. It is as if I have set the constraints on the variables too wide, so that Solver has found a more global minimum, even though these values don't make physical sense.
Thus I am wondering if there are limits on the number of variables that can be fitted and am looking for general good practice for setting constraints.
kjwx109

**AliGW** · 03-15-2024, 04:08 AM

Administrative Note:

Welcome to the forum.

Is your forum profile showing the Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

**MrShorty** · 03-15-2024, 11:26 AM

Thus I am wondering if there are limits on the number of variables that can be fitted

Solver has a programmed limit of at most 100 (maybe 200) "decision variables" (by changing cells). 4 decision variables is well within Solver's ability. Of course, Solver has no control over any instability in your specific mathematical models and algorithms introduced by having 4 or more decision variables. It will be up to you as the programmer/analyst to know your model well enough to understand how it responds to its 4 decision variables.

am looking for general good practice for setting constraints.

I can't think of any broad, generic advice for setting constraints. Each model/problem/algorithm is so unique and varied, that any guidelines for setting constraints will depend entirely on the model/problem/algorithm being solved. As currently programmed, you are telling solver that your decisions variables can vary between 0 and 7500. If that is a broader range than you believe is really possible, you can set those constraints to more realistic values.