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
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?
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
Don't promise very robust performance, having good initial guesses helps.
RE: Multiple Equation Solver in Excel?
Note the warning about needing a good starting guess.
RE: Multiple Equation Solver in Excel?
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?
RE: Multiple Equation Solver in Excel?
See the attached file using Onda's suggestion.
RE: Multiple Equation Solver in Excel?
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?
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?
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:/
Dave
RE: Multiple Equation Solver in Excel?
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Multiple Equation Solver in Excel?
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?
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?
great post, matrix operations will really increase the applications I will be able to use excel for.
RE: Multiple Equation Solver in Excel?
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?
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?
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Multiple Equation Solver in Excel?
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?
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?
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?
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Multiple Equation Solver in Excel?
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?
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?
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?
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Multiple Equation Solver in Excel?
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?
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