In high school, I used to solve mathematical equations where I have the result of the equation but don’t have the input value.
So, if you think about to solve the same kind of problems in Excel, you have a specific tool which can help you.
Its basic idea is to find the input value which a formula needs to get that result.
In simple words, if you know what actual result value you want, you can use goal seek to find the best input value for getting it.
Let’s say you need to borrow some money from your friend.
You know how much money you need, how long you want to take to pay off the loan, and how much you can pay each month.
You can use goal seek to calculate the interest rate you will need to secure with your friend.
There is n number of complex problems which you can solve with goal seek.
So today, in this post, you’ll learn how to use goal seek in Excel, how it works and what are the important points you need to take care.
Let's get started.
To open goal seek use one of these two methods.
You can use these methods to activate goal seek in Microsoft Excel 2007 to 2016 versions.
When you open goal seek, you’ll get a small pop-up to input data. Basically, it has three required components.
Imagine you’re working in a company who is trying to get a tender order through quoting the lowest price.
For this, you need to prepare a price structure in which your landing price (to the customer) should be $1000.
Whereas your present final price is $1089.9.
If you want to do it manually you have two options.
It’s not as easy as it sounds but goal seek can be a game changer here.
Download this file from here and follow the below steps.
Now, if you check base price cell, it values is changed to $925 and with this, your landing price is changed to $1000.
Your base has changed by goal seek using a hit and trial method. But, goal seek is pretty quick in this and you can’t see the entire process.
These are few points need to be kept in mind while using goal seek.
You can enjoy your coffee while working with Excel’s goal seek command. Hope you don’t mind if you can add some extra chocolate in it using VBA.
Yes, you are right, you can use goal seek with VBA.
expression .GoalSeek(Goal, ChangingCell)
Here is a macro code which you can use.
Dim Target As Long
On Error GoTo Errorhandler
Target = InputBox("Enter the required value", "Enter Value")
.GoalSeek _Goal:=Target, _
MsgBox ("Sorry, value is not valid.")
Download this sample file from here to learn more.
As I said, if you try to find input value by applying manual method you need to spend a lot of time or create a formula to perform a back calculation.
But by using goal seek, you can do this in a single click. It will do all the complex calculations for you in a second.
The best part is, you can use it with VBA as well. And, I hope this will help you get better at Excel.
Now tell me one thing. Do you have any complex calculation to solve with goal seek? Please share with me in the comment section, I’d love to hear from you.
And, please don’t forget to share this tip with your friends.