This is a complicated question to ask and I am not sure exactly how to ask it. I am attempting to make a pipe sizing tool to speed up one of my work processes. The procedure is pretty straight forward. Do some math to figure out GPM going to drain (done elsewhere) and then look up the pipe size required for the pipe slope specified. We use the table below (Plumbing Code).
I need to have the spreadsheet pick the appropriate column for pipe slope (typically 1/8" per foot), and then use the MATCH -1 function to pick the first row that is greater than the required GPM capacity calculated elsewhere in the spreadsheet. From there, it needs to index and pick out the pipe size.
I am not sure how to tell MATCH to pick a specific array to match to based on a dynamic input (pipe slope). Any suggestions?
<colgroup><col span="3"><col><col span="3"></colgroup><tbody>
</tbody>
I need to have the spreadsheet pick the appropriate column for pipe slope (typically 1/8" per foot), and then use the MATCH -1 function to pick the first row that is greater than the required GPM capacity calculated elsewhere in the spreadsheet. From there, it needs to index and pick out the pipe size.
I am not sure how to tell MATCH to pick a specific array to match to based on a dynamic input (pipe slope). Any suggestions?
| Table 1106.2 | ||||||
| Storm Drain Pipe Sizing | ||||||
| 1 | 2 | 3 | 4 | 5 | ||
| Capacity (GPM) | ||||||
| Pipe Size (inches) | Slope of Horizontal Drain | |||||
| Vertical | 1/16" per foot | 1/8" per foot | 1/4" per foot | 1/2" per foot | ||
| 9 | 15" | 5543 | 2508 | 3546 | 5016 | 7093 |
| 8 | 12" | 3272 | 1480 | 2093 | 2960 | 4187 |
| 7 | 10" | 2050 | 927 | 1311 | 1855 | 2623 |
| 6 | 8" | 1117 | 505 | 714 | 1010 | 1429 |
| 5 | 6" | 538 | 243 | 344 | 487 | 689 |
| 4 | 5" | 311 | 117 | 165 | 234 | 331 |
| 3 | 4" | 180 | 81 | 115 | 163 | 231 |
| 2 | 3" | 87 | 39 | 55 | 79 | 111 |
| 1 | 2" | 34 | 15 | 22 | 31 | 44 |
<colgroup><col span="3"><col><col span="3"></colgroup><tbody>
</tbody>
