Goal Seek is used when you know what answer you want, but don't know the exact figure to input for that answer. For example, you're quite certain that 8 multiplied by something equals 56. You just not sure what that missing number is. Is it 8 multiplied by 6? Or Is it 8 multiplied by 7? Goal Seek will tell you the answer.
We'll test that example out right now. So start a new spreadsheet, and create one the same as in the image below:
Before you can use Goal Seek, Excel needs certain things from you. First it needs some sort of formula to work with. In the image above we have the simple formula =B1 * B2. We've put this in cell B3. But the answer is wrong for us. We had a Goal of 56 (8 times something). We want to know which number you have to multiply 8 by in order to get the answer 56. We tried 8 times 6, and that gave the answer of 48. So we have to try again.
Instead of us puzzling the answer out, we can let Goal Seek handle it. So do the following:
- From the Excel menu bar, click on Tools
- From the drop down menu, click on Goal Seek
- A dialogue box pops up like the one below
The dialogue box needs a little explaining. "Set cell" is the answer you're looking for, this is the Goal. Set cell needs a formula or function to work with. Our formula is in cell B3, so if your "Set cell" text box does not say B3, click inside it and type B3.
"To Value" is the actual answer you're looking for. With "Set cell", you're just telling Excel where the formula is. With "To Value" you have to tell Excel what answer you're looking for. We wanted an answer of 56 for our formula. So click inside the "To Value" text box and type 56.
"By Changing Cell" is the missing bit. This is the part of the formula that needs to change in order to get the answer you want. In our formula we have an 8 and a 6. Clearly, the 6 is the number that has to go. So the cell that needs to change is B2. So go ahead and enter B2 in the "By Changing Cell" text box. Your dialogue box should now look like this:
Click OK when your dialogue box looks like the one above. Excel will then Set the cell B3 to the Value of 56, and change the figure in cell B2. You'll also get a dialogue box like the one below:
Click OK on the dialogue box. Your new spreadsheet will look like this one:
So Goal Seek has given us the answer we wanted: it is 7 that when times by 8 equals 56.
Download Visual Basic Project work like this Goal Seek
Please sagest me for more improvement in visual basic goal seek program
No comments:
Post a Comment