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

[mrtangent]

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.

 

[mloew]

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.

 

[pmover]

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

 

[mrtangent]

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.

 

[mloew]

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.

 

[Denial]

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.)

(2) You can set up a small iteration scheme within the body of your spreadsheet. The drawback is that you have to pre-determine how many iterations you will use, and that means you will usually proved too many. The iteration scheme would typically take the form of a table, one column per iteration. Row 1 contains the latest estimate of the answer. Rows 2 through n-1 contain some intermediate results based on that latest estimate of the answer. Row n contains an improved estimate of the answer based on the various intermediate results. Then the next column takes this improved estimate in its row 1, and the process repeats. With a good iteration scheme and a good starting estimate of the answer, you will not need too many columns to be assured of adequate convergence. Develop the table in the business area of your spreadsheet, then when it is finished and fully tested move it off to some out-of-sight area. You should always do some sort of accuracy check after the final iteration, and have the spreadsheet alert the user to any convergence failure. (The "accuracy check" could be as simple as comparing the final value with the value from the previous iteration.)

(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

 

[GregLocock]

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

 

[Denial]

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 ...)

 

[mloew]

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.

 

[bltseattle]

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