×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Solving a non-linear equation in function langage in EXCEL

## Solving a non-linear equation in function langage in EXCEL

(OP)
Does anyone have a good example of how to solve a non-linear function using the function command ?

for example.

if you want to know the level corresponding to a particular height in a cyindral tank you need to solve

A= r^2 cos-1 (r-H/r)- [(r-H) * (2rH-H^2)^0.5]
for H knowing r and A.

I remember some linear programming - golden section search maybe but wondered if anyone has any good examples ?

(I done this using a V-LOOKUP table for varing H,r and outputs of A but there must be a function way to do this).

Sorry if this is a dumb qustion.
Replies continue below

### RE: Solving a non-linear equation in function langage in EXCEL

james1030bruce,

You should be able to use the solver function in Excel. Try Tools > Goal Seek.

Best regards,

Matthew Ian Loew
"Luck is the residue of design."
Branch Rickey

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips Fora.

### RE: Solving a non-linear equation in function langage in EXCEL

slight clarification to MLoew posting...

XL has "Solver" and "Goal Seek" capabilities.  "Goal Seek" solves for a single variable (applicable to james1030bruce situation); whereas, the "Solver" solves for a single variable given constraints or limits of other variables.

good luck!
-pmover

### RE: Solving a non-linear equation in function langage in EXCEL

(OP)
Thanks for the info on solver/goal seek.

I was hoping to do was to avoid the manual nature of the goalseek and solver so to use a function such as.

=Heightofcircle(A,R)
and let the function heightofcircle do the solver/goal seek function via some optimisation function or command or for/next loop.  But I've not managed to understand or find some good information that explains how to do to this.

### RE: Solving a non-linear equation in function langage in EXCEL

pmover,

Thanks for the clarification. I am usually doing that type of solving directly in the CAD package I use. I agree with james1030bruce in that it would be nice to have the goal seek function automatically recalculate when new values are introduced. Anyway to do this?

Best regards,

Matthew Ian Loew
"Luck is the residue of design."
Branch Rickey

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips Fora.

### RE: Solving a non-linear equation in function langage in EXCEL

Before I start, I think you have left a factor of 2 off the first term in your formula.  And there is a much easier form of the formula in terms of the angle that the wetted perimeter  subtends at the centre of the circle:
A = 0.5 * r^2 * (theta-sin(theta))
Solve this for the angle theta, then use simple trig to get from theta to H.

But your question is more general.  How to solve an iterative equation in Excel, and how to do it so that the answer will appear instantly rather than requiring the user to push a button, activate a menu item, or run a macro.  There are several ways, and which one you use depends on the nature of the equation being solved, the skill-level of the spreadsheeter, and the required robustness in the end product.

(1)  You can write a VBA subroutine that will invoke the Solver or GoalSeeker for you, and set it up so that this subroutine is automatically executed every time the sheet (or any key cell on the sheet) is changed.  This does not provide you with total flexibility, because the subroutine needs to know the address of the target cell, the target value, and the cell to be changed to meet the target.  (Furthermore, if you use the Solver in a context like this, you might have troubles porting your spreadsheet between different versions of Excel.  I certainly do when I call the Solver from VBA.)

(3)  You could write your own VBA user-defined function to implement the iteration scheme to whatever degree of accuracy you deem appropriate.  (Here your UDF does not attempt to use the solver or the goalseeker.)

HTH

### RE: Solving a non-linear equation in function langage in EXCEL

I seem to remember that solver won't run from a function, but will from a sub. I don't know if you can run a sub from a function, probably not.

Failing that you need some sort of event led call to a sub that calls goal seek - logic would be

if argument_cell is changed then call my_solver()

So, is it possible to run a sub in response to a cell being changed?

Cheers

Greg Locock

### RE: Solving a non-linear equation in function langage in EXCEL

To do something in response to a cell being changed, use the Worksheet_Change() event handler.  It does not reside in a VBA module, but in the code window of the worksheet concerned.

There is plenty of advice out on Google Groups on how to drive this, and how to set it up to trigger only when specific cell(s) change.

One subtle point to watch out for is the temporary use of the
Application.EnableEvents = False
command to avoid infinite recursion (when a change triggers the event handler and the event handler then makes a change which tirggers the event handler which ...)

### RE: Solving a non-linear equation in function langage in EXCEL

Denial,

Nice tips; star for you!

Best regards,

Matthew Ian Loew
"Luck is the residue of design."
Branch Rickey

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips Fora.

### RE: Solving a non-linear equation in function langage in EXCEL

Here is a function I developed to find the partially full area of a circle, which I use in volume calculations for horizontal circular tanks.  Maybe this will help...

Public Function part_full_circ_area(depth, Diam) As Double
'  This function will determine the area occupied when a circular section is partially filled.
'  Prepared by Brian Taylor, Magnusson Klemencic Associates, April 2004
'
Dim arg1, eval_asin As Double
'
Const pi = 3.14159265
'  Screen out very low values.  Precision is limited to input depths greater than 1E-16
If depth <= 0.000000000000001 Then
part_full_circ_area = 0
Else
If depth >= Diam Then
part_full_circ_area = pi * Diam ^ 2 / 4
Else
' The following code computes the arcsin(1-2d/D).
' Note that Visual Basic does not have a function for arcsin.
' The following derived function is used: Arcsin(X) = Atn(X / Sqr(-X * X + 1))
arg1 = 1 - 2 * depth / Diam
eval_asin = Atn(arg1 / Sqr(-arg1 * arg1 + 1))
'
'  Now apply the area formula:
'  A = pi*D^2/8 - D^2/4*arcsin(1-2d/D)-(D/2-d)*sqr(d(D-d))
'
part_full_circ_area = pi * Diam ^ 2 / 8 - Diam ^ 2 / 4 * eval_asin - (Diam / 2 - depth) * Sqr(depth * (Diam - depth))
End If
End If
End Function

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!