Complex simultaneous equation, smart solver help

**Cirion** · 12-06-2009, 05:11 PM

Hi, I have a semi complex EXCEL problem. Here is the deal.

I have a known solution to a problem with several unknowns. Let's make it simple, and say:

x + z + w + v + u = 10

I want excel to basically randomize 5 variables that will make this solution work. Here's the second catch though:

x, z, w, v, and u must be in a certain range. For instance, let's say:

x must be between 2 and 6
z must be between 0 and 2
w must be between 1 and 4
v must be between 2 and 3
u must be between 4 and 5

Can anyone point me on the right track? (Keep in mind that my knowledge of EXCEL is still very low so please explain in full detail on how you would approach this...)

Thanks...

PS - the variables needn't necessarily be integers...

**martindwilson** · 12-06-2009, 06:47 PM

try using the solver add in
(if its not activated go to tools /addins and select it)
then in say a1 to a5 put any random values you like
in b1 put =sum(a1:a5)
then tools/solver
set target cell as b1 by clicking it
select "by changing cells" and select a1:a5
add the constraints for each cell so for a1 >=2 and<=6 which will represent x then a2 will represent z and so on
set the precision in options
ok
then solve
repeat with a different set of random numbers in a1:a5 as many times as you like
see pics

**Cirion** · 12-07-2009, 02:28 AM

Thanks...

It seems like it won't work for my problem though. You see, the actual equation is far more complex than that. Apparently, then, because the solver is only linear, that it doesn't work. Also, I am using Open Office (the solver is pretty similar, though.) If you want to know the exact equation and variables I want to solve, let me know, maybe you know of some more advanced trick.

**zbor** · 12-07-2009, 02:41 AM

Would be easier to upload workbook...
Avoid important data but remain important structure.

**martindwilson** · 12-07-2009, 03:40 AM

here we go! ask one question then change it to something else.
how would anyone know that your equation is far more complex and even if it id it might help if you had posted an example in the first place.

**Cirion** · 12-07-2009, 02:26 PM

I just wanted to know the concept behind it, I didn't know that the solver was only linear. (I do feel kind of dumb now, after looking at it, should have guessed... had never touched the solver until now though...) I figured the same concept could be extended to the my more complex problem, and thus that I wouldn't have to waste anyones' time with a little more of a headache of a problem. Sorry about that. Anyway...

Here is the uploaded excel file. You only really need to worry about the cells in VIOLET color (they are cells AU-AY)

http://www.filefactory.com/file/a1hb...imization3.xls

The equation I am actually using is the section lift coefficient equation. If you don't know what it is, it is right here.

http://en.wikipedia.org/wiki/Lift_coefficient

Cl = L' / (.5 * rho * v^2 * c)

Where:

L' = L / unit wingspan
Wingspan = Lc

So what I am trying to do, is essentially come up with the design space of the wing. To do this, I want to find out all (or a large portion of) the solutions in which Cl = 2.

Variables will be:

c (between 3 and 12 inches)
Lc (between 12 and 48 inches)
L* ( between 35 and 200 lb)
V (between 25 and 300 MPH)

*Note that L and W will be synonymous... since I am setting lift equal to weight (level flight)
**You would note also that I am kind of mixing units. I take care of that in all the conversion factors, for the sake of solving the problem don't stress too much about this. In cell AY16 and down, this is already solved anyway.

Now, I set up the table as you suggested for the easy example, and it actually seems like it comes up with a maximum, sort of, but can't do a minimum, and can't do anything when it comes to solving Cl = 2.