# Thread: Goal seek many cells! (Excel XP)

1. ## Goal seek many cells! (Excel XP)

If I have a student with Term 1 through to Term 3 results...I can use Goal Seek to work out what the student must achieve in Term 4 to get to an aggregate of 65% for the year!

How can I automate this process if I have 300 students? (Is the only option I have a macro to loop through the students and goal seek one at a time??, or is there another way to quickly do this task??? (Formula maybe?))

2. ## Re: Goal seek many cells! (Excel XP)

It can probably be done with a formula. How is the end result calculated from the term 1 through 4 results, and what does 65% aggregate mean?

3. ## Re: Goal seek many cells! (Excel XP)

Student 1 : Term1 =76, Term2 = 69, Term3 = 71, Term4 = ?, : Aggregate = " =(Term1+Term2+Term3+Term4)/4
Student 2 : .......
........

I need to find Term4's value so that each Aggregate calculates to 65! (For many rows down!)

4. ## Re: Goal seek many cells! (Excel XP)

Aggregate = 65 means
(Term1+Term2+Term3+Term4)/4 = 65
So
Term1+Term2+Term3+Term4 = 4*65 = 260
Hence
Term4 = 260-Term1-Term2-Term3

5. ## Re: Goal seek many cells! (Excel XP)

Rudi,

You can use the Solver Addin (included with Excel, but not necessarily installed) to use more than one "goal". The trick is, of course, to define an objective function. This should be a combination of the individual ones (sum=0, or sum of the squares=0, etc).
Alternatively you could use a simple macro to run goalseek.

Regards, Teun

6. ## Re: Goal seek many cells! (Excel XP)

Rudi,

In addition to my previous post:

Another way is to leave the objective function blank (not zero or anything) and to set the required results in the constraints.

Regards, Teunis

7. ## Re: Goal seek many cells! (Excel XP)

<img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>
Sorry for wasting your time!!

8. ## Re: Goal seek many cells! (Excel XP)

Rudi,

The constraints can be entered as a range! e.g. you don't have to enter them individually in the Solver.

Regards, Teun

9. ## Re: Goal seek many cells! (Excel XP)

I've never tried this before...to leave the objective function blank!!!

PS: AFAIK, solver can also solve for only one cell....but by updating multiple cells with the addition of constraintes!!! Are you suggesting that solver be used to solve for all the 4th terms with a constraint?
I think i'm <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

Thx for the suggestions. Hans has really <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>'ed me with a simple formula....but if you like, you could explain in more detail what you mean by your statement...Another way is to leave the objective function blank (not zero or anything) and to set the required results in the constraints.?/

Tx

10. ## Re: Goal seek many cells! (Excel XP)

Rudi,

See attachment. Now is the sum of the score up to now. Required - required score, 65 or what. The constraint is the "Now" must be equal to "Required", by changing the score for term T4.

Hope you understand it and can adapt it to your situation! In general this is the way for more complicated Solve problems to get a feasibile starting solution.

Regards, Teun

11. ## Re: Goal seek many cells! (Excel XP)

Teunis...this is brilliant!!!

You have shown me a whole new operation for Solver!!!
Great Guns!!!!
Tx

12. ## Re: Goal seek many cells! (Excel XP)

Teun,

I found your spreadsheet example very interesting and useful. I've been able to use the concept successfully in my application, but only in manual mode. If I record a macro applying your technique, and then attempt to run the macro I get the following error message "Solver: An unexpected internal error occurred, or available memory was exhausted". After this error, I can still run successfully manually. Any thoughts on what might be the problem?

13. ## Re: Goal seek many cells! (Excel XP)

MartyO's question is a re-post of the one in the thread Odd Solver Behavior (Excel 2003). Please post all replies there; this thread is locked to avoid duplication and confusion.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•