B19 is the cell to be varied. The value calculator calls a macro function that works fine. If I manually enter the goal seek parameters from the 'Tools' menu, it works fine. But when I edit cell C19 to change the goal, it doesn't get the answer. It seems to be calculating but it doesn't find the goal.
Excel has a lot of functions which make your life easier. One such feature is Goal Seek.
It can be used to help you speed up your calculations. Goal Seek is part of a suite of commands sometimes called what-if analysis tools. When you know the desired result of a single formula but not the input value of the formula needed to determine the result, you can use the Goal Seek feature.
When goal seeking, Microsoft Excel varies the value in one specific cell until a formula that’s dependent on that cell returns the result you want. I am explaining it by taking an example, Say, You’ve got a fixed amount of your monthly salary that can go towards your loan payment and are now wondering which interest rate will best suit you. Here is how you can solve it using Goal Seek. STEP 1: Create a table The use of estimate amounts for calculations will result in an inaccurate result.
Hence, the first step to answer a loan-related query is to get accurate values for all the factors revolving around the loan scheme. Before initiating the goal seek feature, enter these values into a new worksheet.
You see that a monthly payment of “1,014.27” is calculated The PMT function calculates monthly payments based on the loan amount, interest rate, and length of time it will take to pay back the money. The syntax for the PMT function is PMT (interest rate per period, number of payments, and loan amount ) STEP 3: Use the Goal Seek tool Goal Seek can be used when you know the result of a formula but not the input value required by the formula to decide the resulting reverse calculation. You can change the value of a specified cell until the formula that is dependent on the changed cell returns the result you want.
Private Sub WorksheetChange CheckGoalSeek End Sub Private Sub CheckGoalSeek With Worksheets('Inverse').Range('E19').GoalSeek Goal:=0ChangingCell:=.Range('B19') End With This is on a worksheet named 'Inverse'. Cell E19 contains the difference between my actual goal (an input variable in C19) and the calculated value (in D19), so the goal is for E19 to be zero.
B19 is the cell to be varied. The value calculator calls a macro function that works fine. If I manually enter the goal seek parameters from the 'Tools' menu, it works fine. But when I edit cell C19 to change the goal, it doesn't get the answer.
It seems to be calculating but it doesn't find the goal.