Goalseek precision

In MS Excel there is a tool called Goalseek that lets Excel search for a solution you are looking for (if you ever make small changes to one cell to get a desired result in another cell you are doing a goalseek, but manually). You can learn more about Goalseek elsewhere but if you already use it you may want to improve the goalseek precision.

YouTube Improve Goalseek precision

Watch, like, subscribe

Problem when goalseeking small numbers, decimals and percentages

Goalseek users may notice that sometimes Excel says it has found a solution but it is not exactly what you were looking for- close but not close enough. This often happens when working with small numbers, particularly percentages.

Below, we want cell B20 (1) to become 21% by changing cell B3 (2). Notice that when we run the Goalseek (3), Excel says it found a solution of 20.907472% (but we were asking for 21%).

Goalseek precision

In order to get a more precise answer (without manually proceeding from this point), you need to change a setting in Excel. Click on FILE, then OPTIONS.

As per below, choose the Formulas and note that by default in Maximum Change (for goalseek type calculations) is set to 0.001. Change this to 0.000001 and click OK.

Goalseek precision

Now when you run the Goalseek (as shown below) it will precisely get to the desired result of 21%. Look at the Sales in row 7 and compare the 2 images and you will see the difference can be significant!

Goalseek precision

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Slow Spell Check, GoalSeek and Find/ Replace in Excel

If you know the answer but want the input from Excel