Well, it is an interesting challenge, and I’d say it’s not really something that Knime is designed for, as it becomes more of a programming challenge, than a data science one. There are really a couple of ways of writing a goal seeking algorithm that spring to mind. The first (simplest and less efficient, and the one I’ve had a stab at here) is to provide the system with a min value and a max value and then get it to iterate between the two, varying each time by a small increment (or decrement) until the final result is within the required tolerance of the required answer.
An alternative to that would be to perform an iteration loop by making a series of “guesses”. Based on each guess, the logic would be to move the next guess either higher or lower depending on whether the result of the current guess were moving toward or away from the required target compared with the previous guess. Typically each guess is made at a perceived “mid point” between the previous guess and the required direction of travel. This is generally more efficient and requires fewer tries to get to the required outcome, but the logic for that would be a little more involved. I suspect though that this is something akin to the mechanism used by Excel.
I have attempted to demonstrate a workflow which tries the first of these two, but I certainly wouldn’t recommend using it for anything other than demonstration purposes, or if you have time some sort of refinement. (Iterations of this type are expensive in terms of time to create, and time to process)
Once I finally had a solution, however, I struggled to make it fit the results of your spreadsheet. I finally realised that your spreadsheet’s goal seek divides by 6 instead of 5 (the actual number of data rows). My workflow counts the number of data rows rather than hardcoding the number and as a consequence you won’t get it to agree with the excel example you’ve given
The workflow is supplied with a “parameters” table (Table Creator) into which you need to enter the required target value, the minimum (current value), maximum, increment step, and tolerance from the required target. If you make the increment step too large, there is every chance an iteration will “jump over” the required answer because it will never be within tolerance, but if the step is too small, the iterations will take too long!
I would doubt that this workflow is likely to be the answer you are looking for in terms of achieving this with Knime. As you can see to handcraft this is not the “single click” that you’d have in Excel, and I’ve had to jump through a few hoops to make this work. It doesn’t necessarily mean though that such a task is completely out of the question, but I’d say it’s not really the type of job that a workflow solution is designed for.
when I was struggling to change the value of a flow variable in a loop! It is definitely one to add to the armoury!
