Iteration in excel

[DamirDz]

Dear member,

I would like some help regarding iterations. Is it possible to perform them in excel without using VBA? And if yes, how?

Here's my exact problem:

I have two equations (only as example)

1) (a+Y)*X^2+(Y+b)*X+h=0 (it's a second order polinomial, the a,b,c etc. values a arbitrary for this example, only X and Y are of interest)

2) Y=(b+c*X)/(X-2*h*p)

Now, i need to find the Y value by satisfying the condition of the first equation. Is this possible? And how?

Thanks!

DamirDz

[MrShorty]

This looks like a case where, once you understand the underlying math, it will be easier to put it into Excel.

What you have is a system of 2 equations with 2 unknowns. I can never remember when this concept is usually first introduced, but it seems like it is first introduced during high school algebra (here in the states anyway). My first suggestion might be to put something like "solving systems of equations" into your favorite internet search engine and study the different strategies for solving systems of equations.

Assuming this example is consistent with other problems, you might consider using the method of substitution in this case. Equation 2 gives Y as a function of X, so substitute this expression into Equation 1, then solve Equation 1 for X. Equation 1 might not be solvable explicitly for X (it looks to me like it might end up as a quartic equation), so you may need to resort to a numerical method to get X.

Excel has a couple of tools for this: Goal Seek and Solver. I personally don't use Goal Seek, so I don't know if it will be robust enough for this problem, but Solver will definitely be able to solve it. At this point, you will set up the spreadsheet to calculate Eq. 1 at some "initial guess" for X, then tell Solver to set Eq. 1 = to 0 by changing X. Recognize that, if this really does turn out to be a quartic, there will potentially be 4 solutions, so you will need to decide if you want one specific solution or all 4 or how you will handle this aspect of the problem.

[DamirDz]

MrShorty,

Tnx for your answer. I'm fairly familiar with basic math, even with far more advanced one. But this isn't the issue here. I gave here a pretty trivial example to figure out how to make iterations in excel to work. Because I'm not a very advanced user in excel.
My actual problem turns up to be a highly nonlinear equation in X so it's very very time consuming and iteration is the "cheapest" way. And I need this to work every time some of the known variables are changed. I will try it with the solver option.
1.JPG
2.JPG

I've attached here my two equations, just for reference.

Tnx

DamirDz

[MrShorty]

It's hard to tell what are the knowns and what are the unknowns, but I'm assuming you still have two equations with two unknowns. I'd probably start by rearranging both equations so they are equal to 0 (eq 1 is already =0). That should allow you to set the two equations equal to each other. I'd probably then rearrange that equation to get a single function that is =0. Then use Solver to find the root(s) of that equation.