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
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,
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
Click here to
return to the Excel index.