Multiple Equation Solver in Excel? 1

[dbengtson]

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

 

[yakpol]

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:

= 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.

 

[Denial]

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

 

[Onda]

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

 

[asixth]

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

 

[cowski]

how do we invert matrices using microsoft excel?

With the 'minverse' function and the 'mmult' function. Just remember to use 'ctrl' + 'shift' + 'enter' to enter an array formula.

See the attached file using Onda's suggestion.

 

[electricpete]

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.

 

[electricpete]

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.

 

[dbengtson]

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/getfil...-7e26-4fdf-9b4f-cb67c561b23a&file=Scratch.xls

Dave

 

[electricpete]

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.

 

[electricpete]

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.

 

[dbengtson]

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

 

[asixth]

cowski,

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

 

[electricpete]

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.

 

[electricpete]

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.

 

[electricpete]

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.

 

[electricpete]

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.

 

[electricpete]

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.

 

[electricpete]

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.

 

[electricpete]

And the file to go with my previous message is attached to this message.

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