Replicating Excel "Goal Seek" to solve for aggregate by changing rate input

[JCA]

I'm relatively new to using Alteryx and its ability to use iterative macros.

I've been trying to automate a process that is currently done by using Excel's "Goal seek" to solve for a rate.

As an example I've attached the fundamental logic and a simple scenario where Y = MAX([M x X] + C],A) and the Sum of all Y values needs to be 50,000.

I've had some success but always seem to fail at integrating the "MAX" part into the equation/macro.

[LordNeilLord · Accepted solution]

Hey @JCA

How about this? It's a bit rough but it does the job.

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[JCA]

Hey @LordNeilLord

Thanks, that worked a treat, I had to split my population when the iterations maxed out at 2,000 but its been working ever since,

One question though, why do you multiply the multiply the threshold by 1.1?

[LordNeilLord]

Hey @JCA

Sorry I didn't see your original reply!

The 1.1 multiplication is just to make sure that I capture just over the desired value (there's probably a better way to do that)...for example I'm looking for 5000...I want to capture 5010 rather than 4800

Neil

[lauren_collins]

How did you solve for the maxing out at 2000 iterations?  

@JCA

or @LordNeilLord

[cakivibes]

Can I get the workflow to solve this goal seek in Alteryx?

 

[apathetichell]

@cakivibesHey - I'd recommend looking into some of the other solutions to this kind of issue - posting in a five year old thread looking for a solution won't help you out. FWITW I'd explore using running total to create discounted cashflows - and then look into the gap between where you are and where you need to be for the final value. Reminder though - we're all volunteers so "can I get a workflow" might not get you where you need to be. I don't build workflows on spec like this - and this community is really about helping people who are troubleshooting problems in their workflow builds vs building ad hoc solutions.