Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations cowski on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel SOLVER - minimizing a vector?
- Thread starter zl1franco
- Start date
electricpete
Electrical
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.
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
Automotive
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.
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.
- Status
Similar threads