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:

- Plug in a random guess for the value of y
- Calculate z given x = 3 and the above value of y
- Measure how far away the resulting z is from the expected result of 90
- If #3 suggests that the result is still far away from the desired, go back to step 1 with an adjusted value of y
- 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:

- 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.
- 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.
- 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!

A little late to this post, but the code provided does not output exactly what you show…

No Z value in the print, and my time shows 0.0 seconds (which is also weird as normally I get a similar result with many decimal places)

Also noticing that the “3” value is hard coded into the code, I assume that this should be a definable variable for future flexible use cases?

I find your blog to be quite useful, thank you for taking the time to put all this together, combining Python, VBA and office products is such a powerful tool set!

Hi Mark,

Thanks for the message!

Yes you are correct that the code doesn’t output the z value. I put it there just to show the back-solved value will give a very close approximation.

In the example, i used a simple function z=x**2 + y**(1/3), which is mainly to show the concept and how to apply it. In reality you probably will have a different function to solve for.

Glad that you find my blog provides some value 🙂 I’m a big fan of automating boring and mundane tasks and love sharing and helping others!

Yes! It took me a bit to sort out what you were doing, I finally realized that the solve was for that very specific use-case.

But thank you for showing the binary-search method, now to get some other libraries working to build that into an existing excel sheet! Cheers and thanks again for all the time you put into this blog