Calculating Open Channel Flow in Excel 3

[KThomson]

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

 

[gbam]

Hello Kate,
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

 

[bltseattle]

I have part of what you need. Until recently, I used a lookup table based on the Lindeburg Appendix 3E to compute area of a partially full pipe. Below you can find a user-defined Excel function to compute the area of a partially filled circle (e.g. horizontal tank x-section).

Here is the reference link for derivation of the formula:

http://mathforum.org/library/drmath/view/52103.html

And here is the VBA code for a user defined function:

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, 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

 

[KThomson]

Thanks -

I am not familiar with VBA codes in Excel, but I will try to teach myself tomorrow!

I appreciate everyones help -

Kate

 

[darth051]

Kate,

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)

 

[Hookem]

Folks: And, I have an Excel Spreadsheet for open channel flow using user defined channel geometry will calculate flow capacities as a function of height. The same exact concept can be use in pipes as well. No need to error prone use of pencil and caluculators.

 

[yack]

I don't do hydraulics. But in the past I did write such a spreadsheet, using Newton's method to converge at the solution. It was kind of cool the solution converges real fast. Also, it gave me an excuse to use partial differential calculus!

Don't have the spreadsheet anymore.

 

[nick1010]

Hi everyone,

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

 

[darth051]

There is a similar thread for calculating partially full pipe depths, areas, wetted perimeters, etc. where I have explained how to set up a spreadsheet to handle the calculations.

Please see "Open Channel, Partially full pipe flow in excel. Please help!" in this forum.

http://www.eng-tips.com/viewthread.cfm?qid=111302&page=1

David Dietrich
KMK Consultants Ltd
Windsor, Ontario, Canada