Results 1 to 13 of 13
  1. #1
    Gold Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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?

  3. #3
    Gold Lounger
    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 = 260-Term1-Term2-Term3

  5. #5
    3 Star Lounger
    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

  6. #6
    3 Star Lounger
    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

  7. #7
    Gold Lounger
    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

  8. #8
    3 Star Lounger
    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

  9. #9
    Gold Lounger
    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.?/

    Tx
    Regards,
    Rudi

  10. #10
    3 Star Lounger
    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

  11. #11
    Gold Lounger
    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!!!!
    Tx
    Regards,
    Rudi

  12. #12
    Star Lounger
    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?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •