Calculating Open Channel Flow in Excel
Calculating Open Channel Flow in Excel
(OP)
Hello --
I have been trying to figure out a solution for this in Excel and can't figure out how to do it.
I want a formula that can determine velocity in a circular pipe (not flowing full). I have a design flow, capacity of the pipe, and all of that information. I need something that determines the depth of flow (or the angle, as shown in my Lindeburg CERM manual with the equations for area and hydraulic radius) to determine the area of flow, and then use that information to solve for the velocity. (I plan on using Manning's equation, unless someone has another idea!)
I have charts to find this by hand with the q/Q and v/V ratios, but I want to find or create the formula in excel since I will use it often and save time.
Any ideas??
Thanks in advance -
Kate
I have been trying to figure out a solution for this in Excel and can't figure out how to do it.
I want a formula that can determine velocity in a circular pipe (not flowing full). I have a design flow, capacity of the pipe, and all of that information. I need something that determines the depth of flow (or the angle, as shown in my Lindeburg CERM manual with the equations for area and hydraulic radius) to determine the area of flow, and then use that information to solve for the velocity. (I plan on using Manning's equation, unless someone has another idea!)
I have charts to find this by hand with the q/Q and v/V ratios, but I want to find or create the formula in excel since I will use it often and save time.
Any ideas??
Thanks in advance -
Kate





RE: Calculating Open Channel Flow in Excel
This can be done in one of two ways, both however, using visual basic. You can code the v/V, q/Q & y/Y tables and use linear interpolation to get results. The second way is to use manning's eqn and code a loop to close in on the solution. I prefer the latter because once you get the depth then all other variables are easily found using standard equations. I have coded this into my calculator (TI85) and it works well. Please refer to "Fundamentals of Hydraulic Engineering", Alan Prasuhn. Section 7-7. There is a break down of partial flow in pipes.
z=D/2-y
theta=cos^-1(2z/D)
A=r^2theta-(rsintheta)(rcostheta)
P=Dtheta
R=A/P
Q=1.49/n A R^2/3 S^.5
I will not define every variable unless you need it.
Just create a loop to solve for Q as a function of y and cycle through the loop until the error is with in your tolerance. It works well for my calculator. BTW my calc. uses a somplified basic code.
GOODLUCK
GB
RE: Calculating Open Channel Flow in Excel
Here is the reference link for derivation of the formula:
http://mathforum.org/library/drmath/view/52103.htm...
And here is the VBA code for a user defined function:
CODE
' This function will determine the area occupied when a circular section is partially filled.
' Prepared by Brian Taylor, 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
RE: Calculating Open Channel Flow in Excel
I am not familiar with VBA codes in Excel, but I will try to teach myself tomorrow!
I appreciate everyones help -
Kate
RE: Calculating Open Channel Flow in Excel
I just so happen to already have such a spreadsheet in Excel.
I set up a table that calculated the flow and velocity in a generic pipe at incremental depths. I then used the lookup function to find the entry in the table with the closest "% Full Flow Capacity" and read off the corresponding "% full flow velocity".
The only catch was to remember to switch the area and wetted perimeter formuals at the half-way point. I also assigned the entire table a Name to make referencing easy.
David Dietrich
KMK Consultants (Windsor)
RE: Calculating Open Channel Flow in Excel
RE: Calculating Open Channel Flow in Excel
Don't have the spreadsheet anymore.
RE: Calculating Open Channel Flow in Excel
I'm trying to set up a spreadsheet to calculate flow (ft.^3/s) in a circular pipe. I have used manning's formula to find the Q using, n=.015 s=.001 and it checks out right, but can anyone help me reverse this process and find the d/D ratio from Q (Flow). In excel there is an option called "Goal Seek" that gives me the answer but this is a temporary solution because it's terribly limited. I want this spreadsheet to calculate Q in GPM and tell me at what percentage the pipe is running at. That part works fine. But then I want to add any desired amount of flow (in GPM of course) to that Q and I want it to tell the increased percentage or (d/D). It's basicly reversing the process. Can anyone help, please? Thanks
RE: Calculating Open Channel Flow in Excel
Please see "Open Channel, Partially full pipe flow in excel. Please help!" in this forum.
http://www
David Dietrich
KMK Consultants Ltd
Windsor, Ontario, Canada