Forum Discussion
problem with solver in excel
mathetes Thank you for your reply. sorry for the late reply as it was weekend for us.
I am attaching here with sample data sheet in which column HB is total but I need total as per Column HC.
I have tried Solver but it is giving the error while changing the total.
Please have a look at the attached workbook. It took about 30 seconds to create the formulae needed across the 208 columns. I did the calculations in Sheet2. You can easily copy all of this down the rows you need to recalculate. Once you have done this, you can copy-paste-values in order to preserve the calculated values and delete Sheet1. No need for Solver.
- Thanks for reply. I know this solution but I am searching like goal seek or solver solution.
What Peter Bartholomew is telling you is that your request is comparable to asking for the use of a large chain saw to cut in half a wooden chopstick. Is it possible for a chain saw to cut a chopstick in half? Yes. But utterly silly, given that a pocket knife can do it too.
You need to use tools for tasks that have the complexity and magnitude the tools are designed for.
Solver is a remarkable tool, truly wonderful. If you want to see it in operation, though, to actually experience that power, you should come up with a situation for which mere formulas don't work. Look at the Excel help text itself for the kind of things that Solver can solve, and come up with your own variations. If you don't have any, wait until you do.
It is possible to set up an optimisation problem with the solution you desire but the result is trivial.
The setup would require a set of parameters p that would be the base values you wish to scale for a solution. The optimisation variables x could be set with an initial value 1, as would the further variable s which is used to scale the solution.
The objective function could be
= SUM( (x - p*s)^2 )
and the total
= SUM(x)
would be constrained to equal the target value set.
The non-linear solver gives the trivially obvious solution.
