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

Cheers

Greg Locock


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

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! faq731-376 forum1529 Entire Forum list
 
Upvote 0 Downvote
There are quite a few examples using Newton's Method (and variants) with VBA on my blog, including:

More recently there are examples using xlwings to connect Excel with Python and Scipy:

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
 
Upvote 0 Downvote
Thanks guys! The only tool I have to work with is Excel 2016.

Take an equation that looks like A + Bx[sup]g[/sup] + Cx[sup]h[/sup]=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=C[sub]i[/sub]
4. Solve A + Bx[sub]i[/sub][sup]g[/sup] + C[sub]i[/sub]x[sub]i[/sub][sup]h[/sup]=0 for x[sub]i[/sub] using Excel's Goal Seeker
5. Record x[sub]i[/sub]
6. Set i=i+1
7. Set C[sub]i[/sub]=C[sub]i-1[/sub]+ Δ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
 
Upvote 0 Downvote
Upvote 0 Downvote
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.




Doug Jenkins
Interactive Design Services
 
Upvote 0 Downvote
Guys I installed SMath and couldn't figure out how to do what I want to do:
Figure_2_Problem_Statemeht_ltzxkm.jpg

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

 
Upvote 0 Downvote
I'm very rusty on Smath as I'm not allowed to install it on my work PC and Andrey hasn't released an OSX version.

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



Cheers

Greg Locock


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

How do you select which root you want to plot?

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376
 
Upvote 0 Downvote
Why not post the constants, then people can have a real crack at it if it indeed requires a numerical approach.
 
Upvote 0 Downvote
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.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! faq731-376 forum1529 Entire Forum list
 
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
138
SkipVought
SkipVought

Part and Inventory Search

Sponsor

Back
Top Bottom