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

 

[electricpete]

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.

 

[yakpol]

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.

 

[electricpete]

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.

 

[electricpete]

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.

 

[dbengtson]

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

 

[dbengtson]

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