Excel 2007 : Iteration with 2 changing variables

Hello everyone!

I was wondering if this can be done in excel with some formula. Here is my problem:

I have one value, say X, which changes by some formula and I have another value, say Y, which I need to match to X via some formula but when 2 other variables are changing. To simplify a little bit:

1) X - the value that I have, which changes by some formula

2) a - first variable, which is any number in the range of 0 and 3.5 for example
b - second variable, which is any number in the range of 0 and 20

3) c - a random value, which =a+b for example
d - also some value which =(a+b)/2

4) Y - the value i need to be equal to X, but also Y=c+d.

So, i need to find out the values of a & b for which X=Y. The formulas here are just for the sake of the example, not my actual problem.

Can this be done in excel? And ofc how? I mean without using VBA.

Thanks in advance!

DamirDz

Try Solver (Tools menu -> Solver). If it's not there, you may need to install the Solver add-in.

Set up a cell Z that =X-Y.

Run Solver and tell it to set Z equal to a value of 0 by changin a and b. See if Solver finds a solution and see if that solution makes sense.

Well, this is embarrassing...I'm working with Excel2007 now, and I can't seem to find "tools". Is it maybe "commands"? Can you help?

If the solver isn't there where can I find it to install?

P.S.
Any chance to do this via some formula?

Thanks!

DamirDz

Well, this is embarrassing...I'm working with Excel2007 now, and I can't seem to find "tools". Is it maybe "commands"? Can you help?

I have had very little experience with 2007. In 2002/03, it is on the Tools menu. "alt-t" -> "v" is the key stroke sequence that calls it, and these keystroke sequences have still seemed to be usable in 2007. I don't know how to find solver on the "ribbon."

If the solver isn't there where can I find it to install?

In '02, Solver is not included in the "typical" or "default" installation options. If you wanted Solver installed, you had to go through the "custon" install option, and specifically tell the installer to install solver. I don't know how they've done it 2007. If you no longer have access to your original "installer package", you might check on Microsoft Office's website to see if it can be downloaded from there.

Any chance to do this via some formula?

Yes, there is a chance this could be done with a formula. You haven't given us any specifics, but, on the surface, your problem sounds like you are trying to solve a system of two equations with two unknowns (put "solving systems of equations" into your favorite search engine and you should find help). If this is indeed the case, one could use those techniques learned in math classes to derive formulas for a and b.

I will look into the solver thing.

Yes, there is a chance this could be done with a formula. You haven't given us any specifics, but, on the surface, your problem sounds like you are trying to solve a system of two equations with two unknowns (put "solving systems of equations" into your favorite search engine and you should find help). If this is indeed the case, one could use those techniques learned in math classes to derive formulas for a and b.

This isn't only a "system of equation" problem I think, because the a i b values have a specified range. I might be wrong.
What do you mean by specifics? I have posted a similar problem to mine actual, just simpler...

Thanks!

DamirDz

This isn't only a "system of equation" problem I think, because the a i b values have a specified range. I might be wrong.

Depends on what's really going on. Based solely on the sample, those "constraints" on a and b only mean that, after you've found the desired solution, you need a test to see if the solution falls within those ranges [=IF(and(a>0,a<3.5,b>0,b<20),TRUE,FALSE)]

What do you mean by specifics? I have posted a similar problem to mine actual, just simpler...

The main problem I see with the sample problem is that there isn't one unique solution. With only the information given, I can see that Y=X when b=(X-1.5a)/1.5, then, as noted, subject to the constraints indicated (which means that for some values of X, there won't be a solution that matches those constraints).

So that is the solution of the sample problem, with the following questions:

Is there more information to determine a unique solution, or is this all we are given and we don't care what value a or b has as long as they fit those constraints and solve the X=Y part of the problem?

What to do when there is no solution that falls within the constraints on a,b?

Is the real problem as simple as this sample, or is there something about the real problem that makes this approach unsuitable? My approach has been to use algebra to find the formula for Y. Solve that formula for b. Then set Y equal to X.