Numerical Iteration or "Goal Seek" replacement

Ask for Help
1

Does anyone know of a way to run a numerical iteration to solve for a number?

Unfortunately the function I need to use, cannot be solved algebraically. Looking through some of the prior help topics, there may have been a plugin people were trying?, but that was 4 years ago. For those familiar with excel, this would be the same as “Goal Seek” in the Data/“What-If” section.

2

Can you describe more about your use case? Have you tried using JavaScript for this?

3

I haven’t tried to use JS. honestly, I’m not ever sure where to start with JS.

The issue is trying to solve high degree polynomials or with multiple types of functions. For example…
y = (1-e^(-x))/x

For my app… I have a value for y and am trying to solve for x. The only way to solve this would be to iterate by guessing values for x and converge on a value for y. So essentially you’re guessing until you find an x that matches y. Without going into a bunch of detail, there’s several factors (like the number of iterations or the “exactness” of the answer) that go into Excel’s built in tool, goal seek.

4

I think you could probably fork the below and then adapt it to work as a Glide external code column.

See also:

1 Like
5

Do you have an example of a number that you would use for ‘y’ and what the result would be for ‘x’?

6

Yeah so the x will be between 0 and 1. Technically it can’t be 0 as it makes an invalid mathematical expression. Y will tend to be in the thousands to millions. Note… for simplicity I stripped the constants out of the equation.

FWIW… I was able to find a decent work around for most situations. It’s not as precise as the goal seek but if I can’t get the JS forking to run then it can probably work for the time being.

7

I have some javascript built based on the formula you shared. Not ‘Goal Seek’ per se, but specific to the formula you shared, and I was just looking for some real world examples to verify if what I had worked or if it was spitting out garbage numbers.

Since you say y can be thousands or millions, I’m guessing my javascript isn’t working correctly.

8

You could be correct. The constants will have a large effect.

y = (1-e^(-x))/x is actually

y = C1(1-e(-C2*x))/x

Constant 1 (C1) is calculated in the app but has a large range but will typically be between 1000 and 10,000.
Constant 2 (C2) will usually be 50 but is a user input that could be changed (to 30, 40, 60 or 70)

9

Still would be nice to have some actual real world input and output example numbers to know if I’m on the right track, but this does do something…just don’t know if it’s giving the result you want.

function solveForX(yString, c1String, c2String, epsilon = 1e-6) {
    const y = parseFloat(yString);
    const c1 = parseFloat(c1String);
    const c2 = parseFloat(c2String);
    if (isNaN(y) || isNaN(c1) || isNaN(c2)) {
        return null;
    }

    let x = 1; // Initial guess
    let previousX;

    do {
        previousX = x;
        x = x - (functionToSolveForX(x, c1, c2) - y) / derivativeOfFunction(x, c1, c2);
    } while (Math.abs(x - previousX) > epsilon);

    return x;
}

function functionToSolveForX(x, c1, c2) {
    return c1 * ((1 - Math.exp(-c2 * x)) / x);
}

function derivativeOfFunction(x, c1, c2) {
    return (c1 * (Math.exp(-c2 * x) * (c2 * x - 1) + 1)) / (x * x);
}


const yString = p1; // string input for y
const c1String = p2; // string input for C1
const c2String = p3; // string input for C2
const x = solveForX(yString, c1String, c2String);
if (x !== null) {
    return ("x =", x);
}
1 Like