{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Calculations","Instructions","Sheet3"],"not_rendered":{"charts":2,"images":0,"macros":1,"external_links":0,"defined_names":2,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Calculations","index":0,"n_rows":62,"n_cols":31,"rows_kept":62,"cut":false,"hidden":false,"merged":[[38,16,38,30],[72,11,72,12],[39,6,39,7],[8,0,8,7],[52,7,52,8],[51,7,51,8],[53,6,53,8],[60,5,61,5],[0,0,1,8],[7,0,7,7],[5,1,5,8],[6,1,6,5],[6,7,6,8],[3,0,3,8],[2,2,2,4]]}
["Stream Crossing Worksheet"]
[]
[null,null,"Version 01.12"]
["ENABLE macros to use the buttons on this spreadsheet.  For culvert crossings, compute the peak flow rate for the desired design storm for the watershed draining to the proposed crossing.  Use EFH-2 or other means to perform this calculation. (Use the 2-yr, 24 hr storm for culvert crossings, according to VA CPS 578.) "]
[]
["Project Description:"]
["Prepared by:",null,null,null,null,null,null,{"$f":"TODAY()","v":"2014-04-11"}]
[]
["1. Enter Survey Data (ft):"]
[null,"Distance (ft)","Elevation (ft)"]
["Cross Section:"]
["Left Floodplain"]
["Top Left Bank"]
["Toe Left Bank"]
["Center",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Bankfull Depth Plot",null,null,null,"Q10 Trial Depth Plot",null,null,null,null,null,null,null,"Vertical Lines for In-Channel Flow"]
["Toe Right Bank",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"X","Y",null,null,"X","Y",null,null,null,null,null,null,"X","Y"]
["Top Right Bank"]
["Right Floodplain",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"$B$12","v":0},{"$f":"$C$52","v":0},null,null,{"$f":"$B$12","v":0},{"$f":"$H$42","v":0},null,null,null,null,null,null,{"$f":"$B$13","v":0},{"$f":"$C$13","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"$B$18","v":0},{"$f":"$C$52","v":0},null,null,{"$f":"$B$18","v":0},{"$f":"$H$42","v":0},null,null,null,null,null,null,{"$f":"$B$13","v":0},{"$f":"IF($H$42>=$C$13,$H$42,$C$13)","v":0}]
["Profile (riffle to riffle):"]
["Upstream station:",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"$B$17","v":0},{"$f":"$C$17","v":0}]
["Downstream station:",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"$B$17","v":0},{"$f":"IF($H$42>=$C$17,$H$42,$C$17)","v":0}]
["Slope:",null,{"$f":"IF($B$22=\"\",\"\",ABS(($C$21-$C$22)/($B$22-$B$21)))","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Coefficient of Meander Choices"]
[]
["Note:  Plot scales can be modified by right-clicking them and then",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Minor",0]
["choosing \"Format Axis.\"",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Appreciable",0.15]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Severe",0.3]
[]
[]
[]
[]
["2. Compute in-channel \"n\" value: "]
["(See Cowan as referenced in VA Erosion & Sediment Control Handbook)"]
[null,null,null,null,null,"3.  Determine Water Surface Elevation for Design Flow:"]
["Channel character","n1 =",null,null,null,"(Use for culvert crossings.)"]
["Irregularity","n2 ="]
["Size & shape variations","n3 =",null,null,null,"Design Q from other tools: ",null,"cfs",null,null,null,null,null,null,null,null,"The cross section is divided into six parts. Area and wetted perimeter are computed for each part based on the water level."]
["Obstructions","n4 =",null,null,null,"Storm Return Period:",null,"yrs",null,null,null,null,null,null,null,null,"If the water level is above the floodplain, the channel boundaries are extended vertically upward.  However, these extensions"]
["Vegetation","n5 =",null,null,null,"Method (EFH2, USGS, etc.):",null,null,null,null,null,null,null,null,null,null,"do not contribute to the wetted perimeter."]
["Sum of n1 through n5","ns =",{"$f":"SUM(C36:C40)","v":0}]
["Coefficient for meander: ",null,null,null,null,"Trial water surface elev. (ft):",null,null,null,null,null,null,null,null,null,null,null,"WSE = H48",null,null,null,"WSE = G34",null,null,null,null,null,null,null,"WSE = B48 = G34"]
["(Use 0 for Minor, 0.3 for Severe)",null,null,null,null,"(To approximate design flow.)"]
[null,"n6 =",{"$f":"C41*C42","v":0},null,null,null,null,null,null,null,null,null,null,null,null,null,"Channel Capacity (\"Bankfull\" Flow)",null,null,null,"Design Flow Trials",null,null,null,null,null,null,null,"In-Channel Flow at Design Depth"]
[null,null,null,null,null,"Area of channel, A: ",{"$f":"IF(H42=\"\",\"\",$V$56)","v":""},"sq. ft."]
["Bankfull channel \"n\":",null,{"$f":"C41+C44","v":0},null,null,"Composite n value:",{"$f":"IF(H42=\"\",\"\",Y59)","v":""}]
["User-defined \"n\" for Left Floodplain:",null,null,null,null,"Flow rate, Q:",{"$f":"IF(H42=\"\",\"\",Y61)","v":""},"cfs",null,null,null,null,null,null,null,null,"Section","Area","WP",null,"Section","Area (ft^2)","WP (ft)","n","WP * n^2",null,null,null,null,"Area (ft^2)","WP (ft)"]
["User-defined \"n\" for Right Floodplain:",null,null,null,null,"Resulting avg. velocity = Q/A:",{"$f":"IF(H42=\"\",\"\",G47/G45)","v":""},"fps",null,null,null,null,null,null,null,null,1,{"$f":"IF($C$52<=$C$13,0,IF($C$52<=$C$12,0.5*($C$52-$C$13)*($B$13-$B$12)*($C$52-$C$13)/($C$12-$C$13),0.5*($C$52-$C$12+$C$52-$C$13)*($B$13-$B$12)))","v":0},{"$f":"IF($C$52<=$C$13,0,IF($C$52<=$C$12,SQRT(($C$52-$C$13)^2+(($B$13-$B$12)*($C$52-$C$13)/($C$12-$C$13))^2),SQRT(($C$12-$C$13)^2+($B$13-$B$12)^2)))","v":0},null,1,{"$f":"IF($H$42<=$C$13,0,IF($H$42<=$C$12,0.5*($H$42-$C$13)*($B$13-$B$12)*($H$42-$C$13)/($C$12-$C$13),0.5*($H42-$C$12+$H$42-$C$13)*($B$13-$B$12)))","v":0},{"$f":"IF($H$42<=$C$13,0,IF($H$42<=$C$12,SQRT(($H$42-$C$13)^2+(($B$13-$B$12)*($H$42-$C$13)/($C$12-$C$13))^2),SQRT(($C$12-$C$13)^2+($B$13-$B$12)^2)))","v":0},{"$f":"$C$47","v":0},{"$f":"W48*(X48^2)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,2,{"$f":"IF($C$52<=$C$14,0,IF($C$52<=$C$13,0.5*($C$52-$C$14)*($B$14-$B$13)*($C$52-$C$14)/($C$13-$C$14),0.5*($C$52-$C$13+$C$52-$C$14)*($B$14-$B$13)))","v":0},{"$f":"IF($C$52<=$C$14,0,IF($C$52<=$C$13,SQRT(($C$52-$C$14)^2+(($B$14-$B$13)*($C$52-$C$14)/($C$13-$C$14))^2),SQRT(($C$13-$C$14)^2+($B$14-$B$13)^2)))","v":0},null,2,{"$f":"IF($H$42<=$C$14,0,IF($H$42<=$C$13,0.5*($H$42-$C$14)*($B$14-$B$13)*($H$42-$C$14)/($C$13-$C$14),0.5*($H$42-$C$13+$H$42-$C$14)*($B$14-$B$13)))","v":0},{"$f":"IF($H$42<=$C$14,0,IF($H$42<=$C$13,SQRT(($H$42-$C$14)^2+(($B$14-$B$13)*($H$42-$C$14)/($C$13-$C$14))^2),SQRT(($C$13-$C$14)^2+($B$14-$B$13)^2)))","v":0},{"$f":"$C$46","v":0},0]
["4.  Bankfull Flow:",null,null,null,null,"5. Stone Size Required for Ford Crossing:",null,null,null,null,null,null,null,null,null,null,3,{"$f":"IF($C$52<=$C$15,0,IF($C$52<=$C$14,0.5*($C$52-$C$15)*($B$15-$B$14)*($C$52-$C$15)/($C$14-$C$15),0.5*($C$52-$C$14+$C$52-$C$15)*($B$15-$B$14)))","v":0},{"$f":"IF($C$52<=$C$15,0,IF($C$52<=$C$14,SQRT(($C$52-$C$15)^2+(($B$15-$B$14)*($C$52-$C$15)/($C$14-$C$15))^2),SQRT(($C$14-$C$15)^2+($B$15-$B$14)^2)))","v":0},null,3,{"$f":"IF($H$42<=$C$15,0,IF($H$42<=$C$14,0.5*($H$42-$C$15)*($B$15-$B$14)*($H$42-$C$15)/($C$14-$C$15),0.5*($H$42-$C$14+$H$42-$C$15)*($B$15-$B$14)))","v":0},{"$f":"IF($H$42<=$C$15,0,IF($H$42<=$C$14,SQRT(($H$42-$C$15)^2+(($B$15-$B$14)*($H$42-$C$15)/($C$14-$C$15))^2),SQRT(($C$14-$C$15)^2+($B$15-$B$14)^2)))","v":0},{"$f":"$C$46","v":0},0,null,null,null,"(use sections 2-5 from out-of-bank calculations)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,4,{"$f":"IF($C$52<=$C$15,0,IF($C$52<=$C$16,0.5*($C$52-$C$15)*($B$16-$B$15)*($C$52-$C$15)/($C$16-$C$15),0.5*($C$52-$C$16+$C$52-$C$15)*($B$16-$B$15)))","v":0},{"$f":"IF($C$52<=$C$15,0,IF($C$52<=$C$16,SQRT(($C$52-$C$15)^2+(($B$16-$B$15)*($C$52-$C$15)/($C$16-$C$15))^2),SQRT(($C$16-$C$15)^2+($B$16-$B$15)^2)))","v":0},null,4,{"$f":"IF($H$42<=$C$15,0,IF($H$42<=$C$16,0.5*($H$42-$C$15)*($B$16-$B$15)*($H$42-$C$15)/($C$16-$C$15),0.5*($H$42-$C$16+$H$42-$C$15)*($B$16-$B$15)))","v":0},{"$f":"IF($H$42<=$C$15,0,IF($H$42<=$C$16,SQRT(($H$42-$C$15)^2+(($B$16-$B$15)*($H$42-$C$15)/($C$16-$C$15))^2),SQRT(($C$16-$C$15)^2+($B$16-$B$15)^2)))","v":0},{"$f":"$C$46","v":0},0]
["Bankfull water surface elev. (ft):",null,null,null,null,"Velocity","Stone","Min. Depth",null,null,null,null,null,null,null,null,5,{"$f":"IF($C$52<=$C$16,0,IF($C$52<=$C$17,0.5*($C$52-$C$16)*($B$17-$B$16)*($C$52-$C$16)/($C$17-$C$16),0.5*($C$52-$C$17+$C$52-$C$16)*($B$17-$B$16)))","v":0},{"$f":"IF($C$52<=$C$16,0,IF($C$52<=$C$17,SQRT(($C$52-$C$16)^2+(($B$17-$B$16)*($C$52-$C$16)/($C$17-$C$16))^2),SQRT(($C$17-$C$16)^2+($B$17-$B$16)^2)))","v":0},null,5,{"$f":"IF($H$42<=$C$16,0,IF($H$42<=$C$17,0.5*($H$42-$C$16)*($B$17-$B$16)*($H$42-$C$16)/($C$17-$C$16),0.5*($H$42-$C$17+$H$42-$C$16)*($B$17-$B$16)))","v":0},{"$f":"IF($H$42<=$C$16,0,IF($H$42<=$C$17,SQRT(($H$42-$C$16)^2+(($B$17-$B$16)*($H$42-$C$16)/($C$17-$C$16))^2),SQRT(($C$17-$C$16)^2+($B$17-$B$16)^2)))","v":0},{"$f":"$C$46","v":0},0]
["(Select the lower top of bank elevation.)",null,null,null,null,"0.0-6.0 fps","d50 = 2\", d100 = 4\"","8\"",null,null,null,null,null,null,null,null,6,{"$f":"IF($C$52<=$C$17,0,IF($C$52<=$C$18,0.5*($C$52-$C$17)*($B$18-$B$17)*($C$52-$C$17)/($C$18-$C$17),0.5*($C$52-$C$18+$C$52-$C$17)*($B$18-$B$17)))","v":0},{"$f":"IF($C$52<=$C$17,0,IF($C$52<=$C$18,SQRT(($C$52-$C$17)^2+(($B$18-$B$17)*($C$52-$C$17)/($C$18-$C$17))^2),SQRT(($C$18-$C$17)^2+($B$18-$B$17)^2)))","v":0},null,6,{"$f":"IF($H$42<=$C$17,0,IF($H$42<=$C$18,0.5*($H$42-$C$17)*($B$18-$B$17)*($H$42-$C$17)/($C$18-$C$17),0.5*($H$42-$C$18+$H$42-$C$17)*($B$18-$B$17)))","v":0},{"$f":"IF($H$42<=$C$17,0,IF($H$42<=$C$18,SQRT(($H$42-$C$17)^2+(($B$18-$B$17)*($H$42-$C$17)/($C$18-$C$17))^2),SQRT(($C$18-$C$17)^2+($B$18-$B$17)^2)))","v":0},{"$f":"$C$48","v":0},0]
[null,null,null,null,null,">6.0 fps","consult Engineering staff"]
["Area of channel, A: ",{"$f":"IF(C52=\"\",\"\",$R$56)","v":""},"sq. ft."]
["Wetted perimeter, P:",{"$f":"IF(C52=\"\",\"\",$S$56)","v":""},"ft.",null,null,null,null,null,null,null,null,null,null,null,null,null,"TOTAL",{"$f":"SUM(R48:R53)","v":0},{"$f":"SUM(S48:S53)","v":0},null,"TOTAL",{"$f":"SUM(V48:V53)","v":0},{"$f":"SUM(W48:W53)","v":0},null,{"$f":"SUM(Y48:Y53)","v":0},null,null,null,"TOTAL",{"$f":"SUM(V49:V52)","v":0},{"$f":"SUM(W49:W52)","v":0}]
["Hydraulic radius, R:",{"$f":"IF(C52=\"\",\"\",B55/B56)","v":""},"ft.",null,null,"6. Design Capacity for Culvert Flow"]
["Slope (decimal), S:",{"$f":"IF($B$22=\"\",\"\",ABS(($C$21-$C$22)/($B$22-$B$21)))","v":""}]
["Bankfull channel n:",{"$f":"IF(C52=\"\",\"\",$C$46)","v":""},null,null,null,"Q = lesser of Q2 and bankfull flow         =",null,{"$f":"IF(G38=\"\",\"\",MIN(G47,B62))","v":""},"cfs",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"n-composite",{"$f":"SQRT(Y56/W56)","v":{"$error":"#DIV/0!"}},null,null,null,null,"n-bankfull",{"$f":"C46","v":0}]
["Manning's equation:  V = (1.486 / n) * R2/3 * S1/2 ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"V",{"$f":"1.486/Y59*($V$56/$W$56)^(2/3)*SQRT($C$23)","v":{"$error":"#DIV/0!"}},null,null,null,null,"V",{"$f":"1.486/AE59*($AD$56/$AE$56)^(2/3)*SQRT($C$23)","v":{"$error":"#DIV/0!"}}]
["Bankfull velocity, V =",{"$f":"IF(C46=0,\"\",(1.486/$C$46)*(B57^(2/3))*(B58^(1/2)))","v":""},"fps",null,null,"Tailwater surface elevation to use in Culvert Flow Tool (ft):",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Q = VA",{"$f":"Y60*V56","v":{"$error":"#DIV/0!"}},null,null,null,null,"Q = VA",{"$f":"AD56*AE60","v":{"$error":"#DIV/0!"}}]
["Flow rate, Q = VA =",{"$f":"IF(B61=\"\",\"\",B61*B55)","v":""},"cfs",null,null,null,null,{"$f":"IF(G38=\"\",\"\",IF(B62<G47,C52,H42))","v":""},"ft"]

{"sheet":"Instructions","index":1,"n_rows":60,"n_cols":1,"rows_kept":60,"cut":false,"hidden":false,"merged":[[0,0,1,6],[4,0,4,7],[5,0,5,7],[6,0,6,7],[30,0,30,6],[7,0,7,7],[18,0,18,2],[10,0,10,6],[17,0,17,2],[63,0,63,6],[13,0,13,6],[47,0,47,6],[48,0,48,6],[31,0,31,6],[32,0,32,6],[46,0,46,6],[8,0,8,7],[40,0,40,6],[9,0,9,7],[11,0,11,6],[12,0,12,6],[41,0,41,6],[33,0,33,6],[64,0,64,6],[61,0,61,6],[62,0,62,6],[34,0,34,6],[56,0,56,6],[38,0,38,6],[39,0,39,6],[53,0,53,6]]}
["Instructions for Using the Stream Crossing Worksheet"]
[]
[]
["Background:"]
["The March 2008 version of Conservation Practice Standard 578 requires calculation of the:"]
["           •  2-yr, 24-hr peak runoff rate for culvert crossings, and the"]
["           • 10-yr, 24-hr peak runoff rate for ford crossings."]
[]
["These design flows can be determined with existing NRCS computation tools:  EFH2, USGS regression equations, etc."]
["This spreadsheet provides a means for determining the water surface elevation corresponding to the desired flow "]
["through trial and error.  In the case of culvert flow, the water surface elevation can be used as a tailwater input to the"]
["Culvert Flow Hydraulics Formula tool.  For ford crossings, this water surface elevation is used in step #4 to determine "]
["the velocity of the \"in-channel\" flow -- the faster moving flow within the main channel (not including the floodplain). "]
["This velocity is used for selecting an appropriate stone size."]
[]
["Instructions:"]
[]
["The gray cells on the worksheet are for user input."]
["Computed values are shown in pale yellow cells."]
[]
["1.  Enter Survey Data (ft):"]
["Survey data entered on the left side of the worksheet are plotted in the graphs to the right.  Seven points are allowed to "]
["describe the channel cross section and adjacent floodplain.  Two points describe the profile which is plotted below the cross-section.  "]
["The x and y axes may need to be adjusted to improve readability.  To do so, right-click the axis (a black square should appear at"]
["each end of the scale).  Select \"Format Axis\" and then click on the Scale tab.  Adjust the max and min values as appropriate.  "]
["You will also likely need to adjust where the x and y axes cross each other. "]
["Note:  Consider placing the crossing where livestock are already crossing the stream."]
[]
[]
["2. Compute in-channel \"n\" value:"]
["Select values to describe the roughness of the channel (not including the floodplain).  See pages V135-V139 in the"]
["Virginia Erosion and Sediment Control Handbook (1992 Edition) for guidance.  Based on the computed value for "]
["bankfull channel \"n\" and knowledge of the floodplain surface, pick a left and right floodplain \"n\" value for out-of-bank flow computations."]
["These values will be used to compute a composite 'n\" value which will be used to calculate out-of-bank flow."]
["Ask your Area Engineer for guidance in selecting \"n\" values."]
[]
[]
["3.  Determine Water Surface Elevation for Design Flow:"]
["Determine the desired design flow rate (such as Q2 or Q10) using NRCS tools or other methods.  Enter the design flow rate "]
["and record the method used in the spaces provided.  Next, enter a water surface elevation and adjust until the computed "]
["flow rate is close to the design value determined by other means.  These computations make use of the composite \"n\" value."]
["The method used for computing the composite n is as described by Dan Moore in :  "]
["http://www.wsi.nrcs.usda.gov/products/w2q/downloads/papers/manningsNaturally.pdf "]
["which cites Chow's Open Channel Hydraulics (Eq. 6-18)."]
[]
["4. In-Channel Flow at Design Water Surface Elevation:"]
["This section of the worksheet uses the water surface elevation determined in #3 and computes an in-channel velocity -- "]
["that is, the velocity of the water flowing between the Left and Right tops of bank.  The bankfull channel \"n\" value is used."]
["It is this velocity which is used for sizing stone.  This velocity is computed by applying Manning's formula to a cross-section"]
["defined by the design WSE and the two tops of bank (floodplains are cut off).  Because it ignores the turbulence between the "]
["floodplain and in-channel zones noted by Dan Moore, this estimate of velocity can be assumed to be conservatively high."]
[]
["4a.  Bankfull Flow:"]
["Bankfull flow is generally associated with the the 2-year storm.  Conservation Practice Standard 578 states that ford crossings "]
["must be able to withstand the 10-year 24-hr peak flow.  However, bankfull flow is still of interest for culvert crossings and "]
["streambank stabilization projects."]
["Enter the bankfull water surface elevation.  This is usually the lower of the Top Left Bank or Top Right Bank elevations."]
["A different value can be entered if recommended by the Area Engineer.  Velocity is computed using the bankfull \"n\" value."]
["For culvert crossings, the standard says to use the lesser of the bankful or 2-yr, 24-hr peak discharge.  The water surface elevation"]
["associated with the lesser of these two values can be entered as the tailwater elvation in NRCS' Culvert Flow tool."]

{"sheet":"Sheet3","index":2,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}