Excel spreadsheet that uses Goal Seek (if possible) to find variable satisfying two conditions

[Oblsss]

Hello to everyone reading this!

My excel spreadsheet contains a cell with a variable value used in two formulas.
Both of these formulas should have values less than or equal to predetermined numbers, a different number for each formula.
Nevertheless, I must calculate the minimum number satisfying the above formulas, following the logic below:

=IF(OR(AND(E2=3,5;F2<=2,35);AND(F2=2,35;E2<=3,5));VALUE(1);VALUE(0))

where E2, F2 are the cells containing the formulas and 3,5 & 2,35 the predetermined numbers (the variable is contained in a different cell).

The goal seek cannot find an answer unless I have already input the correct value in the cell containing the variable.

What am I missing here?

 

[phamENG]

When you say "Goal Seek" are you referring to the Data Sovler Add-In?

It looks like you have two fields, El stress Dint and El stress Dext that are both inputs to your Condition (1 if they fall within either of two ranges and 0 if they are outside of them).

Rather than try to point the solver at H2, set your goal to be E2 or F2. If your goal is to find the lowest Ins value that will give you Condition 1, then run two solver scenarios.

Set Objective: $E$2
To: Value of: 3.5
By Changing Variable Cells: $G$2
Subject to the Constraints: $G$2<=1000, $G$2>=1 (or use more realistic values; these are required for the next step to work), and F$2$<=2.35
Select a Solving Method: Evolutionary
(If F$2$<=2.35 is omitted, it gives 217.3 on the first run. With it, you have to solve twice to get the right answer.)

Then run it again for F2=2.35 and E2<=3.5 and use the lower value. Modify procedure to get what you actually need from the data.

If you'll be using this a lot, it would be worthwhile to write a VBA script to run the solver twice with the various settings, compare the values, and provide the desired result(s).

 

[IRstuff]

Getting back to the original solver issue, you need to understand why your cost function crashed and burned. Solvers, such as the one in Excel, depend on being able to find the direction toward the correct solution from the guess value. This is done by looking at the slope of the cost function at the guess value. This generally requires, then, that the cost function has a continuous derivative. Your cost function is a delta function, so there's no slope for the solver to follow.

As a general rule, Boolean tests are bad choices for cost functions, because they're often discontinuous and rarely have continuous derivatives.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[phamENG]

IRstuff - are you speaking specifically about the GRG Nonlinear solver?

Here's the blurb the solver dialog gives you to guide you in the choice of solver settings:

Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth.

So just as IRstuff said, using GRG Nonlinear needs to look at the slope at the initial value. If you don't have that, you can use Evolutionary which creates a "population" of the data, tests it, and then creates a new one from the results, and continues through multiple "generations" to see how the result is "evolving." Once it stops evolving (converges), you get an answer.

Here's a website that explains the solver functions in just enough detail to get the gist: [URL unfurl="true"]https://www.engineerexcel.com/excel-solver-solving-method-choose/[/url]

 

[IRstuff]

His cost function, as shown in his OP, uses equality tests, which automatically makes that function ill-behaved for any solver, since it's only satisfied at one point and is zero elsewhere.

That's not to mention the ROUND functions, which add another layer of discontinuities

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[IDS]

The spreadsheet linked in the OP is set up to use the Goal Seek function rather than Solver, so the answer to the original question is that Goal Seek is the wrong tool for the problem. Goal Seek finds a solution to a continuous equation with a single variable.

Solver can be set up to minimise a function, subject to specified constraints.
Removing the Round functions from the stress equations, Solver finds a minimum Ins value of 217.4 to satisfy both stress constraints. It will work with any of the solvers, but the evolutionary solver is very slow, and the GRG Nonlinear solver is near instantaneous.

Note that the solver add-in is not enabled by default. Go to File-Options-Add-ins Manage Excel Add-ins if it does not appear at the right hand end of the Data Ribbon.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[GregLocock]

One effective method for finding simple optima is to use a Monte Carlo, that is, just random sets of inputs. Engineering judgement can then be used to find the global optimum.

here's the solution space for a 2 factor problem, the black square is a feasible solution using production parts, not quite as good as the optimum at(2.8,-.45) but not bad. This was all done in excel.

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[phamENG]

IDS - that's odd. I ran the same thing but the GRG Nonlinear kept coming back with a fault for me. I could only get it with Evolutionary (slow though it is).

 

[IDS]

phamENG - see attached spreadsheet.

Note that the stress constraints are entered as an upper limit, rather than a required value.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

I had another go at this with Goalseek.

I set up two cells (E4 and F4) with the difference between the maximum allowed stresses and the calculated values, then I changed the "condition" formula in H2 to:
=IF(MIN(E4:F4)<0,1,MIN(E4:F4))*1000.

You can then use goalseek to adjust G2 so that H2 is close to zero.

It's a bit slow, and works best if the starting value is greater than the optimum, but it does work. Starting with a value below the optimum it often comes up with a poor approximation. In that case just accept the poor answer and run it again with that as the starting point.

Revised spreadsheet attached.

Edit:

That's over complicated. Changing the condition formula to:
=MIN(E4:F4)*1000

is simpler and works better with starting values less than the optimum.

Revised revised spreadsheet attached.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/