If you know the answer but want the input from Excel

When you are working with complex calculations you sometimes know what you want the answer to be, but you can’t calculate backwards to determine the answer. So for example, if you know what the take home pay needs to be, but are not sure what the pre tax income needs to be to achieve it, you can get Excel to Goalseek it. This way you build/ use a calculator that logically goes from income to take home pay, but you get Excel to go backwards.

Below the Tax Tables in South Africa. We want to know what the blue input cell (A31) needs to be so that the Take Home Pay (answer cell J31) is R750 000. The red box is the calculations required to determine this.

If you know the answer but want the input from Excel

We can manually type in some numbers into cell A31 and see how close we come (so trial and error) or get Excel to do it for you.

So click on:

  • Data Ribbon, then
  • What If Analysis, then
  • Goalseek (as shown below)

If you know the answer but want the input from Excel

Now follow the dialogue box. Tell it

  • what cell you are working towards (the take home pay),
  • what value do you want it to become (R750 000 in this case), and
  • what cell is Excel allowed to change to achieve this number.

If you know the answer but want the input from Excel

Excel will then run through all the possibilities until it either finds an answer, or tells you that what you want can’t be achieved. As shown below, you need to start on R1 133 262.54 to end up with R750 000 assuming just tax deductions.

If you know the answer but want the input from Excel

This works for any calculation. So if you have a complex spreadsheet that takes an input and turns it into an output, Excel can work backwards so that it will tell you what the input needs to be in order to achieve a desired output.

You can learn more about Goalseek in the What If Analysis Course