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.
Table of contents
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%).
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.
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!
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.