Create LAMBDA function to substitute the Goal Seek or Solver Functions

[AEAA]

Hi Everyone,

I recently discovered that with the use of recursive LAMBDA functions, it would be possible to set up a logic to replace the Goal Seek or Even the Solver function in Excel. Has anyone created a LAMBDA logic already that does the job? I tried doing it myself, with the help of ChatGPT and didn't get much success. I tried using the Newton-Raphson method for the iterations, but I guess any simpler method is also good enough.

This is a draft I get from ChatGPT.

=LET(
max_iter, 1000, // max number of iterations to prevent infinite loops
epsilon, 0.0001, // acceptable error
f, LAMBDA(x, YourFunctionHere),
df, LAMBDA(x, YourDerivativeFunctionHere),

NR, LAMBDA(x, iter,
IF(iter >= max_iter, x, // base case: too many iterations
LET(
fx, f(x),
dfx, df(x),
new_x, x - fx/dfx, // Newton-Raphson update
IF(ABS(new_x - x) <= epsilon, new_x, // base case: result is accurate enough
NR(new_x, iter+1) // recursive case: keep iterating
)
)
)
),

NR(StartingValueForX, 0) // start the recursion
)

But I haven't had time to break it down and make it work. Would appreciate if anyone had a working approach.

Ideally the function and input it would look something like this:

GOALSEEK(set_cell;to_value;by_changing_cell)

If anyone has set up an even more complex logic, for example to do multiple cells or more constraints like in Solver that would also be great.

Thanks for the help!

 

[mleewis]

Did you get this to work? I was just thinking i could use a goal seek function like this.
If you made any progress I hope you can share it.

Thanks!

 

[AEAA]

Did you get this to work? I was just thinking i could use a goal seek function like this.
If you made any progress I hope you can share it.

Thanks!

Hi, unfortunately I could not. But honestly I didn't try too much after this message as I got busy with other tasks.

However, I think it would be very useful!