×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel SOLVER - minimizing a vector?

Excel SOLVER - minimizing a vector?

Excel SOLVER - minimizing a vector?

(OP)
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!

RE: Excel SOLVER - minimizing a vector?

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.

RE: Excel SOLVER - minimizing a vector?

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources