Excel Goal Seek

Hi again Ian

Thanks for clarifying.
Rather than repeating Goal Seek many times for each subject you can use Solver to achieve the same result.

If you are new to Solver first install the Add-In by selecting File, Option, Add-ins.
Click the Go button and tick Solver Add-in.
(You only have to do this once to use Solver)

Solver will now be on the Excel Data tab.

Suppose your data is like this:


---A-------B--------C------D-------E-------F
Subject Courses Delegates Average Target Average
Subject A 26 600 23 25
Subject B 19 380 19 25
Subject C 14 310 22 25
Subject D 30 700 23 25
Subject E 12 300 25 25


Set the Solver parameters as follows:

Set Objective: D2
To: Maximum
Changing cells: B2:B6 (Courses)

Add Constraint:
D2:D6 = E2:E6 (Average = Target)

Click Solve to see the results

Subject Courses Delegates Average Target Average
Subject A 24 600 25 25
Subject B 15 380 25 25
Subject C 12 310 25 25
Subject D 28 700 25 25
Subject E 12 300 25 25

The number of courses are automatically calculated in one go.

Hope this makes sense. It might not at first if you are new to Solver. It is similar to Goal Seek with added constraints and multiple changing cells.

Hope this gives you another approach.

An alternative is to write a macro that repeats the Goal Seek. I can post the code if you are interested in this rather than using Solver.


Regards
Doug
STL