Formulas and Functions give you answers based upon input. If you know what a formula’s answer should be, but can’t derive the input value, Goal Seek is the tool for you.
I have a simple formula from my last post that takes one input, a Date in “m/d/yyyy” format, and returns the Day of the Year. This will tell me that Valentine’s Day — 14 Feb — is the 45th day of the year, or my birthday is day 311 this year.
The quickest way to find out what Date the 250th day of the year falls on is to use Goal Seek. For this example, here are the steps to take.
- Choose Data > What-If-Analysis > Goal Seek…
- In Excel for Mac and Excel 2003 choose Tools > Goal Seek…
- Click the Set cell box and select cell B2, which has the formula =A2-DATE(YEAR(A2),1,0)
- Click the To value box and type in 250, which is the outcome I want
- Click the By changing cell box and select cell A2, the value that Goal Seek will change
- Click OK
Goal Seek searches for a solution and displays the following results.
Click OK to accept the changes, or click Cancel to restore the original value and leave the spreadsheet unchanged.
Note: Goal Seek works only with one variable input value.
Check out Goal Seek next time you know what a formula should be, but don’t have the time, inclination or smarts to figure it out.