Replicate Excel Goal Seek in Python

Sharing is caring!

Last Updated on July 14, 2022 by Jay

Excel provides an excellent feature – Goal Seek, which allows back-solving an input value when given the end result. It is a handy tool, so today we’ll learn how to replicate Goal Seek in Python.

This tutorial is part of the “Integrate Python with Excel” series, you can find the table of content here for easier navigation.

How to Goal Seek in Excel

If you are not familiar with Goal Seek in Excel, it’s a feature under “What-If Analysis”:

Let’s look at this simple example: we have an equation z = x^2 + y^(1/3). If we let x = 3, and y = 10, plug them into the equation, z = 11.15443. Easy peasy!

Now let’s make it more interesting, assume you want the end result z = 90, hold x constant = 3, what value should y be? We can use Excel’s Goal Seek to back-solve the value of y. Goto Data -> What-If Analysis -> Goal Seek. Set z = 90, by changing y. As you can see, after a couple of seconds, Excel was able to back solve a pretty close number for y, which is 531423.3. Plugging it in, we get z = 89.9991, which is very close to our desired result of 90!

Apologies for the low resolution gif, I’m still learning how to make a proper gif, and will update with a higher quality one when I figure out!

What’s happening in Excel Goal Seek

If you pay attention to the Goal Seek window during solving, you’ll see this line “on iteration xxx…” Essentially, Excel is performing the following tasks during goal seek:

1. Plug in a random guess for the value of y
2. Calculate z given x = 3 and the above value of y
3. Measure how far away the resulting z is from the expected result of 90
4. If #3 suggests that the result is still far away from the desired, go back to step 1 with an adjusted value of y
5. Repeat #1 – #4, until the desired z is arrived, or a threshold is met

Those readers good at math might suggest that you can just solve for y from the equation. While that’s indeed correct given this simple equation, there are many situations where inverting an equation is impossible, and that’s where Goal Seek can bring value. My example here might be oversimplified, but I hope you get the idea.

Goal Seek in Python

Once we know the logic, we can implement it in Python! Let’s first formulate the equation.

def z(x,y):
    return x**2 + y**(1/3)

>>> x = 3
>>> y = 10
>>> z(x,y)
11.154434690031884

Binary search algorithm

Next, we need a function to perform the back-solve. There are many algorithms to back-solve for input values, the one we’ll be exploring is called a binary search. The idea is as follows:

1. We know that the value we are trying to solve likely falls between some range, but we don’t know what exactly that value is.
2. We take the mid-point of the range , plug it into the equation, see how far away we are from the target value. Based on the difference, we can determine whether the value is in the lower half or upper half of the range.
3. Then we take the mid-point of the new range and test it again. Repeat step 2-3 for as many times as needed, until the difference is without our tolerance.

Binary search example

Let’s see a simple example to get a hold of the above algorithm. Let’s say we want to guess a number from 0 – 100. The answer is 85, but assume for a minute that you don’t know the answer is 85. Let’s begin:

• Original range is 0-100, initial guess is 50. Too low (compare to the answer 85) -> new possible range is (50, 100)
• 2nd guess = (50+100)/2 = 75. Too low -> new possible range is (75,100)
• 3rd guess = (75+100)/2 = 87.5. Too high -> new range is (75, 87.5)
• 4th guess = (75+87.5)/2 = 81.25. Too low -> new range is (81.25, 87.5)
• 5th guess = (81.25+87.5)/2 = 84.375. Too low -> new range is (84.375, 87.5)
• 6th guess = (84.375+87.5)/2 = 85.9375. Too high -> new range is (84.375, 85.9375)
• 7th guess = (84.375+85.9375)/2 = 85.15625. At this point we are only 0.15625 away from the True number, so we can decide to stop.

Binary search for function z

Our problem is slightly more difficult than guessing a random number from 0-100, but we can still use the binary search. Let’s set up a possible range from 0 to 1,000,000. Hint: 1000000 ^(1/3) = 100, so the target number 90 implies that the y value has to be between 0 to 1,000,000. We also define a threshold, which is the tolerance for the difference between the true number and our best guess. Below is the binary search code, I’ve also added some print statement to help visualize which range the current guess is in.

def goal_seek(target,_threshold):
    threshold = _threshold
    lower = 0
    upper = 1000000
    solve = (lower + upper)/2
    while abs(threshold) >= _threshold:
        print(f'Threshold is: {threshold}')
        print(f'range is: {lower}  ----  {solve}   ----   {upper}')
        if threshold < 0:
            upper = solve
            solve = (lower + upper)/2
        elif threshold > 0:
            lower = solve
            solve = (lower + upper)/2        
        threshold = target - z(3,solve)

    print(f'Final result: Threshold: {threshold}....Solved input: {solve}')
            
    return solve

Let’s test it with a timer:

import time
start = time.time()
goal_seek(90, 0.00001)

print(f'Finished in {time.time() - start} seconds')

###### Skipping a bunch of output lines here ######

Final result: Threshold: 1.357422164005584e-06....Solved input: 531440.9732818604....z=89.99999864257784
Finished in 0.27593541145324707 seconds

It took Python 0.2759 seconds to solve. Roughly 10x faster than Excel, not bad!