Goal Seek for multiple cells all at once.

[tamcclanahan]

I am trying to write a macro to goal seek all rows to change column T to be between -0.0131 and 0.0131 by changing column Q to be between 4 & 6. Goal seek works to find the answer but the spreadsheet in 40000 rows and will take forever to do this manually. Please Help!

[tamcclanahan]

See here for example link to spreadsheet

[beyond Excel]

Hello. I ask you because it is not clear to me.
That is to say: What is the objective that must be met in each row?...

[tamcclanahan]

By changing column Q it adjust column T. Need to change all of column Q within the parameters of minimum of 4 and maximum of 6 to make column T fall within -0.0131 and 0.0131

[MrShorty]

I don't think a line by line goal seek is going to solve this, as each line's solution depends on two lines, so that all 13 rows in your example end up being tied together into one problem.

Are you required to write your own macro for this? Would you be allowed to obtain and install 3rd party solvers for this?

The small problem in your example is easily solved using Excel's built in Solver. To set up the Solver model, I:

1) Enter a suitable objective function in a convenient cell. I chose =SUMSQ(T2:T14) in T21
2) Call Solver and tell it to:
2a) Set target Cell: T21
2b) To a minimum
2c) By changing Q2:Q14
2d) Subject to the constraints that T2:T14 must be less than 0.013 and T2:T14 must be greater than -0.013.
3) Tell it to Solve and wait for the solution.

Excel's built in Solver is arbitrarily limited to 100 (or 200, documentation isn't always clear which) decision variables (by changing cells), in order to encourage those with bigger problems to purchase Frontline's more robust Solver package. I have also seen people in this forum suggest opensolver (free and open source). Would you be allowed to purchase and install a robust solver package for this? I think that would be the easiest way to solve this problem.

[beyond Excel]

No... no... no...

There is "something" that is not well defined. For example:

a) We see 4.6768555 in Q2, right?
b) But if it were 5, "T" would also be within the stipulated range.

I ask: Which solution (and why) do you prefer more? 4.6768555 or 5?