Excel SOLVER - minimizing a vector?

[zl1franco]

Hi,

Not sure if this has been addressed in a different forum, but I was trying to figure out if it is possible to minimize a vector using the SOLVER feature of Excel (i.e. multiple "target cells"). Any ideas/suggestions would be greatly appreciated.
Thanks!

 

[electricpete]

You need to combine the elements of your vector into a single target cell.

Assuming the vector starts at 0 and ends at the vector coordinates (u,v,w,x,y,z), and further assuming your objective is to minimize the length of the vector, you could generate a new cell with the length:

L = sqrt(u^2+v^2+w^2+x^2+y^2+z^2)

...and use this L for your minimization.

Note the sqrt makes it easier to understand but is unnecessary. Minimizing the sum of squares also minimizes the square root of sum of squares.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[GregLocock]

One slight refinement is to use a weighting function for each sub-target.

In practice where I am fairly sure that a solution within the desired bounds is not achievable I use a quadratic cost function, with variable tolerances for each sub-target. Then I can just type in new tolerances and sub-target means on the fly without having to rebuild the cost function.

The other advantage of having a table explicitly for this is that you can include any amount of logic, although you need to be aware that the solver is not expecting to have to cope with if type statements, so you may get unsolvable situations. If you need a lot of logic then it is better to go with a monte carlo solution rather than the solver, but that is harder to set up and takes longer.

If you really want to go mad try a genetic algorithm, they are robust in the face of complex logic, but again take a while to set up and run - eg optimising 15 parameters can take an overnight run for a calculation that takes about a minute.



Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.