# Multiple Cell Goal Seek all at once

1. ## Multiple Cell Goal Seek all at once

Hello,

I want to Goal Seek set values in column AA35 to AA382. In column Z35 to Z382 are the actual static values I need AA35 to AA382 to reflect. My changing cell is S35 to S382. When I used tthe goal seek function it only allows me to change one cel at a time doing that will take to long. Is there a way I can make these changes all at once with goal seek? Also each cell in column AA35 to AA382 will have a different value.

Thanks,

2. ## Re: Multiple Cell Goal Seek all at once

I'm pretty certain that goal seek will not do that.

Solver might, but I think the version of Solver that comes bundled with Excel is limited to 200 decision variables (by changing cells) or something like that. The Premium version of Solver (see www.solver.com ) can handle larger problems (I am not certain of its limits, but they are published on the website), so that might be worth considering.

Using VBA, one can program Goal Seek and Solver to loop through a series of calculations, I'm not sure how long that would take for the problem at hand.

You have not explained any details about the computation you need performed. I see a lot of "Goal Seek" type problems come across the forum that could be solved much more quickly using some algebra outside of Excel, then program the resulting formula into the spreadsheet. Is this a possibility?

3. ## Re: Multiple Cell Goal Seek all at once

I have used quite a bit of Algerbra to get to this point. inorder to obtain the values in column AA35 to AA382 I made it = y35/T35*10000. column y = Sum(W35: X35)*P\$9+R\$17, W and X very from cell to cell in the column and P and R are locked static values. Column T = S35*P\$9\$. P9 is a static value also.

4. ## Re: Multiple Cell Goal Seek all at once

There is nothing unusual or difficult about the algebra, so I would definitely solve this one algebraically:

(using column letters as variables)
1) AA=Z=Y*1E4/T T is a function of our unknown S that we are trying to find, so solve this for T.
2) T=Y*1E4/Z
3) T=S*P9 Solve this for S
4) S=T/P9
5) Set the two expressions for T equal to each other and solve that expression for S. You should now have a formula to put in column S that will find S at any given Z. (I will leave some of the algebra for you to do).

Unless there is something I am missing.

There are currently 1 users browsing this thread. (0 members and 1 guests)