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

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

(OP)

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:

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?

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?

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

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).

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

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: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

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

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

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: https://www.engineerexcel.com/excel-solver-solving-method-choose/

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

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: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

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

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/

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

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: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?

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

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

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/

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

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/