×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

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

Students Click Here

Jobs

Calculating Open Channel Flow in Excel
3

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

RE: Calculating Open Channel Flow in Excel

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

RE: Calculating Open Channel Flow in Excel

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

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

CODE

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

RE: Calculating Open Channel Flow in Excel

(OP)
Thanks -

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

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

RE: Calculating Open Channel Flow in Excel

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.

RE: Calculating Open Channel Flow in Excel

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.

RE: Calculating Open Channel Flow in Excel

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

RE: Calculating Open Channel Flow in Excel

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&amp;page=1

David Dietrich
KMK Consultants Ltd
Windsor, Ontario, Canada

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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources