Folks,
I have a complicated nonlinear equation in one unknown that has a bunch of constants and coefficients I want to change to see what happens to the solution. So I'd like to set up perhaps a hundred or more iterations, each of which requires a solution of the nonlinear equation. Any suggestions on the easiest way to do this in Excel 2016?
TIA
Can you clarify things a bit:
» By "a hundred or more iterations" do you mean that you need to solve of your nonlinear equation a hundred times, each with a different set of input constants / coefficients?
» Or do you mean that you need a single solution using an iterative process that will require up to a hundred iterations to converge?
» Or both?
If it is either (1) or (3), you could carry out the solution process in VBA through a user defined function.
RE: Solving a non-linear equation a bunch of times
How 'easy' is one equation to solve? I'm guessing you've looked for an analytical solution and not got one (many real world issues are like that). I'd use Newton Raphelson and vba.
In Mathcad, and possibly SMath Studio, you can create solve blocks for solving equations, and these solve blocks can then be assigned to function definitions ala f(a,b,c):=find(x), where a,b,c are parameters you wish to iterate. There are a number of programming languages such as VBA, Python, R (possibly) that can be used to write a program to do the same.
Thanks guys! The only tool I have to work with is Excel 2016.
Take an equation that looks like A + Bxg + Cxh=0
where
A, B, C, g, and h are real numbers.
I want to solve the equation for x for n values of C thus providing n solutions for x.
The solution would go like this:
1. Set A, B, g, and h. These are constants.
2. Set i=1
3. Set C=Ci
4. Solve A + Bxig + Cixih=0 for xi using Excel's Goal Seeker
5. Record xi
6. Set i=i+1
7. Set Ci=Ci-1+ ΔC
8. If i≤n go to 4.
I hope this makes it clear. From what I've done so far Goal Seeker works fine. I suspect VBA will be needed. If some kind soul could show the VBA macro for this I'd be really grateful! I simply can't afford to buy MathCAD, Matlab, or any other math program at this time.
Tunalover - I think the spreadsheet at the link below does exactly what you want using just Excel and VBA; i.e. you can set up a table with each row having the required input to be solved using Goalseek, then rather than selecting the Goalseek inputs for each row individually the macro will solve all the rows in the table in a single operation.
Guys I installed SMath and couldn't figure out how to do what I want to do:
Some guidance is appreciated. I'm publishing a journal article and a 3D graph would be a really good, eye-catching way to bring home an important point to the readers. Does SMath do multi-variable graphs?
ElectroMechanical Product Development
(Electronics Packaging)
UMD 1984
UCF 1993
RE: Solving a non-linear equation a bunch of times
If you already have the roots (based on previous replies you have been given a few options to find them for all the combinations of alpha and epsilon), then whats wrong with simply using a 3D surface chart from excel?
Arrange the data in a table with alpha horizontally, epsilon vertically, select all the data range for the roots without the labels. Then select insert recommended chart, find the 3D surface chart under all charts tab and add this. Right click on the chart and select 'select data', under Horizontal (category) axis label, select edit and set it to one of the the 0.05 through to 0.95 alpha range labels at the top of your table for the labels.
You will see a group of series, these are each of the rows in your table of roots of T, edit each series and for the series title make it equal to the cell which contains the row title for epsilon.
You should now have a 3D plot of your roots vertically with respect to alpha and epsilon on each of the horizontal axes. Play around with the colours/fonts/axes as required.
RE: Solving a non-linear equation a bunch of times
That's a fairly unpleasant function to find the root of. Assuming Smath agrees, I think you are back to Newton Raphson. Have you got typical values G1...G5, and T such that f(T)=0? Probably the quickest for you is Doug's suggestion and VBA, but I'll have a noodle around in Octave to see if it is fun.
Thanks guys. I don't think Smath is a good candidate to do this because, for one thing, I don't think it can do 3D graphs. Probably the best solution in the long run is to use MS Excel with VBA and Goal Seeker since Excel has good 3D graphing capability.
Quote (Agent666)
If you already have the roots (based on previous replies you have been given a few options to find them for all the combinations of alpha and epsilon), then whats wrong with simply using a 3D surface chart from excel?
Agent666 no I don't have the solution data for the equation so it's not just a graphing exercise.
ElectroMechanical Product Development
(Electronics Packaging)
UMD 1984
UCF 1993
RE: Solving a non-linear equation a bunch of times
I had a look at the ugly equation. Assuming G1..G5 are Real, for a given alpha and epsilon, all terms bar the second are Real, so for a real root the second term must be real. If T<=320 it is complex. None of the others are ever complex, if T is real.
The last 3 terms are just a constant. The first term looks like a parabola, the second is similar to a straight line. In other words on a graph it all just looks like a quadratic.
So ignoring petty details, you either have 2 repeated roots, no root, or 2 different roots.
You ought not constrain yourself to one tool or another. There's no reason you can't use SMath to do the math and something else to graph it, which is pretty typical in academic papers.
Guys,
Thanks for all the productive ideas! For lack of time and money, I ended up using MS Excel with its GoalSeeker solver. It took about four hours because it tool solving (using GoalSeeker) 212= 441 times. I changed the domains to go from 0 to 1 rather than 0.05 to 0.95. There was one real root for each pair. Then I did a surface plot and here it is:
I think it looks pretty cool and adds a good visual to the paper.
The constants are:
G1=9.4083X10-8
G2=0.767
G3=1005.6
G4=1129.5
G5=53.0
Thanks again!
ElectroMechanical Product Development
(Electronics Packaging)
UMD 1984
UCF 1993
RE: Solving a non-linear equation a bunch of times
VBA solver attached. It still needs some tidying up, but it should work for any similar problem. I'll post on my blog with more details when its finished.
For what its worth, here an alternative solution based on using Excels built in Solver, utilises solving the formula located within the sheet and looping through the alpha/epsilon values to solve the equation for the value of T when f(T) = 0.
About 20 minutes work to get the VBA working as intended and check against IDS's solution + about 15 minutes wasted due to forgetting you have to add Solver as a reference to use solver it in VBA! ....
On my computer, IDS's solution takes about 31 seconds.
On my computer, my solution takes about 24 seconds.
Same answer as IDS obviously to about 4-5 decimal places.
So unless there is some error in the constants or formulas then Tunalover it looks like your solution probably has some error?
just for giggles, the problem is set up in Mathcad, and the answers are identical to IDS'.
Mathcad took 33 milliseconds to solve the 441 iterations, and it took about 10 minutes to set up the problem
just for giggles, the problem is set up in Mathcad, and the answers are identical to IDS'.
Mathcad took 33 milliseconds to solve the 441 iterations, and it took about 10 minutes to set up the problem
OK, challenge accepted :)
Instead of solving the equation on the sheet, I added a short VBA function to evaluate it. Solution time is now 0.23 seconds, so still a fair bit slower than Mathcad, but at least it's faster than Solver.
It will be interesting to see how Scipy performs, but that will have to wait, I'm supposed to be on holiday.
Well I learned some stuff. Use of global, and vectorised fsolve, to be exact. Got it down to 0.55 seconds for the 21x21 matrix. I'll see how matlab handles it next week.
My octave script mangled into matlab form runs in.... 1.5 seconds. That's Matlab 2015a 32bit, on W10, on a Dell. My time in octave on my rather old macbook air was 0.55 seconds (at best, sometimes it takes longer). Admittedly Matlab 2018 is supposed to run faster, but um, that's all a bit embarrassing!
Admittedly Matlab 2018 is supposed to run faster, but um, that's all a bit embarrassing!
Quite surprising, I expected the MATLAB times to be similar to Scipy, or at least Mathcad.
I should also confess that the 5 milliseconds in Scipy was just the time for doing all the hard work. When you add in the time to transfer 42 numbers from Excel, and transfer 441 numbers back again, the time comes up to about 30 milliseconds.
Among other possibilities is that there may be some licensing activity (I don't think so, Matlab only seems to check every 15 minutes) or that my horrible selection of toolboxes is slowing things down, again I don't think so, as a second run would have cache'd anything special. I'll try running tic toc just on the fsolve call.
A contactor is an electrical device used for switching an electrical circuit on or off. Considered to be a special type of relay, contactors are used in applications with higher current carrying capacity, while relays are used for lower current applications. Download Now
Eight years into its development, PTC’s Creo Parametric continues to expand its toolset, making computer-aided design (CAD) easier and more efficient. Download Now
This research report, based on a survey of 234 product development professionals, examines the current state of Internet of Things (IoT) adoption by product design teams, its perceived importance, and what features and capabilities teams consider important when making decision about adding IoT functionality to their products. Download Now
The term Industry 4.0 denotes a cluster of technologies that’s poised to
fundamentally reshape manufacturing and bring about a new industrial
revolution. These include 3D printing
(AM), the Industrial Internet of Things (IIoT), artificial intelligence (AI) and
mixed reality technologies, more commonly known as virtual reality (VR) and
augmented reality (AR). Download Now
Talk To Other Members
RE: Solving a non-linear equation a bunch of times
» By "a hundred or more iterations" do you mean that you need to solve of your nonlinear equation a hundred times, each with a different set of input constants / coefficients?
» Or do you mean that you need a single solution using an iterative process that will require up to a hundred iterations to converge?
» Or both?
If it is either (1) or (3), you could carry out the solution process in VBA through a user defined function.
RE: Solving a non-linear equation a bunch of times
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Solving a non-linear equation a bunch of times
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: Solving a non-linear equation a bunch of times
https://newtonexcelbach.com/2014/08/10/faster-biax...
More recently there are examples using xlwings to connect Excel with Python and Scipy:
https://newtonexcelbach.com/2018/04/09/xlwscipy-up...
All the download files are free and open-source.
Please ask if anything is not clear or doesn't work.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Solving a non-linear equation a bunch of times
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: Solving a non-linear equation a bunch of times
RE: Solving a non-linear equation a bunch of times
Take an equation that looks like A + Bxg + Cxh=0
where
A, B, C, g, and h are real numbers.
I want to solve the equation for x for n values of C thus providing n solutions for x.
The solution would go like this:
1. Set A, B, g, and h. These are constants.
2. Set i=1
3. Set C=Ci
4. Solve A + Bxig + Cixih=0 for xi using Excel's Goal Seeker
5. Record xi
6. Set i=i+1
7. Set Ci=Ci-1+ ΔC
8. If i≤n go to 4.
I hope this makes it clear. From what I've done so far Goal Seeker works fine. I suspect VBA will be needed. If some kind soul could show the VBA macro for this I'd be really grateful! I simply can't afford to buy MathCAD, Matlab, or any other math program at this time.
ElectroMechanical Product Development
(Electronics Packaging)
UMD 1984
UCF 1993
https://www.youtube.com/watch?v=BKorP55Aqvg
RE: Solving a non-linear equation a bunch of times
https://en.smath.info/view/SMathStudio/summary
https://en.smath.info/forum/yaf_topics12_Download-...
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: Solving a non-linear equation a bunch of times
https://newtonexcelbach.com/2009/07/25/using-goal-...
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Solving a non-linear equation a bunch of times
It does units extremely well, and, in matrices also.
Dik
RE: Solving a non-linear equation a bunch of times
Some guidance is appreciated. I'm publishing a journal article and a 3D graph would be a really good, eye-catching way to bring home an important point to the readers. Does SMath do multi-variable graphs?
ElectroMechanical Product Development
(Electronics Packaging)
UMD 1984
UCF 1993
RE: Solving a non-linear equation a bunch of times
Arrange the data in a table with alpha horizontally, epsilon vertically, select all the data range for the roots without the labels. Then select insert recommended chart, find the 3D surface chart under all charts tab and add this. Right click on the chart and select 'select data', under Horizontal (category) axis label, select edit and set it to one of the the 0.05 through to 0.95 alpha range labels at the top of your table for the labels.
You will see a group of series, these are each of the rows in your table of roots of T, edit each series and for the series title make it equal to the cell which contains the row title for epsilon.
You should now have a 3D plot of your roots vertically with respect to alpha and epsilon on each of the horizontal axes. Play around with the colours/fonts/axes as required.
RE: Solving a non-linear equation a bunch of times
To the best of my recollection you can do 3d mesh like plots, but not contour plots.
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Solving a non-linear equation a bunch of times
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Solving a non-linear equation a bunch of times
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Solving a non-linear equation a bunch of times
Agent666 no I don't have the solution data for the equation so it's not just a graphing exercise.
ElectroMechanical Product Development
(Electronics Packaging)
UMD 1984
UCF 1993
RE: Solving a non-linear equation a bunch of times
The last 3 terms are just a constant. The first term looks like a parabola, the second is similar to a straight line. In other words on a graph it all just looks like a quadratic.
So ignoring petty details, you either have 2 repeated roots, no root, or 2 different roots.
How do you select which root you want to plot?
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Solving a non-linear equation a bunch of times
RE: Solving a non-linear equation a bunch of times
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: Solving a non-linear equation a bunch of times
Thanks for all the productive ideas! For lack of time and money, I ended up using MS Excel with its GoalSeeker solver. It took about four hours because it tool solving (using GoalSeeker) 212= 441 times. I changed the domains to go from 0 to 1 rather than 0.05 to 0.95. There was one real root for each pair. Then I did a surface plot and here it is:
I think it looks pretty cool and adds a good visual to the paper.
The constants are:
G1=9.4083X10-8
G2=0.767
G3=1005.6
G4=1129.5
G5=53.0
Thanks again!
ElectroMechanical Product Development
(Electronics Packaging)
UMD 1984
UCF 1993
RE: Solving a non-linear equation a bunch of times
Incidentally I was wrong above, about the roots. I shouldn't have ignored G3-5.
Oddly i get different results to you, these are the 4 corners, in Kelvin
Elapsed time is 6.0929 seconds.
ans = 1597.9
ans = 333.45
ans = 2636.9
ans = 384.42
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Solving a non-linear equation a bunch of times
The code didn't take long, because I already had the important bit. I don't know how long the original code took, much more than a couple of hours.
I'll have a look at Excel + Python as well.
I will upload the spreadsheet, but I will tidy it up a bit first.
I get very different results to Greg for the high values, almost the same for the low ones.
675.1 901.1
333.4 384.1
The function I am solving is:
G_1*epsilon*T^4+G_2*(T-320)^1.25-G_3*alpha-G_4-G_5
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Solving a non-linear equation a bunch of times
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Solving a non-linear equation a bunch of times
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Solving a non-linear equation a bunch of times
About 20 minutes work to get the VBA working as intended and check against IDS's solution + about 15 minutes wasted due to forgetting you have to add Solver as a reference to use solver it in VBA! ....
On my computer, IDS's solution takes about 31 seconds.
On my computer, my solution takes about 24 seconds.
Same answer as IDS obviously to about 4-5 decimal places.
So unless there is some error in the constants or formulas then Tunalover it looks like your solution probably has some error?
RE: Solving a non-linear equation a bunch of times
Here are the corners again
ans = 675.31
ans = 334.95
ans = 901.72
ans = 384.69
Well that's annoying, the previous answers were wrong because the initial guess was wrong. I should always plot the function out.
In this case it would be a good idea to plot the first one and use the root of that as the guess for the next run.
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Solving a non-linear equation a bunch of times
Mathcad took 33 milliseconds to solve the 441 iterations, and it took about 10 minutes to set up the problem
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: Solving a non-linear equation a bunch of times
So, something very nasty in the woodshed for a few of the values, although it recovers neatly enough
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Solving a non-linear equation a bunch of times
OK, challenge accepted :)
Instead of solving the equation on the sheet, I added a short VBA function to evaluate it. Solution time is now 0.23 seconds, so still a fair bit slower than Mathcad, but at least it's faster than Solver.
It will be interesting to see how Scipy performs, but that will have to wait, I'm supposed to be on holiday.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Solving a non-linear equation a bunch of times
Solution time = about 6 milliseconds :)
I'll post details when I have time.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Solving a non-linear equation a bunch of times
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Solving a non-linear equation a bunch of times
https://newtonexcelbach.com/2018/10/21/iterative-s...
Solution time varies from about 10 seconds down to about 5 milliseconds.
Details and open source code is included with the downloads. Any questions, please ask.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Solving a non-linear equation a bunch of times
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Solving a non-linear equation a bunch of times
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Solving a non-linear equation a bunch of times
Quite surprising, I expected the MATLAB times to be similar to Scipy, or at least Mathcad.
I should also confess that the 5 milliseconds in Scipy was just the time for doing all the hard work. When you add in the time to transfer 42 numbers from Excel, and transfer 441 numbers back again, the time comes up to about 30 milliseconds.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Solving a non-linear equation a bunch of times
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?