Solver

  • Thread starter Thread starter TCEBob
  • Start date Start date
T

TCEBob

Solver is pretty good but it appears to have a few drawbacks. It is not a
function, so you have to run it by hand every time. And it leaves all previously
calculated values changed. I am cooking up a hydraulic flow sheet in which each
line provides for entry of some values like channel depth and width, and
produces values like velocity and energy. I need to show a column for the depth
at which the energy is minimized. So I varied depth in Solver and got a
believable value for the min. energy and the depth that produces it.

Is there a function which will do that? There are a number of solver functions
in User Functions but not much guidance.

rs
 
One. Depth.

Here's the formula: E=y+V^2/64.4
y is depth
V is Q/A (flow/area), which - for a single row - remains constant.
The resulting curve is an hyperbola asymptotic to the x-axis and a 45 degree
line. The least E (x-axis) marks the minimum depth
| *
|d *
|e *
|p *
|t *
|h @ right here.
| *
| *
| *
|______________________________________________*
E axis

I hope you can come up with something!

rs
 
Please clarify. If V is constant, then you have given the equation for
a line, not a parabola.

Jerry
 
For each row I can change one or more of the parameters:

b=bottom width
z=side slopes (as in run/rise)
Q=flow (cubic feet/second)
y=depth

The dependant variables are:

A=area ;for this problem the channel is trapezoidal.
V=velocity
E=energy
WP=wetted perimeter
Rh=hydraulic radius

For readability in the spreadsheet I refer to V to calculate E even though V is
in turn the result of Q/A .

Of course, it is possible to solve for minimum E by calculus (where the slope is
infinite). But in large-scale hydraulics such as drainage 3 decimal places is
more than adequate.

In this case I wish to vary y to produce the minimum E, given constant b, z, and
Q. Hence Solver, which works but cannot be integrated into the spreadsheet as an
automatic function.

Please note that I said "hyperbola," not "parabola." The legs are asymptotic, as
I pointed out.

The actual equation is E=y+V^2/2g

Noting that V=Q/A and A=by+zy^2 -> V=Q/(by+zy^2),
and g=32.2 ft/sec/sec

E=y+(Q^2/(by+zy^2)^2)/64.4)

which is why I used a spreadsheet.

rs
 
Don't know if this is something you had in mind. You gave no test data, so
if I set the following to values on a worksheet:

b= 2
q= 4
z= 3

Solver varied 'y' and returned a minimum value of
0.404677534
using the equation:
y+(q^2/(b*y+z*y^2)^2)/64.4

Using the custom function below returned
0.404677534099498

However, this uses the Newton method, and apparently, it is not very stable.
The initial guess needs to be fairly close. Your actual data may make if a
little better. It is probably better to use the other methods.
I have not included any fancy programming to account for special cases.
This is just something to see if it is something you might want to research
more.
I changed y to x. Instead of looking for an infinite slope, I switched it
around to look for a 0 slope.

Debug.Print Fx(b:=2, q:=4, z:=3, guess:=0.2)

Returned: 0.404677534099498


Function Fx(b, q, z, guess)
'// = = = = = = = = = = = = = = = =
'// Dana DeLouis
'// = = = = = = = = = = = = = = = =

Dim LastGuess As Double
Dim x As Double
Dim Counter As Long
Const g As Double = 32.2

x = guess
LastGuess = x + 1 ' Just make it different

Do While LastGuess <> x And Counter <= 20
LastGuess = x

x=(x*(4*b^2+ _
x*(13*b*z+x*(12*z^2+ _
x*(-((b^4*g)/q^2)+ _
x*(-((4*b^3*g*z)/q^2)+ _
x*(-((6*b^2*g*z^2)/q^2)+ _
x*(-((4*b*g*z^3)/q^2)-(g*x*z^4)/q^2))))))))/ _
(3*b^2+x*(10*b*z+10*x*z^2))

' Debug.Print Counter; x
Counter = Counter + 1
Loop
Fx = x
End Function


Again, I know this is not stable, nor does it have error checking.
HTH :>)
Dana DeLouis
 
I'm going to give that a try tomorrow. Your test data are fine; almost any
numbers will work. Real life would have a larger Q, but who cares?

I appreciate your effort! Thank you.

rs
 
Back
Top