Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 29. July 2009 11:24

Today I came across a post which demonstrates how to use Goal Seek to determine how to save for your retirement. Goal Seek is essentially a simplified solver: point Goal Seek at a cell, tell it how much you want it to be and what cells it can tinker with, and Goal Seek will try to find values that reach that goal. The post is an excellent illustration of what’s great about it: it’s super easy to use, and very practical.

However, there is no such thing as a perfect tool, and Goal Seek can fail miserably at finding the optimal answer to very simple problems. After reading this, I thought it would be a good public service to illustrate what its shortcomings are, especially if you are going to trust it for questions as important as your retirement!

For our illustration, we will use the following setup.

Setup

Now let’s say we want to find a value such that B2 = 250. Following Pointy Haired Dilbert, let’s use Goal Seek:

GoalSeek

Put in a value of 1 in cell B1, and run Goal Seek - here is what happens:

GoalSeekFail

Goal Seek fails to find a value in B1 such that B2 = 250. Complete failure.

More...

Comments

Comment RSS