You can use Goal Seek to find the solution. You might want to know how many books are sold at the original price to obtain a total revenue of 24,500. Later, the bookstore announced a 10% discount on that book and cleared off the stock. The original price of the book is 250 and certain number of books was sold at that price. Suppose there is a bookstore that has 100 books in storage. Let us understand this with the help of an example. You can solve story problems easily with Goal Seek. Goal Seek produces a result, as shown below −Īs you can observe, Goal Seek found the solution using cell C6 (containing the formula) as 12% that is displayed in the cell C2, which is the interest rate. Step 5 − This cell that Goal Seek changes, must be referenced by the formula in the cell that you specified in the Set cell box. It is cell C2, which you named as Interest_Rate. This box has the reference of the cell that contains the value you want to adjust, in this case the interest rate. Step 4 − Type Interest_Rate in the By changing cell box. The number is negative because it represents a payment.
Here, you get the formula result, in this case, the EMI that you want to pay. Step 3 − Type -50000 in the To value box. It is the cell C6, which you named as EMI. This box is the reference for the cell that contains the formula that you want to resolve, in this case the PMT function. Step 1 − Go to DATA > What If Analysis > Goal Seek on the Ribbon. Perform the Analysis with Goal Seek as follows − The table now looks like −Īs the Interest_Rate cell is empty, Excel takes that value as 0 and calculates the EMI. The PMT function is placed in the cell EMI so that it can be used by Goal Seek.Įxcel computes the EMI with the PMT function. Goal Seek requires a formula to find the result. Further, though you know the EMI that you can pay (50000), that value is not included as you have to use the Excel PMT function to arrive at it. The cell Interest_Rate is kept empty, as you have to retrieve that value. Step 2 − Enter the values in column C corresponding to column D. Step 1 − Set up the Excel cells for Goal Seek as given below. You can use Goal Seek to find the interest rate at which you can borrow the loan as follows − You want to know at what interest rate you can borrow the loan. Suppose you want to take a loan of 5,000,000 and you want to repay in 25 years. Refer to the chapter – Optimization with Excel Solver in this tutorial. If you have more than one input value to be determined, you have to use the Solver add-in. Goal Seek works only with one variable input value. Then, by varying the input value in the formula, Goal Seek tries to arrive at a solution for the input value. Goal Seek requires a formula that uses the input value to give result in the target value.
Try to learn for your own with more difficult examples.Goal Seek is a What-If Analysis tool that helps you to find the input value that results in a target value that you want. That how many pieces you can afford.Īs you see, Goal Seek is a very easy way to calculate results. In this example the result is correct so click OK. If not, click Cancel and nothing will change in your table. In this example it is a C3 cell.Īfter clicking OK, a new dialog box appears. By changing cell field, you should choose cell with count of pieces. In the Value field, enter the planned value of 1000000. In this example this is a value of $1,000,000. Next click Goal Seek from the list.Įxcel marked cell which the result is set as a result of the planned analysis. Go to Data tab in the Ribbon and select What-If Analysis button. In the above example it is a cell with your budget. How many pieces you can afford? Select the cell with the formula (that calculates the result) to be set by Excel for the expected value. In this example, price is $230 and your budget is $1 000 000. You want to check, how many pieces you can buy. In the example, you know price and budget. To seek the goal, you need a data (as in the example below). The result is based on the formula from one of the cells in a worksheet. Goal Seek lets you find the right value for the expected result.
#GOAL SEEK ON EXCEL FOR MAC HOW TO#
This is a excellent way to find whole data when you know the result and you know how to calculate it. In this lesson you can learn how to use Goal Seek tool in Excel.
0 kommentar(er)
