Using Goal Seek and Solver in MS Excel
(Page 1 of 4 )
If you stumbled into this article in the hope of learning what “goal seek” and “Solver” are in relation to MS Excel applications, then you've come to the right place. First we will define their use. Goal seek is used when you know the result (it could be an answer, or any numerical value) but you are not sure what combinations will arrive at it. It lets you analyze only one variable at a time. Like goal seek, you use Solver when you know the answer, but it lets you analyze several factors at once to arrive at your desired result.
For an example using goal seek, say you have a budget of $1000 per month on a certain project. And your budget is being governed by a specific formula, say Budget=5x + y (I am just making up this example to relate it to reality). Say x is the food budget and y is the expenses for transportation.
Thus you might want to compute the expenses for transportation, given the total food expenses to be $100. With problems like this, you need goal seek for faster computation and to avoid algebraic manipulation of the variables, which can be very time consuming to do on an Excel worksheet.
Now suppose you do not know the food and transportation expenses, but you are given the following constraints: food expenses should not exceed $400, yet not be less than $100, while transportation expenses should not exceed $600 yet not be less than $200. Given these criteria, you are asked to solve for the optimal food and travel expenses, given that the overall budget does not exceed $1000.
This type of problem can be solved by the MS Excel Solver add-on. Note that Solver is an add-on, and should be installed to be used, unlike goal seek which is a built-in function of Excel.
If you do not have Solver installed under the Tools section of your MS Excel, try reading this Microsoft tutorial on Installing and Running Solver.
I ill give you real examples of how to apply goal seek in MS Excel. This tutorial is tested to work starting with MS Excel 2002. MS Excel might be evolving; I have seen many advanced features, particularly in Excel 2007. But this tutorial is simple, and the basic principles are still covered.
Next: Using Goal Seek >>
More BrainDump Articles
More By Codex-M