Find goal in Excel: solve the mystery

The basics 

The Find Goal tool in Excel allows you to fill in the value of a formula to achieve a desired result. For example, you can see what interest rate you need to get a loan with a certain payment level, or what grade you have to get on your next test to pass the course. In mathematical terms, it allows you to solve an equation with a single unknown, without headaches!

Ninja Tip: For more complicated tasks where you have multiple formulas with different constraints, you can use the SOLVE. 

Key information

Target cell: It is the one that you want to have a certain value, such as the minimum grade to pass the course or the monthly payment you want to make for a loan. That is, it is the result of an Excel formula for which you have the result, but not all the elements that make it up. It is important to be clear that this value you define it.

Changing cell: It is the one that you give to Excel to define the value. It is the “unknown” that must be resolved to reach the objective value, the element of the formula whose value you do not know. 

Step 1: Define the parameters

The following table shows the grades for the two semester tests. In this part, you simply have to manually determine the information you know, which are the scores of your two past tests

Step 2: Specify the formula to solve that will be the objective

You still do not have the exam grade, but you would like to know what grade you have to get so that the final course grade is 50%. This is simply the average of all course grades. Thus, you must tell Excel how to calculate the result, which is to apply the average function. 

The table finally looks like this: 

Step 3: Open the “Find Target” tool

In the Data tab, go to the “Forecast” section, select “What-if analysis” and then “Search target”

Step 4: Define the objective

A new window will open in which you must fill in the target value and cell that will change to reach it. In “Define the cell”, select C6, which is the one that contains the final grade of the course and the one you want to be filled with a specific value. 

Then in “With the value”, manually indicate the value you want it to take. In this case, the minimum to pass the course, which is 50. This is the goal you want Excel to look for. 

Finally, in “Changing the cell,” select the cell whose value you want to find. In this case, the exam grade located in cell C5. 

To finish, press “OK”

That's it! We see that Excel shows us in a window (“Goal Search Status”) the development of the task and tells us if it has been successful. In this case, we have the value for Exam left at 60, while the Final value was 50, exactly what we wanted! Now you know that the minimum score to pass is a 60% in the exam. 

Search target for weighted grades

Did the previous case fall a little short for you? Don't course grades weigh the same? No problem! The key here is simply to define the parameters and the formula well. 

Step 1: Indicate the parameters for the calculation. In this case, the grades with their respective weighting. 

Step 2: Specify the formula. In this case, we will use SUMPRODUCT which allows us to make the weighted average of the grades. 

Step 3: Open the “Find Target” tool 

Step 4: Define the objective. As in the previous case, we want the exam grade cell to change so that it gives us a final grade of 50. 

That's it! With this, you need to have a 57.5% on the exam to have a final grade of 50%

Find objective for the case of a credit

The following table has the parameters of a loan to buy a house. The credit is for 20 years with a rate of 5% and a total of 12,000. With the formula PAY You determined that you must pay 962.91 each year to cover the credit.

Ninja Tip: For more details on accounting for your debts and its relevant functions, see this post.  

The problem is that the maximum budget you have is 900 each year. So, what is the maximum loan you can get by paying installments of 900? In the objective function tool, indicate that you want the payoff to be -900 (it should be negative since it is money that you are giving) and you want the value of the loan to be the one that changes. 

Thus, with 20 years and a rate of 5%, you could obtain a loan of 11,216 if you paid 900 each year. 

If you don't have flexibility with the amount, you could see how many years it would take you to pay off a loan of 12,000 with a payment of 900. The only difference is that, in “Changing the cell” you must select the one that contains the years instead of the one that contains the years. contains the loan. Your objective remains the same, to pay 900 each year, but changing other things such as the time it will take. 

Thus, it would take you approximately 2 and a half more years to pay the loan if you reduce the payment to 900 each year. 

This is how you can apply this feature for a large number of tasks. The most important thing is that you know what the parameters are, what the objective is, and what cell or cells you can change to achieve it.