Hi all, I’m working on a small project and I’m looking to build a neat graph. In order to do so I need to calculate a couple hundred values via a goal seek operation.
Essentially I need a way to goal seek a column of values to zero all at once by varying a corresponding value two rows to the left of the one I’m setting to zero. In excel the first value that I want to goal seek is located at R88C5, while the value I want to vary is R88C2. How may I extend this goal seek function to an arbitrary column length?
Sorry to say but I need an answer as quickly as possible! Thanks in advance.
I found solver in 2007, but it doesn’t seem to be able to work across numerous cells at once. By that I mean I must change a value to zero by variation of a corresponding value which is not tied by an analytic equation. This is why I attempted the goal seek method.
Umm… Solver is Goal-Seek, but on steroids. If you’re trying to seek a column of zeros do the following:
-sum the outputs into a single “Target Cell”
-tell solver to “Set Target Cell” Equal to “0”
-By Changing Cells “insert input range here”
-add appropriate constraints (i.e. maybe no negatives for inputs, etc)
Solver will change all the inputs until the sum of the outputs is zero. If your program is not linear or has weird kinks, that will cause problems.
The only other way I know is to code VBA with some Logic Loops (essentially programming a goal-seek code)
Hope that helps
The project is over with, but this particular revelation will likely profoundly alter my academic career. I had tried similiar amalgamations using regular old goal seek, but always got a negative answer. Thanks dude !
