"How To Excel" Mini-Tutorials
by TheExcelAddict.com
"Helping Average Spreadsheet Users Become Local Spreadsheet Experts"

A Quick Solution With Goal Seek

There are times when you know the result you want from a formula but what you need to determine is the input value that will give you that result. Fortunately Excel has a great tool for this that is simple to use. It's called Goal Seek.

Anytime you need to produce a given result in one formula cell by adjusting the value of another cell, Goal Seek can save you a lot to trial and error.

To demonstrate this, let's use a simple example.

Say you have written two of three exams. On the first exam your mark was 67 and on the second it was 85. You have one more exam to write and you want to know what mark you'll need on your last exam to achieve a final mark of 80. Cell A1=67, cell A2=85, and cell A4 contains the formula =AVERAGE(A1:A3). Currently your average mark is 76 (i.e. the average of 67 and 85). Your final mark will be recorded in cell A3.

  1. From the Tools menu select Goal Seek.
  2. In the Set cell box, enter (or select) the cell that contains the formula that you want to generate a specific result (i.e. in this example A4).
  3. In the To value box, enter the result you want (i.e. in this example 80).
  4. In the By changing cell box, enter (or select) the cell that contains the value you want to adjust (i.e. in this example A3).
  5. You will be presented with a dialog box that allows you to either accept the solution found by Goal Seek and input the value into the By changing cell or Cancel and leave the value unchanged.
Some important points to remember when using Goal Seek:
    The Set cell must be a cell that contains a formula, function or cell reference.
  • The By changing cell must be a number or a blank cell. It cannot contain a formula, function or cell reference
  • The Set cell must be referenced by formula to the By changing cell.

Why not print (CTRL+P) this tip and share it with your friends and associates?

Get more time-saving tips just like this one delivered to you by email every week in my FREE newsletter "Spreadsheet Tips From An Excel Addict". Subscribe at the www.TheExcelAddict.com/Newsletter.htm.
You'll be amazed how much time you'll save just by learning a few of these tips.

Copyright 2003 All Rights Reserved by Francis Hayes (The Excel Addict)

If you came to this page from my Excel Mini-Tutorials page, click here to close this window,
otherwise click here and you'll find lots more time-saving Excel tutorials like this one.