How do I go about replicating the goal-seek directly in R? I've tried 5 different ready-made R packages, and optim( ) / optimize( ) / polyroot( ) functions but still could not get the desired rate.
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. However, I cannot get the same value as when I use Excel Goal-Seek. I treated this as an internal rate of return problem using the uniroot( ) function in R. The objective is: E44 = 0, by solving for rate. Respective columns are calculated as follows:Ĭol_B: initial principal (B2) of 106700000 fixed, otherwise equal to prior row of Col_E Hi have a similar issue as In the attached "sample_data", I have a payment schedule. 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! Introduction Storing intermediate results as variables for later use Using the solver feature to solve an arbitrary equation with a single unknown variable. There doesn't really seem to be a way to do this in Alteryx that I can see. 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.
#Polyroots ms excel series
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. 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:Īnd hit “Okay.” Excel then iteratively finds the value for the cell J6 that makes the cell G9 equal 330. Here's what Microsoft explains about goal seek. 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.
0 kommentar(er)
