×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Multiple Equation Solver in Excel?

Multiple Equation Solver in Excel?

Multiple Equation Solver in Excel?

(OP)
Hi:

I'm trying to set up an Excel worksheet that solves multiple equations for unknowns. For example I'd like to have equations for power and voltage drop across a resistor, and have Excel solve for Voltage, resistance and current, given enough variables input. I can do this on my HP49, using the multiple equation solver. Solver doesn't seem to do what I want. Any suggestions?

Regards

Dave
 

RE: Multiple Equation Solver in Excel?

Dave,
Solver is capable of solving system of equations, I use it all the time.
Having equations f1(x,y)=0 and f2(x,y)=0 specify SOLVER target to the cell containing formula:

CODE

= abs(f1(x,y))+ abs(f1(x,y))
and set SOLVER to minimize it or to zero value.

Don't promise very robust performance, having good initial guesses helps.
 

RE: Multiple Equation Solver in Excel?

Yakpol's approach will work.  I have used it many times.
Note the warning about needing a good starting guess.
 

RE: Multiple Equation Solver in Excel?

If your system is of linear equation you could resolve it in matrices form.

as an example :
1x + 3y - 1z=8
3x - 1y - 2z=2
0x - 2y + 1z=10

write the matrices:
     |1  3 -1|     |8 |
 A=  |3 -1 -2|   B=|2 |
     |0 -2 +1|     |10|
reverse the matrices   (calculate A^-1) and multiply A^-1*B.
the vector result will be x,y,z

Onda
 

RE: Multiple Equation Solver in Excel?

Thats a very good suggestion Onda, but how do we invert matrices using microsoft excel?

 

RE: Multiple Equation Solver in Excel?

Attached I have demonstrated 6 different ways to solve simultaneous equations in excel.  Each in a different tab:

Solver With Constraints
Solver With Residuals
Matrix
Iteration Without Replacement
Iteration With Replacement
Circular Refernce.

Notes
1 - Matrix works only for linear systems. I believe most of the others can be adapted for non-linear systems.
2 - Iteration without replacement has convergence problems for some problems.  These convergence problems generally go away using iteration with replacement or circular refernece approaches.
 

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Multiple Equation Solver in Excel?

That is an old spreadsheet - I haven't worked with it for awhile.

One thing I just remembered is that named variables a11, a22 etc used in the iteration without replacement tab and circular reference tab are based on cells in the iteration without replacement tab.  So if you use this particular spreadsheet and alter the values. Make sure that those last three tabs all have the same constants or else you might be solving a different problem than you think you're solving.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Multiple Equation Solver in Excel?

(OP)
Hi All:

yakpol had the closest description of what I'm trying to accomplish, but it didn't work very well. You were right that the initial values need to be pretty close, and that's sort of a deal breaker for me. I guess I'll stick with my Calculator for that. I've attached my spreadsheet if you are interested.
http://files.engineering.com/getfile.aspx?folder=0e81236a-7e26-4fdf-9b4f-cb67c561b23a&file=Scratch.xls

Dave

RE: Multiple Equation Solver in Excel?

I think you'll do better if you minimize the sum of the squares instead of just the sum of the absolutes.  

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Multiple Equation Solver in Excel?

I looked at your spreadsheet and I have to scratch my head to figure out what the heck you are trying to do.

It looks like R is a constant (right?.... not part of the items you allowed solver to change).

V is an input.

From V and R, you calculate Power =V^2*R and we convert power to several other formats (watts, db-watts, milliwatts, db-milliwatts - strange units).

The solver solution just put V as low as you let it.  From there all your different types of power appear.

Doesn't seem like a very useful excercize to me.

You have only one input variable V.  You could solve any of those other equations for V easily if you have some target power in your preferred units.

This is not what I would call simultaneous equations if I'm understanding it right.

I would be interested to hear the problem description.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Multiple Equation Solver in Excel?

(OP)
I'm trying to convert between dBm, (dB relative to milliwatts), dBW(dB relative to 1 Watt), Watts, milliWatts, and Voltage across 50 Ohms. I'd like to be able to enter one of those input numbers and have Excel calculate the rest. I do have R set as a constant.
The equations are all simple, but this is a simple example. I could certainly do this directly with equations, but when there are three or four interdependent equations, it's difficult to do this analytically.

Dave

 

RE: Multiple Equation Solver in Excel?

cowski,

great post, matrix operations will really increase the applications I will be able to use excel for.

RE: Multiple Equation Solver in Excel?

Dave - Thanks for the explanation.  I can see better what you are trying to accomplish.

Here is one problem - you can't solve for EVERYTHING.  You have to take at least one of those variables (either voltage or one of the power measures) and hold it constant.  Otherwise, think about what you are solving for... nothing!

Here is an excercize that may or may not be useful to what you are trying to accomplish.

Find conversion to watts and from watts for all your other parameters (except constant R).   The error term for each variable should be the error between it's value and it's value calculated form watts.  The watts error equation should be watts value minus sum of square of watts calculated from each of the other parameters.

Then when you run solver, you need to leave one of those parameters constant/unchanging (either watts or DBW, or mw, or dbmw, or V50).

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Multiple Equation Solver in Excel?

Quote:

The watts error equation should be watts value minus sum of square of watts calculated from each of the other parameters.
Actually, I don't think you would need that because it would be be redundant with the other error terms.  And if you leave it out, that eliminates the need to solve watts from the other parameters... just find the other parameters from watts.

If I get a chance I will post a spreadsheet to do what I described.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Multiple Equation Solver in Excel?

And it may not be needed to express everything from watts.  May be ok the way you did as long as you exclude one of those inputs from the set of variables to change.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Multiple Equation Solver in Excel?

Here is a spreadsheet that does things the way I described above.

I also wrote a vba macro to handle selecting which cells will be varied.

To run it:

Pick one item to hold constant and enter your value in corresponding cell.

Push the button and the program will ask you which cell to hold constant (select the cell and press enter in response).

Seems to work pretty well. Some cases end up with fairly largge residual (error).  If you run the solver again using exact same input but the final values from previous as your new initial values, the results tend to get better (may have to repeat several times).

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Multiple Equation Solver in Excel?

Attached I have cleaned up the file/program to make it a little more understandable.

Note when you open the file it asks it should be opened as read only.  I say "no".   Why does it ask that? Has something to do with the way the original file was set up.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Multiple Equation Solver in Excel?

Attached is the same file with a few more improvements:

1 - Set it up so that the current active cell is the default cell to hold constant, and so that the selected cell to hold constant is left as the active cell.  The result of this is that it will be easier to run the simulation multiple times.  Just keep pressing the button (don't need to change the cell).

2 - Provided weighting factors.  If you see one of the errors is higher than you'd like, you can increase the residual to pull that parameter into line.

3 - Added a formula/error for watts. It is redundant with the others but I added it anyway.

The approach to run would be to watch the residuals and keep hitting the button until they get very low.  If they don't seem to be converging, then adjust the weighting factors on the one that seems way out of line.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Multiple Equation Solver in Excel?

Yet another change.  The interface has been re-organized a little so you can keep pressing the button rapidly without telling it where to go or saying OK.

The instructions 1, 2, 3 at the top in blue should be self-explanatory.

It seems the following approach works pretty darned good:
A    Run the program initially with all weights to 1
B    Keep pressing the button until the Sum of Square of weighted errors stops going down
C    Pick the item with the highest fractrional error (column J) and set its weight to 10
D    Keep pressing the button until the Sum of Square of weighted errors stops going down
E    If fractional error is still not good, set the weight a factor of 10 higher and repeat, and repeat...
F    If fractional error is OK, then reset the weight to 1, and go to step C and find the new worst actor

I had originally envisioned that we would use our intuitiion to guess the right weighting factor (smaller items get weighted higher).  That doesn't seem to work. I think the solution is relatively more sensitive to the initial conditions than to the weighting facotrs (within bounds).  So by attacking the worst actors one at a time (by setting their weighting factors high), we seem to nudge in on the correct solution pretty well. Even when we set the worst actor weight back to 1 and go after the new worst actor, it seems to remember the history of where it's been.

Solver is a little weird as we have discussed before.  I'm not sure what makes it tick.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Multiple Equation Solver in Excel?

dbengtson,

You don't have a true system of equations in the file you posted. Rearanging your equations I came up with these assignments: (D8 is constant)
D5=(D6^2)/D8
D3=1000*D5
D2=10*LOG10(D3)
D4=10LOG10(D5)

D2,D3,D4 and D5 depend on D6, there's nothing to solve.

RE: Multiple Equation Solver in Excel?

Yakpol - That is exactly the point I made 30 Aug 08 1:01: "From V and R, you calculate Power =V^2*R and we convert power to several other formats (watts, db-watts, milliwatts, db-milliwatts - strange units)....You have only one input variable V.  You could solve any of those other equations for V easily if you have some target power in your preferred units.  This is not what I would call simultaneous equations if I'm understanding it right. "

The response was already given 30 Aug 08 7:27:  "I'm trying to convert between dBm, (dB relative to milliwatts), dBW(dB relative to 1 Watt), Watts, milliWatts, and Voltage across 50 Ohms. I'd like to be able to enter one of those input numbers and have Excel calculate the rest. I do have R set as a constant.  The equations are all simple, but this is a simple example. I could certainly do this directly with equations, but when there are three or four interdependent equations, it's difficult to do this analytically."

Note that Voltage ((in cell D6) was not intended to be an input (even though it was programmed that way in the spreadsheet).  He wants to convert between 5 different ways to express a power (voltage accross a known resistance is just one more way to express a power) with ANY ONE of them as an input (not just voltage).

Yes there are simpler ways to do it.   To input any of the 5 powers and calculate the others could be done with 5^4 equations (or lesser number with a little bit of if/then logic).  

I believe the approach might be useful for a complex problem where each output (=solver changing variable) is a function of the other outputs and constants.  In most cases where we are not just doing a unit-conversion type excercize, we would want to remove from the solver inputs any "duplicate" variable (one that easily be solved from the other inputs) in order to simplify the solver task.  

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Multiple Equation Solver in Excel?

Sorry - I did not mean to jump in the middle of a question for the original poster. dbengston - please feel to respond to the question or clarify if I have not described your problem correctly

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Multiple Equation Solver in Excel?

(OP)
Hi All:

electricpete: You've described the situation pretty correctly. This is an example of a pretty generic engineering problem, where you have a set of equations that describe a system, and you know some of the parameters, and want to calculate what you don't know. The example I came up with was a simple power conversion, mainly because the equations are simple. However, you could do the same thing with a 2nd order PLL design, and have pretty complicated equations that aren't tractable analytically.

As I read through Trade journals and books, I tend to 1-write down useful equations and 2-put them into my calculator for further use. This is currently pretty easy, and I'd like to have a simple way to use Excel, as it's easier to use a real keyboard.

I haven't taken a look at your latest spreadsheet, I'll try to do that sometime today or tomorrow.

Regards

Dave
 

RE: Multiple Equation Solver in Excel?

(OP)
electricpete:

This is pretty interesting. It looks like it does most of what I'm looking for. I'll have to try putting in some other equations to see how easy it is to adapt to other uses.

Thanks

Dave
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources