Using Goal Seek and Solver in MS Excel - Using Goal Seek
(Page 2 of 4 )
FIRST STEP: Goal seek requires one variable to be unknown in a set of equations. Remember our example on the previous page:
Budget = 5x + y
If the budget is known ($1000), and the food cost is known ($100), we can solve for the transportation cost.
Therefore the first step in a goal seek process is to define your equations or relationships between the variables.
SECOND STEP: The next step after defining the equations is to define the given part of the equations.
THIRD STEP: After defining both the given variables and the equations, open your Excel spreadsheet. Define the equations in one cell ONLY. Note that defining the equations in Excel differs a lot from the mathematical world. For example, the above equation can be written as:
Budget = 5*x + y
The multiplication sign should be written as “*”. Other complicated mathematical expressions such as:
can be expressed as Z= (5*x*y*4*log((5^x), 10)/(y)
To implement our example in the spreadsheet, copy and paste this formula in cell A1:
=5*A2+A3
With this formula, cell A2 will be used for “Food expenses” while cell A3 will be used for “Travel Expenses.” Shade A2 and A3 with yellow so that you will know they are variables.
If you are asked to solve for the travel expenses given that the food expenses equal $100 and the total monthly budget is $1000, place $100 in cell A2, then go to “Tools” and click on “Goal Seek.” Enter the following data:
Set Cell: $A$1
To value: 1000
By Changing Cell: $A$3
Then click OK. After that, Excel analyzes the given conditions, and then goal seek returns a result like the one below:
If you have some formulas yourself, try solving them using goal seek. It is a much faster approach than any other methods.
Next: Using Solver >>
More BrainDump Articles
More By Codex-M
