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.

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 and using it when budgeting or forecasting? If you prefer live courses and live in South Africa look at the MS Excel training courses available. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.