Click here for great value web hosting.

ALL ABOUT OFFICE

HOME

ACCESS

EXCEL

WORD

OUTLOOK

POWERPOINT




Search for
 

help


EXCEL

Goal Seek

If you want to set a cell to a certain value by changing only one other cell then Excel can do the calculations for you to find the new value. For instance, you might know how much money you can afford in a loan repayment each month and, knowing this, you might want to calculate the amount you could afford to borrow.

In our example we have some data for the costs of running some vehicles.  The total fuel cost was just over 15,000.  We want to know what cost of diesel would have made the total fuel cost exactly 15,000. To do this we need to change the diesel cost cell, B24, to set the Total Fuel Cost cell, B30, to 15,000.

Click on the Tools drop-down menu and choose Goal Seek, a window appears as shown below.

Click on the red icon at the right hand end of the Set cell box and then click on the cell which contains your desired result, B30 in our case. Now enter the value you want to obtain in the To value box.  Finally, click on the red icon at the right hand end of the By changing cell box and click on the cell that you want Excel to adjust, cell B24 in this case.

When you click OK, Excel will carry out some calculations and should come up with a result as shown below.

You can see that the Diesel cost has changed to 0.85 (the actual value is about 0.8518....).  If you don’t want to keep the found value click Cancel and you will be returned to your original spreadsheet. If you click OK the found value will be kept in your spreadsheet.  If you click OK by accident and do not want to keep the amended spreadsheet, click the Undo icon and your spreadsheet will be returned to the values before you used Goal Seek.

Goal seek can only be used to change one cell to find your solution. If you want to change two cells you will have to use Excel’s Solver add-in.

Click here to return to the Excel index.

webmaster@allaboutoffice.co.uk