Replicating "Goal Seek" functionality from Excel

[AliKat]

There is a great functionality in Excel that lets users "seek" a value that makes whatever chain of formulas you might have work out to a given value. Here's what Microsoft explains about goal seek: https://support.office.com/en-us/article/Use-Goal-Seek-to-find-a-result-by-adjusting-an-input-value-...

My specific example was this:

In the excel (attached), all you have to do is click on the highlighted blue cell, select the “data” tab up top and then “What-if analysis” and finally “goal seek.” Then you set the dialogue box up to look like this:

 Set cell: G9

To Value: 330

By changing cell" J6

And hit “Okay.” Excel then iteratively finds the value for the cell J6 that makes the cell G9 equal 330. Can I build a module that will do the same thing? I’m figuring I wouldn’t have to do it iteratively, if I could build the right series of formulas/commands. You can see what I’m trying to accomplish in the formulas I’ve built in Excel, but essentially I’m trying to build a model that will tell me what the % Adjustment rate should be for the other groups when I’ve picked the first adjustment rate, and the others need to change proportionally to their contribution to the remaining volume.

There doesn't really seem to be a way to do this in Alteryx that I can see. I hate to think there is something that excel can do that Alteryx can't! Any ideas on ways to do this? I've cross posted this in the "ideas" section, in case it really can't be done yet!

[JohnJPS · Accepted solution]

Good question.  You know it's good when there's no response for a few days!

This isn't the prettiest solution, as it just reverse engineers what went into the problem, turns that into a function in R, then finds the root of that function. In that light, doing something similar for a different problem will also involve jumping through a few hoops, notably coming up with the function for R to solve.

But, once implemented, you can change the target (e.g. sum=330), and also your supplied "Group Adjust" values (e.g. X = 0.75), and it should recalculate without issue.

Please see the attached workflow for more, including a few in-line comments in the R code. Hopefully if nothing else, it will be a good head start.

[jdunkerley79 · Accepted solution]

As @JohnJPS you know it's tricky when no fast answer.

If you don't want to use R the other solution is to use an iterative macro. To do this you will be effectively recreating something like a Newton Raphson solver.

I would do this as two macros. One implementing the calculation and one implementing the solver.

As having been meaning to try this for ages have attached an implementation of your sheet. Consists of 3 parts. A demo work flow, the iterative macro and the calculation macro.

[AliKat]

Thanks so much!

[j_s_xue]

Hi @JohnJPS,

I have a similar issue as @AliKat. In the attached "sample_data", I have a payment schedule. Respective columns are calculated as follows: 

Col_A: fixed

Col_B: initial principal (B2) of 106700000 fixed, otherwise equal to prior row of Col_E

Col_C: Col_A/360*Col_B*rate

Col_D: fixed

Col_E: Col_B + Col_C - Col_D

The objective is: E44 = 0, by solving for rate. I treated this as an internal rate of return problem using the uniroot( ) function in R. However, I cannot get the same value as when I use Excel Goal-Seek. 

In "sample_data.xlsx", you can see the calculated roots are identical until the 5th decimal point, however, when you compare the payment schedules calculated using the roots (in the "sample_workflow.yxmd", only the Goal-Seek root makes E44 = 0 (green container), whereas the uniroot( )$root leaves about 75000 remaining in E44. 

I've tried 5 different ready-made R packages, and optim( ) / optimize( ) / polyroot( ) functions but still could not get the desired rate. How do I go about replicating the goal-seek directly in R? 

I realize this thread is over 3 years old but might as well give this a shot. 

Thanks in advance for your time. 

[JohnJPS]

Hi @j_s_xue,

It seem slike possibly rounding errors; I noticed the input text in tehe workflow's "cfs" field is rounded to 2 digits, whereas the spreadsheet is not.  Could that make a difference?

Hope that helps!

John

[j_s_xue]

Hi JohnJPS,

Alas that was the first thing I checked, even before I tried to rewrite the R code using different functions and R packages. I think the only chance to do this correctly in R is replicating the goal-seek process. 

I'd imagine a for-loop within the optimization function, which will then serve as the input function to uniroot( ). However, I can't quite get the function to correctly wrap the for loop. 

I found another community post with the author trying to solve the exact same problem, but there's been no reply: 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Using-R-script-to-find-a-solver-or-usi...

Any pointer is appreciated at this point. Thank you!

[JohnJPS]

Hi @j_s_xue,

There's always @jdunkerley79's iterative macro approach mentioned in this post; may be worth a try.

Python may be an option too, thought R and Python are typically pretty close in functionality.

 - John

[j_s_xue]

Thanks for the suggestions John. Unfortunately, I'm not very familiar with Python, but I doubt it's due to an inherent calculation error within R. Only issue with using an iterative macro is I'm not sure whether the macro can replicate something like a Newton-Raphson solver. 

In any case, I knew this was a long shot. But figured might as give the community a try. Thanks again for your time!

[JLM13]

Hi I am trying to solve a similar issue with using excel “Goal Seek” to find a beginning payment amount and having a hard time translating the Newton example. Any help or suggestions would be much appreciated!

Criteria (sample data with formulas attached)
119 payments
Remaining balance to 0 after payment 119
3.04% payment escalation every January 1st
Payment then stays constant for the reminder of the year Feb-Dec

Thank you!