Results 1 to 13 of 13
Thread: Goal seek many cells! (Excel XP)

20051110, 09:25 #1
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
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?))Regards,
Rudi

20051110, 09:31 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
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?

20051110, 09:45 #3
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
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!)Regards,
Rudi

20051110, 10:01 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
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 = 260Term1Term2Term3

20051110, 10:08 #5
 Join Date
 Dec 2001
 Location
 Doorn, Netherlands
 Posts
 311
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20051110, 10:10 #6
 Join Date
 Dec 2001
 Location
 Doorn, Netherlands
 Posts
 311
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20051110, 10:12 #7
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
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!!Regards,
Rudi

20051110, 10:13 #8
 Join Date
 Dec 2001
 Location
 Doorn, Netherlands
 Posts
 311
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20051110, 10:17 #9
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
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.?/
TxRegards,
Rudi

20051110, 10:32 #10
 Join Date
 Dec 2001
 Location
 Doorn, Netherlands
 Posts
 311
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20051110, 10:41 #11
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Goal seek many cells! (Excel XP)
Teunis...this is brilliant!!!
You have shown me a whole new operation for Solver!!!
Great Guns!!!!
TxRegards,
Rudi

20070331, 02:24 #12
 Join Date
 Apr 2006
 Location
 California
 Posts
 69
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20070331, 10:21 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Goal seek many cells! (Excel XP)
MartyO's question is a repost 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.