what if analysis - goal seek...

[gledister]

I know how to use the goal seek function...but my question is:

Is there any way to apply the goal seek function for more cells(even more than the example given)...example:

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64 align=right>10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64 align=right>0.2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64 align=right>50</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>15</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0.3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>50</TD></TR></TBODY></TABLE>

50 is given by 10/0.2 and the other 50 given by 15/03....what we want to do it to set values in C1 and C2 to value 100 by changing values in A1 and A2....

Any idea?

Thanks in advance.

 

[shg]

Goal Seek will only change a single cell. Solver can do this.

See http://office.microsoft.com/en-us/e...-to-set-quantity-and-pricing-HA001111864.aspx

 

[gledister]

I tried with Solver too...it says that the objective cell must be a single cell...hmmm :-/

 

[shg]

That's true, and it makes sense.

You can instead enter in another cell

=(C1-100)^2 + (C2-100)^2

... and use Solver to minimize that.

I assume this is just a notional example, because your problem can be solved easily in closed form without coupling C1 and C2.

 

[gledister]

thanks anyway.

 

[shg]

If you're frustrated, I'm missing why. Solver does stuff like this routinely; you just have to express your objective function (what you want to minimize, maximize, or set to a specific value) in a single cell. That's not a limitation, it's a conceptual requirement of any solution-finding system.

 

[gledister]

I am not frustrated at all I was just searching for a solution that fits the best with my job concerns.

What exactly I want to do...is not just setting an objective for a single cell but to set a common objective for multiple cells....lets say even thousands of them.

Your help is always appreciated shg

Kind regards.

 

[shg]

...is not just setting an objective for a single cell but to set a common objective for multiple cells....lets say even thousands of them.

Solver works the way it works, so I'll just try to explain why with an example from left field.

Suppose you're designing an optical system. You want a sharp image across an infinite depth of field, zero aberrations, and zero distortion. No optical system can do that, so you create some objective function that combines depth of field, aberrations, and distortion to arrive at a figure of merit. Assuming the optical model were on the worksheet, Solver could optimize the figure of merit by changing the controllable elements of the design.

You just want a bunch of values to all be the same value. So write a simple formula that computes their RMS deviation from that value and minimize that.

But maybe tomorrow it's more important for some of the variables to be closer to the given value than others. Your formula could do that in a way that would be impossible to explain to Solver in any simpler fashion.

Going back to your example, though, if you have a bunch of independent models, you should solve them independently (i.e., via a loop), rather than tell Solver to solve it as single problem.

 

[Sal Paradise]

To add to what shg is saying, it looks like your issue is conceptual, not technical. All the fancy bells and whistles in Excel can't solve your problem if you aren't clear on what the real problem is you're trying to solve.

 

[gledister]

Thanks for your advice guys.

Anyway, I found a multi-goal seeker add-in which resolved my problem properly.

Kind regards.