Backsolve calculations using Goal Seek in Excel

When problem-solving in Excel, usually, you'll work from front to back by constructing formulas that perform calculations on the available data to find a desired solution. This is referred to as 'forward solving'.

On the other hand, backsolving is a method where you start with the result and work backwards to determine the value(s) leading to that outcome.

Excel has a feature for this — Goal Seek.

In the example, student Shane Smith has completed five out of six modules for his university degree. He is averaging an overall mark of 67.8, putting him on course for a merit classification.

He knows that if he pulls out all the stops in the final module, he can achieve a distinction.

The question is: what module mark does Shane need to meet the minimum threshold for distinction?

By going to Data ＞ What-If Analysis ＞ Goal Seek, the average mark is referenced in 'Set cell' (D9), with a 'To value' of 70, as that's the target value.

'By changing cell' references the empty module cell (B10), as that's the unknown mark that needs to be generated.

After selecting OK, Goal Seek iterates to find a solution and ends up planting 81 in the module cell, bumping the average mark up to 70. Shane now knows exactly what he must achieve.

Do you ever use Goal Seek?

#exceleration #excel #microsoftexcel #excelformulas #exceltips #globalexcelsummit

---

The Global Excel Summit is the world's largest gathering of Microsoft Excel users and experts.

Find us on: