I have never used solver in a Spreadsheet.
I have read the "Help - Solver", but don't understand what cells and what conditions it needs me to apply.
I know the series of formulae to get to the answer.
I can manually try different start values (the variable) to get close to the answer I want. And by trial and error hit the target answer I want.
But how do I use solver to go backwards?
i..e from the answer and the formulae, how do I get back to the trial starting value?
Is there a simple example version using just one formula that goes from the target answer to determine the variable's value?
Is there a you tube explanation, or some similar "hand-holding"?
I have read the "Help - Solver", but don't understand what cells and what conditions it needs me to apply.
I know the series of formulae to get to the answer.
I can manually try different start values (the variable) to get close to the answer I want. And by trial and error hit the target answer I want.
But how do I use solver to go backwards?
i..e from the answer and the formulae, how do I get back to the trial starting value?
Is there a simple example version using just one formula that goes from the target answer to determine the variable's value?
Is there a you tube explanation, or some similar "hand-holding"?
Andrew,
I know of no way to find the starting value. Using solver is something of a one-way function. There are quite a number of youtube videos out there, including some of mine, https://youtu.be/1HtzR73-PuI, https://youtu.be/usu7dJphBvE, and https://youtu.be/sHr3NC0ds-o.
No guarantee that the way I use and setup for solver will be of use for your problem.
Anyway, I'd be happy to help.
Regards,
Paul
I know of no way to find the starting value. Using solver is something of a one-way function. There are quite a number of youtube videos out there, including some of mine, https://youtu.be/1HtzR73-PuI, https://youtu.be/usu7dJphBvE, and https://youtu.be/sHr3NC0ds-o.
No guarantee that the way I use and setup for solver will be of use for your problem.
Anyway, I'd be happy to help.
Regards,
Paul
I have had a look at your videos and a couple of others. Thank you for the links.
I think I know how to get started.
I'll do a one line version first and get that working. Wish me luck.
Then I want to extend that to repeat the solve for 24 lines, using the same equation but aiming for 24 different answers.
I think I know how to get started.
I'll do a one line version first and get that working. Wish me luck.
Then I want to extend that to repeat the solve for 24 lines, using the same equation but aiming for 24 different answers.
I use Solver quite a bit -- my Office version is 10 -- bear with me for a minute.
I grab the data off a data sheet -- a VI curve for a 12ax7 for instance -- using Engauge and the X and Y data go in columns, starting in about row15 of the spreadsheet. In this case X is Vak, and Y is Iak
Let's guess that you have an equation which you think will work, with a couple of coefficients and constants. For a triode, mu, ex,kg,kp, kvb . Put the values of these coefficients in the cells which are subject to change.
In another column, I use the data computed from the descriptive equation above and downloaded data to calculate an estimated Iak(e). A fourth column includes the error term, Iak- Iak(e).
Lots of ways to look at the error term, sum of the squared errors, sum log squashed error, absolute percentage error etc. Set up a cell with the sum of the errors to your particular flavor.
The summed error term is the item you want to minimize in your optimization.
I graph the data and estimates and step through the iterative process to see how the variables act to optimize the result. If the process goes haywire, and it often does, you can terminate and go back to the original values.
You can also require the optimization to set minima and maxima for the calculated coefficients. Thusly, if the RCA Handbook says it's a high mu tube, set mu within a range seeming reasonable from the datasheet.
I'll have to do another youtube on this.
You can call me if you want to do this, or we could facetime it on the Iphone.
I must say that I spend too much time on this crud and would prefer to be soldering.
I grab the data off a data sheet -- a VI curve for a 12ax7 for instance -- using Engauge and the X and Y data go in columns, starting in about row15 of the spreadsheet. In this case X is Vak, and Y is Iak
Let's guess that you have an equation which you think will work, with a couple of coefficients and constants. For a triode, mu, ex,kg,kp, kvb . Put the values of these coefficients in the cells which are subject to change.
In another column, I use the data computed from the descriptive equation above and downloaded data to calculate an estimated Iak(e). A fourth column includes the error term, Iak- Iak(e).
Lots of ways to look at the error term, sum of the squared errors, sum log squashed error, absolute percentage error etc. Set up a cell with the sum of the errors to your particular flavor.
The summed error term is the item you want to minimize in your optimization.
I graph the data and estimates and step through the iterative process to see how the variables act to optimize the result. If the process goes haywire, and it often does, you can terminate and go back to the original values.
You can also require the optimization to set minima and maxima for the calculated coefficients. Thusly, if the RCA Handbook says it's a high mu tube, set mu within a range seeming reasonable from the datasheet.
I'll have to do another youtube on this.
You can call me if you want to do this, or we could facetime it on the Iphone.
I must say that I spend too much time on this crud and would prefer to be soldering.
Last edited:
I can get the formula to run using "goal seek" and it gives the correct resistance value to allow the result to hit the target value.
But when I use solver it always comes back saying no solution,
not a linear equation.
If I specify the range to include the fixed values used in the equation, solver runs but sets the fixed values to new nonsense values, usually zeros.
But when I use solver it always comes back saying no solution,
not a linear equation.
If I specify the range to include the fixed values used in the equation, solver runs but sets the fixed values to new nonsense values, usually zeros.
Want to provide a screen shot of the equation, or PM it to me?
Sum of log squashed errors seems to work well:
=LN(1+ABS(P16-O16))
- Status
- This old topic is closed. If you want to reopen this topic, contact a moderator using the "Report Post" button.