Soldato
- Joined
- 18 Oct 2012
- Posts
- 8,341
so i have what essentially boils down to the following:
single input value>many complicated things over multiple sheets>a set of 6 values.
what i'd like to do is use solver to try and figure out which setting of the input value minimises all of the 6 output values.
currently i've averaged the 6 final values just to give a single cell although i'm not sure if this is the best way to go about it (because for example one value being really high whilst another is really low isn't really ideal)
unfortunately i seem to be consistently getting "solver found a solution" with the solution being the same arbritary initial value of the input cell.
now manually changing the input cell will quite quickly show that this is not the optimal solution. however because the system isn't remotely linear (eg a setting of 5% might yeild x, 5.5% might yeild Y, 6% yeilds X again meanwhile the best solution could be 0.001% or 25% etc) i'm wondering if excel is trying to take little steps, seeing moving either direction isn't improving things and coming back to the starting value claiming it's finished.
anyone any advice? maybe i'm not using the right tool for the job here.....
single input value>many complicated things over multiple sheets>a set of 6 values.
what i'd like to do is use solver to try and figure out which setting of the input value minimises all of the 6 output values.
currently i've averaged the 6 final values just to give a single cell although i'm not sure if this is the best way to go about it (because for example one value being really high whilst another is really low isn't really ideal)
unfortunately i seem to be consistently getting "solver found a solution" with the solution being the same arbritary initial value of the input cell.
now manually changing the input cell will quite quickly show that this is not the optimal solution. however because the system isn't remotely linear (eg a setting of 5% might yeild x, 5.5% might yeild Y, 6% yeilds X again meanwhile the best solution could be 0.001% or 25% etc) i'm wondering if excel is trying to take little steps, seeing moving either direction isn't improving things and coming back to the starting value claiming it's finished.
anyone any advice? maybe i'm not using the right tool for the job here.....