Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in
  • Congratulations KootK on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Solving a non-linear equation a bunch of times 5

Status
Not open for further replies.
Tunalover

Tunalover

Mechanical
Mar 28, 2002
1,179
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


ElectroMechanical Product Development
(Electronics Packaging)
UMD 1984
UCF 1993
 
Replies continue below

Recommended for you

Sort by date Sort by votes
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) 21[sup]2[/sup]= 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:
Surface_Plot_gfeqzk.jpg

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
 
Upvote 0 Downvote
It takes 7 seconds in Octave... but a couple of hours to write the script, which, once I found the right algorithm, should have taken 15 minutes.

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
 
Upvote 0 Downvote
My VBA took 20 seconds, but I should be able to get that down a bit.

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
 
Upvote 0 Downvote
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.

Quadbrebta1-1_sxd5mv.png




Doug Jenkins
Interactive Design Services
 
Upvote 0 Downvote
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?
 
 https://files.engineering.com/getfile.aspx?folder=94e02c4e-37d2-4024-a455-da961d3582ff&file=Tunalover_example.xlsm
Upvote 0 Downvote
Elapsed time is 4.72551 seconds. (for the 21*21 grid)
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
 
Upvote 0 Downvote
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
solver_xixdzd.png

surface_u6clqh.png


TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! faq731-376 forum1529 Entire Forum list
 
Upvote 0 Downvote
Here's a plot of the progress, x axis is iteration number, y axis the current estimate of the root

So, something very nasty in the woodshed for a few of the values, although it recovers neatly enough

bruce_mrsbbp.png


Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376
 
Upvote 0 Downvote
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
Click to expand...

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
 
Upvote 0 Downvote
I have now set this up to solve from Excel using Python and Scipy.

Solution time = about 6 milliseconds :)

I'll post details when I have time.

Doug Jenkins
Interactive Design Services
 
Upvote 0 Downvote
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.

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376
 
Upvote 0 Downvote
Upvote 0 Downvote
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!

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376
 
Upvote 0 Downvote
Admittedly Matlab 2018 is supposed to run faster, but um, that's all a bit embarrassing!
Click to expand...

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
 
Upvote 0 Downvote
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.

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kenja824
  • Locked
  • Question
Creating a schedule?
Replies
17
Views
390
Kenja824
Kenja824
n8cole
  • Question
Looking for an AI that will help me create a chemical functional group database
Replies
7
Views
2K
pierreick
pierreick
BhagyeshVashi
  • Locked
  • Question
Need a rename macro for excel
Replies
11
Views
547
sdz
sdz
Kenja824_home
  • Locked
  • Question
Excel - Looking for a formula please 2
Replies
14
Views
341
BridgeSmith
BridgeSmith
As-Lag
  • Locked
  • Question
How to update calculations after a #NAME? or #VALUE?i repair
Replies
6
Views
136
SkipVought
SkipVought

Part and Inventory Search

Sponsor

Back
Top Bottom