Solver GRG doesn’t take 10 variables

[Zbysek]

Hi, I am working on school project trying to construct equation which describes tabulated data.
So far I’ve gotten to final form of the equation and am applying nonlinear GRG to obtain better coefficients for the equation.
The problem is that Excel does not accept full set of variables that appear in the equation (10 variables a2->d) for the regression process.
I have got to feed it manually "per partes" until I get to acceptable solution. So far got to SSE of approx. 266, but with correctly working solver it would probably got better.
Any idea what is done wrong? Thank you, Excel file is attached, hope it is descriptive enough

[Alf]

Just to test I ran you model using the "Evolutionary" solver engine. No problem getting Solver to run but result changed from 266,145409 to 266,1448396 so hardly any improvement.

I also tested the Non-linear version of OpenSolver (freebie from Univercity of Aukland NZ) it's their OpenSolver 2.9.0 AdvancedWin with a bit better result

OpenSolver_Coin.jpg

Still I don't know why the GRG Nonlinear can't handle this problem.

Alf

[Zbysek]

Thank you for your help! I noticed that number format for a2 was set to different one, but no difference, still the solver misbehaves. I tried to downgrade one of the coefficients and run the GRG again but it still doesn’t find find better solution when given all 10 coeffs to the solver. OpenSolver seems to be no-go for me as I’ve found it is not macOS friendly.
Seems that using Evolutionary could do the job. Would you be so kind and send me how to configure the Evolutionary? It is giving me erorreous result with this config. Attachment 636443

[Alf]

Have problem can't see your attachment. See link and Post #13 and #14

https://www.excelforum.com/suggestio...ttachment.html

Running "Evolutionary" engine I used your uploaded file with your values. As starting point often will determine if solver finds a solution or not I've not tested clearing range N18:N27 and run "Evolutionary" solver and frankly I don't think solver could find a solution as the range between the numbers are to big i.e. 48,17 to 2,00E-08.

You could try to contact the makers of solver and see if their help desk can offer assistance.

https://www.solver.com/

and there is also a possibility to have a free model as a student
https://www.solver.com/welcome-students

Running OpenSolver with a macro usually works very well. Record you macro using the Excel solver. Then set a reference (after installing it) to both OpenSolver and Solver (Tools->References) and tick boxes for OpenSolver and Solver.

Then select the OpenSolver engine you wish to run.

The macro for running OpenSolver on your file:

Option Explicit

Sub Macro1()

 SolverOk SetCell:="$N$29", MaxMinVal:=2, ByChange:="$N$18:$N$27"
 
 Application.Run "OpenSolver.xlam!RunOpenSolver"
 
End Sub

Again I don't know if OpenSolver will find a solution if you clear range N18:N27. You probably need to set a constraint on range of values N18:N27 can have, then there is a chance to find a solution.

Alf

[Alf]

Finally I'll upload your file with the Evolutionary setting. Why this did not work for you I would assume you cleared the range N18:N27.

I think one need a good starting point if solver shall have a chance to find a solution. As I ran the model with your values there was a starting point for solver that the
Evolutionary engine managed to improve slightly.

In the case of the OpenSolver it sees to make a better improvement but both models need a "good" starting point for values N18:N27.

Alf

[Alf]

having done a bit more testing and as you say the excel GRG nonlinear engine will work happily until you feed 10 variables, nine is ok but the 10 is a no-no.

Checking using the OpenSolver Advanced model and the COIN-OR Couenne (Non-linear, non-convex solver) engine a solution was found after clearing the range N18:N27 and running this solver engine for about 460 seconds.

So the good news is this solver engine will take 10 variables.

The bad: Takes time to find a result and one probably have to run at least a second time to ensure this is the best solution.

First result with this solver engine was 277,???? so I did a rerun with the found values from first run. The second run took a little less time with the result 265,8735649. So I did a third run but no change in the value of the target cell i.e. still 265,8735649

Alf

[MrShorty]

There's a lot going on in this problem -- and I don't think all of it is simple programming issues. Yes, there are questions about programming (like which of Solver's optimization algorithms to use), but I see other statistical questions and stability questions that could probably be explored, too.

Sometimes, I have had trouble with Solver dealing with very small (or very large) numbers in the decision variables (by changing cells). For example, a2 is on the order of 1E-8. In those cases, I adjust the spreadsheet so that Solver can work with "normal" sized numbers by setting up a simple multiplication. For example, I might put 1E-9 into J18 and 20 into I18. N18 then becomes =I18*J18. (repeat the process for each decision variable in rows 18:27). The I tell Solver to use I18:I27 as the by changing cells instead of N18:N27. Sometimes, that helps Solver to better find solutions. When I make this change, the I find that Solver very quickly and easily finds solutions that seem statistically equivalent to yours in one go.

Sometimes, the problem is with the objective function (set target cell). The behavior of the objective function is such that the algorithms get caught in a local minimum or the OF does not change very rapidly in the region near the optimum. Occasionally, I decide that I need a better OF (something other than a simple sum of squared error) in order to improve the algorithm. I do not understand your problem well enough to recommend something, but you might look at other common objectives and see if they provide better behavior near the optimum.

OR, I decide that Solver's result is "good enough" (by whatever measure I am using to measure goodness of fit) and stop trying to find the absolute minimum in the OF. Sometimes we get it in our heads that there is only one, unique solution to a problem. For these kind of regression problems (especially when lots of parameters are involved), it is often more correct to say that there is a range of acceptable solutions, and the challenge is deciding when we are close enough to the solution to stop looking.

When I want to judge "goodness" of fit, I tend to prefer to also see the absolute and relative deviations for each point. If I calculate relative deviation for each point (=SQRT(Q18)/P18 copied down and across), I see that, with your parameters, the worst point is in error by about 1%. Do you expect dramatically better than that?

Sometimes, one of the best things we can do is identify any parameters that contribute very little to the solution and remove them from the problem. For this, I went through each of your current values for the a's, b's, c's, and d and changed them one at a time by about 10% and see how much they impact the OF/SSE. Some parameters, like d, caused large changes in the OF. Others, like a1, had almost no impact on the OF. By identifying which parameters are more and less important to the regression, you may be able to remove some parameters from the problem, which can improve the numerical stability of the problem. I know that there are advanced statistics and tests that can measure "how strongly a parameter effects the regression" and "how to decide which parameters to discard", but those are well above my pay grade and (I expect) beyond the statistics any of us on this Excel board are familiar with. With my simple minded test, I would be inclined to remove a1 and a0 from the regression.

All of that said, some of what you are up against may be related to Solver's unsuitability to the problem. However, I expect that more of the problem is related to the inherent instability of working with a simply sum of squared errors regression with 10 parameters. If you think that the current set up is not getting a good enough regression, you will make the most progress by seeking a better objective function and/or reducing the number of parameters. There might also be value in checking with more dedicated statistics packages and see if there are better utilities/algorithms for non-linear regressions with large numbers of parameters (I wonder if R would have something built in?).

You seem to really want to do this as one single regression, but sometimes I find that it can work quite well to decompose a regression problem into steps. Your current regression equation looks like (edit to correct)TOD=(At^2+Bt+C)*exp(dp) where A, B, and C are quadratic functions of m. I could see some potential value (especially in terms of numerical stability) in separately regressing A, B, and C (at a given value for d) at your three values for m (this could be done using LINEST() if you set up the input matrix correctly). With the resulting A(m), B(m), C(m) values, you could then separately regress the ai's, bi's, ci's (again, you can use LINEST() for these). With this kind of setup, the only non-linear parameter is d. Once you have the LINEST() functions all set up and working, you can then use Solver to optimize your OF/SSE by changing d only. I have had good success with this kind of set up in the past, and I would be tempted to try it on this problem.

That's a lot of talk without giving any real recommendations. Hopefully some of those comments will be useful to you.

[MrShorty]

Bonus material (mathophobes beware -- this could get a little bit scary), because it is an interesting problem and it's a quiet weekend with nothing else to do. An equation with 10 parameters (and lots of embedded polynomials) feels like it is trying too hard without really knowing what it is doing. Dislcaimer -- I am not a pilot, nor an aeronautical engineer, nor any similarly qualified person who really knows anything. This is just my attempt to understand the problem.

I have enough experience with gases to know that many properties of gases are best expressed in terms of volume/density. My first step to better understanding the problem is to compute air density at your different conditions -- hoping that TOD will make more/simpler sense as a function of volume/density. The ideal gas law should describe air at typical temperatures and pressures quite well (https://en.wikipedia.org/wiki/Ideal_gas_law ), so all I really need is a way to convert your "altitude" pressures to actual pressures. The US's National Weather Service has a calculator here (https://www.weather.gov/epz/wxcalc_pressurealtitude ) with an associated equation (https://www.weather.gov/media/epz/wx...reAltitude.pdf ). With that equation, I calculate actual pressure from your altitude pressures, then calculate molar volume for each temperature/pressure pair from the ideal gas law.

The hypothesis is that TOD will hopefully be a "simple" function of m and V. Arrange the data so that I can create two scatter "contour" charts of TOD vs V (at constant m) and TOD vs. m (at constant V). On the TOD vs V plot, I see three nice smooth curves, and on the TOD vs. m plot I see 11 curves.

I generally consider polynomials my "equation of last resort", so I begin to explore other ways to describe the data. In your original equation, you described TOD as an exponential function of P, so I wonder if that transformation will help. I select my y/TOD axis and tell Excel to make it a logarithmic axis (exponential equations are straight lines on a semi-log plot). I still have three nice curves, but they don't look any simpler. As a guess, I try making the x axis logarithmic as well (simple power equations are straight lines on a log-log plot). Lo and behold, TOD vs. V appears to be very linear on a log-log plot. Are the three curves evenly spaced? Go over to the TOD vs. m plot and make it a log-log plot as well. There are only 3 points per volume, but each constant volume set looks like a straight line on a log-log plot. I hypothesize that maybe ln(TOD)=m1*ln(V)+m2*ln(m)+b will make a good regression equation (and only 3 parameters -- much better than 10). I set up my regression matrix [ln(V), ln(m), ln(TOD)], run it through the LINEST() function to get the coefficients, and then test. My SSE comes out to about 14 (compared to the 266 with the other equation).

Maybe ln(TOD)=a*ln(V)+b*ln(m)+c is a better regression equation? It is certainly a more stable approach, since it uses the built in linear regression algorithms of LINEST() and only three parameters.

[shg]

Where did you get that regression? It seems sketchy.

I would think that any formula that includes temperature would use an absolute scale (Kelvins or Rankine).