>> Drop your fears at the door; love is spoken here. Enjoy the free libre HawsEDC AutoCAD tools too. <<
A highly automated calculator for tributary storm drain networks, street flow, and inlet bypass accounting. An interesting example of engineering use of spreadsheets.
Download SDNET.ODS (136 kb) spreadsheet in OpenOffice.org format
View or download SDNETKEY.PDF (499 kb) network key map
This spreadsheet is designed to calculate flows and hydraulic grade lines in a tributary storm drain network. It automatically keeps track of network connectivity by knowing which is the downstream "line" of every "line" in the network. It also keeps track of surface inlet connectivity by knowing if each inlet has an upstream inlet. It keeps track of flows by adding up all the pertinent Rational Method upstream C*As and using the maximum upstream Tc to calculate an intensity. (It has a feature to add pipe travel time to Tc which has been disabled for theoretical reasons since the networks I analyze typically are submerged and have essentially zero flood wave travel time--what goes in one hole must simultaneously pop out another.) It uses an equation to calculate intensity, and has a page for deriving the variables for that equation. It has two main pages, an input data page, and a calculation page. The main table of the calculation page contains strictly formulas so that changes can be made and formulas copied without data loss. Below the table are notes and constant values.
If you have read this far, you are not faint of heart. This spreadsheet is not for the faint of heart. I built it before 1995 for myself and have used it steadily and exclusively in one form or another since then. It has journeyed from Quattro Pro for DOS through the various Windows versions of Quattro Pro and Excel, and it now resides in OpenOffice.org. Each spreadsheet program has its own nuances, and this spreadsheet is tricky enough to get tripped up by all of them, and that is why I am not offering it right now in Excel format, not to mention that OpenOffice.org is freely available for download, and has a better spreadsheet, in my humble opinion, than Excel.
To avoid breaking the spreadsheet, never move or delete the top row of the input or the calculation page! I usually stay a few rows away (blame it on bad memories) just to be safe when I add or delete rows on either page.
Also never add or delete columns from the calculation table. I don't know for sure that today's spreadsheets are yet capable of adjusting VLOOKUP functions for that.
I always start by numbering the "lines" or "concentration points" of the network. Because lines and concentration points are not the same animal, this may not make immediate sense, but in working with this spreadsheet, you have to think economy. I suggest downloading and opening the key map for illustration. Every network number in this spreadsheet applies both 1) to a line between nodes and 2) to the manhole, inlet, or concentration point at the upstream end of that line. This requires that every manhole/node have only one line leaving it, and that is why this spreadsheet is for tributary networks. If there were two lines leaving a manhole, it would be a distributary network. If you can model (or play pretend with) your networks as tributary networks, this spreadsheet may work for you.
I usually assign a letter to each outfall (usually a retention basin in Mesa/Gilbert). Then I assign a "major" number series to each storm drain network that flows into that outfall. Let's say Basin E and major series 300. Then I start numbering up the network, always following the shortest branches first, then doubling back to catch the longer branches. Sometime I skip numbers to use minor series for the biggest branches of the network (like the E330s and the E360s). See the key map at the top of this page for illustration. Then I put all the lines into the input page of the spreadsheet starting low and working my way up to the top, or starting at the top with the highest number and working my way down (it's easier the first way).
I model on-grade inlet connectivity by putting the number of any single upstream inlet that might shed its bypass as carryover flow for an inlet. Every line needs either the word NONE or an inlet (line) number in this input column.
Automatic on-grade inlet bypass accounting only works in this spreadsheet if the surface flow runs in the same direction as the storm drain flow. If they run in opposite directions, as is the case for lines B204 and B212 on the PDF key map, you have to resort to manually typing in a cell reference for the carryover flow from an inlet that is "below" in the spreadsheet instead of above where the spreadsheet can automatically search.
I probably should write more, and I will if this turns out to be a useful thing to share. Please contact me if you find this useful, and let me know if I need to explain further.
To submit revisions, send an email with your revised code.
This program is free software under the terms of the GNU (GNU--acronym for Gnu's Not Unix--sounds like canoe) General Public License as published by the Free Software Foundation, version 2 of the License.
You can redistribute this software for any fee or no fee and/or modify it in any way, but it and ANY MODIFICATIONS OR DERIVATIONS continue to be governed by the license, which protects the perpetual availability of the software for free distribution and modification.
You CAN'T put this code into any proprietary package. Read the license.
If you improve this software, please make a revision submittal to the copyright owner at hawstom@despammed.com or see www.hawsedc.com.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License on the World Wide Web for more details..