BMP Calculations¶
Sheet visibility: visible.
Stored values and formulas are shown below. Cached formula results are source snapshots and have not been recalculated.
| Cell | Stored value or cached result | Formula |
|---|---|---|
| A1 | Project Name | |
| B1 | ||
| C1 | Description | |
| D1 | ||
| E1 | ||
| F1 | Analysis Date | |
| G1 | ||
| H1 | BMP or LID Type | |
| I1 | ||
| J1 | ||
| K1 | ||
| L1 | ||
| M1 | ||
| N1 | ||
| O1 | ||
| P1 | ||
| Q1 | ||
| R1 | ||
| S1 | ||
| T1 | ||
| U1 | ||
| V1 | ||
| W1 | ||
| X1 | ||
| Y1 | ||
| Z1 | ||
| AA1 | 0 | =+C14-G14 |
| A2 | Sample Project | =+'Annual Recharge'!K1 |
| B2 | ||
| C2 | This is a test application | =+'Annual Recharge'!K2 |
| D2 | ||
| E2 | ||
| F2 | 37865 | =+'Annual Recharge'!K3 |
| G2 | ||
| H2 | ||
| I2 | ||
| J2 | ||
| K2 | ||
| L2 | ||
| M2 | ||
| N2 | ||
| O2 | ||
| P2 | ||
| Q2 | ||
| R2 | ||
| S2 | ||
| T2 | ||
| U2 | ||
| V2 | ||
| W2 | ||
| X2 | ||
| Y2 | ||
| Z2 | ||
| AA2 | ||
| A3 | Recharge BMP Input Parameters | |
| B3 | ||
| C3 | ||
| D3 | ||
| E3 | Root Zone Water capacity Calculated Parameters | |
| F3 | ||
| G3 | ||
| H3 | ||
| I3 | Recharge Design Parameters | |
| J3 | ||
| K3 | ||
| L3 | ||
| M3 | ||
| N3 | ||
| O3 | ||
| P3 | ||
| Q3 | ||
| R3 | ||
| S3 | ||
| T3 | ||
| U3 | ||
| V3 | ||
| W3 | ||
| X3 | ||
| Y3 | ||
| Z3 | ||
| AA3 | ||
| A4 | Parameter | |
| B4 | Symbol | |
| C4 | Value | |
| D4 | Unit | |
| E4 | Parameter | |
| F4 | Symbol | |
| G4 | Value | |
| H4 | Unit | |
| I4 | Parameter | |
| J4 | Symbol | |
| K4 | Value | |
| L4 | Unit | |
| M4 | ||
| N4 | ||
| O4 | ||
| P4 | ||
| Q4 | ||
| R4 | ||
| S4 | ||
| T4 | ||
| U4 | ||
| V4 | ||
| W4 | ||
| X4 | ||
| Y4 | ||
| Z4 | ||
| AA4 | ||
| A5 | BMP Area | |
| B5 | ABMP | |
| C5 | 6656 | |
| D5 | sq.ft | |
| E5 | Empty Portion of RWC under Post-D Natural Recharge |
|
| F5 | ERWC | |
| G5 | 0.9264395961538462 | =+IF(SegBMP=0,ERWC,(1-0.5C_factor)'BMP Calculations'!C16) |
| H5 | in | |
| I5 | Inches of Runoff to capture |
|
| J5 | Qdesign | |
| K5 | 0.5397002023064793 | =+VLOOKUP(MATCH(MAX('event calc table'!$G$3:$G$81),'event calc table'!$G$3:$G$81,0),'event calc table'!$A$2:$D$81,4) |
| L5 | in | |
| M5 | ||
| N5 | ||
| O5 | ||
| P5 | ||
| Q5 | ||
| R5 | ||
| S5 | ||
| T5 | ||
| U5 | ||
| V5 | ||
| W5 | ||
| X5 | ||
| Y5 | ||
| Z5 | ||
| AA5 | ||
| A6 | BMP Effective Depth, this is the design variable |
|
| B6 | dBMP | |
| C6 | 5.179003184432713 | |
| D6 | in | |
| E6 | ERWC Modified to consider dEXC |
|
| F6 | EDRWC | |
| G6 | 0.9264395961538462 | =+IF(SegBMP=0,EDRWC,(1-0.5C_factor)'BMP Calculations'!C17) |
| H6 | in | |
| I6 | Inches of Rainfall to capture |
|
| J6 | Pdesign | |
| K6 | 0.672028306791204 | =+VLOOKUP(MATCH(MAX('event calc table'!$G$3:$G$81),'event calc table'!$G$3:$G$81,0),'event calc table'!$A$2:$C$81,3) |
| L6 | in | |
| M6 | ||
| N6 | ||
| O6 | ||
| P6 | ||
| Q6 | ||
| R6 | ||
| S6 | ||
| T6 | ||
| U6 | ||
| V6 | ||
| W6 | ||
| X6 | ||
| Y6 | ||
| Z6 | ||
| AA6 | ||
| A7 | Upper level of the BMP surface (negative if above ground) |
|
| B7 | dBMPu | |
| C7 | -5.2 | |
| D7 | in | |
| E7 | Empty Portion of RWC under Infilt. BMP |
|
| F7 | RERWC | |
| G7 | 0.7350024335462353 | =+('event calc table'!$A$81/365)'event calc table'!F83+((365-'event calc table'!$A$81)/365)G6 |
| H7 | in | |
| I7 | Recharge Provided Avg. over Imp. Area |
|
| J7 | ||
| K7 | 18.996236666666668 | =+'event calc table'!H84 |
| L7 | in | |
| M7 | ||
| N7 | ||
| O7 | ||
| P7 | ||
| Q7 | ||
| R7 | ||
| S7 | ||
| T7 | ||
| U7 | ||
| V7 | ||
| W7 | ||
| X7 | ||
| Y7 | ||
| Z7 | ||
| AA7 | ||
| A8 | Depth of lower surface of BMP, must be>=dBMPu | |
| B8 | dEXC | |
| C8 | 0 | |
| D8 | in | |
| E8 | ||
| F8 | ||
| G8 | ||
| H8 | ||
| I8 | Runoff Captured Avg. over imp. Area |
|
| J8 | ||
| K8 | 24.774702750770814 | =+K7/G15 |
| L8 | in | |
| M8 | ||
| N8 | ||
| O8 | ||
| P8 | ||
| Q8 | ||
| R8 | ||
| S8 | ||
| T8 | ||
| U8 | ||
| V8 | ||
| W8 | ||
| X8 | ||
| Y8 | ||
| Z8 | ||
| AA8 | ||
| A9 | Post-development Land Segment Location of BMP , Input Zero if Location is distributed or undetermined |
|
| B9 | SegBMP | |
| C9 | 0 | |
| D9 | unitless | |
| E9 | ||
| F9 | ||
| G9 | ||
| H9 | ||
| I9 | ||
| J9 | ||
| K9 | ||
| L9 | ||
| M9 | ||
| N9 | ||
| O9 | ||
| P9 | ||
| Q9 | ||
| R9 | ||
| S9 | ||
| T9 | ||
| U9 | ||
| V9 | ||
| W9 | ||
| X9 | ||
| Y9 | ||
| Z9 | ||
| AA9 | ||
| A10 | ||
| B10 | ||
| C10 | ||
| D10 | ||
| E10 | BMP Calculated Size Parameters | |
| F10 | ||
| G10 | ||
| H10 | ||
| I10 | CALCULATION CHECK MESSAGES | |
| J10 | ||
| K10 | ||
| L10 | ||
| M10 | ||
| N10 | ||
| O10 | ||
| P10 | ||
| Q10 | ||
| R10 | ||
| S10 | ||
| T10 | ||
| U10 | ||
| V10 | ||
| W10 | ||
| X10 | ||
| Y10 | ||
| Z10 | ||
| AA10 | ||
| A11 | ||
| B11 | ||
| C11 | ||
| D11 | ||
| E11 | ABMP/Aimp | |
| F11 | Aratio | |
| G11 | 0.10186715641261096 | =+ABMP/$C$15 |
| H11 | unitless | |
| I11 | Volume Balance-> | |
| J11 | OK | =IF(ABS(G14-C14)<0.001,"OK","Solve Problem to satisfy Annual Recharge") |
| K11 | ||
| L11 | ||
| M11 | ||
| N11 | ||
| O11 | ||
| P11 | ||
| Q11 | ||
| R11 | ||
| S11 | ||
| T11 | ||
| U11 | ||
| V11 | ||
| W11 | ||
| X11 | ||
| Y11 | ||
| Z11 | ||
| AA11 | ||
| A12 | ||
| B12 | ||
| C12 | ||
| D12 | ||
| E12 | BMP Volume | |
| F12 | VBMP | |
| G12 | 2872.620432965345 | =+ABMP*(dBMP/12) |
| H12 | cu.ft | |
| I12 | dBMP Check---> | |
| J12 | OK | =IF(dBMP<=dEXC-dBMPu,"OK", "dBMP must be<=dEXC-dBMPu, adjust parameters") |
| K12 | ||
| L12 | ||
| M12 | ||
| N12 | ||
| O12 | ||
| P12 | ||
| Q12 | ||
| R12 | ||
| S12 | ||
| T12 | ||
| U12 | ||
| V12 | ||
| W12 | ||
| X12 | ||
| Y12 | ||
| Z12 | ||
| AA12 | ||
| A13 | Parameters from Annual Recharge Worksheet | |
| B13 | ||
| C13 | ||
| D13 | ||
| E13 | System Performance Calculated Parameters | |
| F13 | ||
| G13 | ||
| H13 | ||
| I13 | dEXC Check---> | |
| J13 | OK | =IF(dEXC>dBMPu,"OK","make dEXC larger than dBMPu") |
| K13 | ||
| L13 | ||
| M13 | ||
| N13 | ||
| O13 | ||
| P13 | ||
| Q13 | ||
| R13 | ||
| S13 | ||
| T13 | ||
| U13 | ||
| V13 | ||
| W13 | ||
| X13 | ||
| Y13 | ||
| Z13 | ||
| AA13 | ||
| A14 | Post-D Deficit Recharge (or desired recharge volume) |
|
| B14 | Vdef | |
| C14 | 103434.50864999997 | =+Vdef |
| D14 | cu.ft | |
| E14 | Annual BMP Recharge Volume |
|
| F14 | ||
| G14 | 103434.50865000003 | =+('event calc table'!G84/12)*ABMP |
| H14 | cu.ft | |
| I14 | BMP Location---> | |
| J14 | Location is selected as distributed or undetermined | =IF(SegBMP=0,"Location is selected as distributed or undetermined",IF(VLOOKUP(SegBMP,'Annual Recharge'!H6:I20,2)=0,"Land Segment Number Selected for BMP is not Defined", "OK")) |
| K14 | ||
| L14 | ||
| M14 | ||
| N14 | ||
| O14 | ||
| P14 | ||
| Q14 | ||
| R14 | ||
| S14 | ||
| T14 | ||
| U14 | ||
| V14 | ||
| W14 | ||
| X14 | ||
| Y14 | ||
| Z14 | ||
| AA14 | ||
| A15 | Post-D Impervious Area (or target Impervious Area) |
|
| B15 | Aimp | |
| C15 | 65340 | =+Aimp |
| D15 | sq.ft | |
| E15 | Avg BMP Recharge Efficiency |
|
| F15 | ||
| G15 | 0.7667594181760925 | =+'event calc table'!G84/'event calc table'!E84 |
| H15 | Represents % Infiltration Recharged |
|
| I15 | OTHER NOTES | |
| J15 | ||
| K15 | ||
| L15 | ||
| M15 | ||
| N15 | ||
| O15 | ||
| P15 | ||
| Q15 | ||
| R15 | ||
| S15 | ||
| T15 | ||
| U15 | ||
| V15 | ||
| W15 | ||
| X15 | ||
| Y15 | ||
| Z15 | ||
| AA15 | ||
| A16 | Root Zone Water Capacity | |
| B16 | RWC | |
| C16 | 3.9422961538461543 | =+IF(SegBMP=0,RWC,VLOOKUP(SegBMP,'Annual Recharge'!AA6:AL20,12)) |
| D16 | in | |
| E16 | %Rainfall became Runoff |
|
| F16 | ||
| G16 | 0.7828979082660678 | =+'event calc table'!D84/Pavg |
| H16 | % | |
| I16 | Pdesign is accurate only after BMP dimensions are updated to make rech volume= deficit volume. The portion | |
| J16 | ||
| K16 | ||
| L16 | ||
| M16 | ||
| N16 | ||
| O16 | ||
| P16 | ||
| Q16 | ||
| R16 | ||
| S16 | ||
| T16 | ||
| U16 | ||
| V16 | ||
| W16 | ||
| X16 | ||
| Y16 | ||
| Z16 | ||
| AA16 | ||
| A17 | RWC Modified to consider dEXC |
|
| B17 | DRWC | |
| C17 | 3.9422961538461543 | =+IF(SegBMP=0,DRWC,VLOOKUP(SegBMP,'Annual Recharge'!AA6:AO20,15)) |
| D17 | in | |
| E17 | %Runoff Infiltrated |
|
| F17 | ||
| G17 | 0.6620265749276713 | =+'event calc table'!E84*Aratio/'event calc table'!D84 |
| H17 | % | |
| I17 | of BMP infiltration prior to filling and the area occupied by BMP are ignored in these calculations. Results are | |
| J17 | ||
| K17 | ||
| L17 | ||
| M17 | ||
| N17 | ||
| O17 | ||
| P17 | ||
| Q17 | ||
| R17 | ||
| S17 | ||
| T17 | ||
| U17 | ||
| V17 | ||
| W17 | ||
| X17 | ||
| Y17 | ||
| Z17 | ||
| AA17 | ||
| A18 | Climatic Factor | |
| B18 | C-factor | |
| C18 | 1.53 | =+C_factor |
| D18 | no units | |
| E18 | %Runoff Recharged |
|
| F18 | ||
| G18 | 0.5076151114086526 | =+G14/(('event calc table'!D84/12)*Aimp) |
| H18 | % | |
| I18 | sensetive to dBMP, make sure dBMP selected is small enough for BMP to empty in less than 3 days. For land | |
| J18 | ||
| K18 | ||
| L18 | ||
| M18 | ||
| N18 | ||
| O18 | ||
| P18 | ||
| Q18 | ||
| R18 | ||
| S18 | ||
| T18 | ||
| U18 | ||
| V18 | ||
| W18 | ||
| X18 | ||
| Y18 | ||
| Z18 | ||
| AA18 | ||
| A19 | Average Annual P | |
| B19 | Pavg | |
| C19 | 47.8 | =+Pavg |
| D19 | in | |
| E19 | %Rainfall Recharged |
|
| F19 | ||
| G19 | 0.39741080892608105 | =+G14/((Pavg/12)*Aimp) |
| H19 | % | |
| I19 | Segment Location of BMP if you select "impervious areas" RWC will be minimal but not zero as determined by | |
| J19 | ||
| K19 | ||
| L19 | ||
| M19 | ||
| N19 | ||
| O19 | ||
| P19 | ||
| Q19 | ||
| R19 | ||
| S19 | ||
| T19 | ||
| U19 | ||
| V19 | ||
| W19 | ||
| X19 | ||
| Y19 | ||
| Z19 | ||
| AA19 | ||
| A20 | Recharge Requirement over Imp. Area |
|
| B20 | dr | |
| C20 | 18.99623666666666 | =12*C14/Aimp |
| D20 | in | |
| E20 | ||
| F20 | ||
| G20 | ||
| H20 | ||
| I20 | the soil type and a shallow root zone for this Land Cover allowing consideration of lateral flow and other losses. | |
| J20 | ||
| K20 | ||
| L20 | ||
| M20 | ||
| N20 | ||
| O20 | ||
| P20 | ||
| Q20 | ||
| R20 | ||
| S20 | ||
| T20 | ||
| U20 | ||
| V20 | ||
| W20 | ||
| X20 | ||
| Y20 | ||
| Z20 | ||
| AA20 | ||
| A21 | How to solve for different recharge volumes: By default the spreadsheet assigns the values of total deficit recharge volume "Vdef" and total proposed impervious area "Aimp" from the "Annual Recharge" sheet to "Vdef" and "Aimp" on this page. This allows solution for a single BMP to handle the entire recharge requirement assuming the runoff from entire impervious area is available to the BMP. To solve for a smaller BMP or a LID-IMP to recharge only part of the recharge requirement, set Vdef to your target value and Aimp to impervious area directly connected to your infiltration facility and then solve for ABMP or dBMP. To go back to the default configuration clik the "Default Vdef & Aimp" button. |
|
| B21 | ||
| C21 | ||
| D21 | ||
| E21 | ||
| F21 | ||
| G21 | ||
| H21 | ||
| I21 | ||
| J21 | ||
| K21 | ||
| L21 | ||
| M21 | ||
| N21 | ||
| O21 | ||
| P21 | ||
| Q21 | ||
| R21 | ||
| S21 | ||
| T21 | ||
| U21 | ||
| V21 | ||
| W21 | ||
| X21 | ||
| Y21 | ||
| Z21 | ||
| AA21 | ||
| A22 | ||
| B22 | ||
| C22 | ||
| D22 | ||
| E22 | ||
| F22 | ||
| G22 | ||
| H22 | ||
| I22 | ||
| J22 | ||
| K22 | ||
| L22 | ||
| M22 | ||
| N22 | ||
| O22 | ||
| P22 | ||
| Q22 | ||
| R22 | ||
| S22 | ||
| T22 | ||
| U22 | ||
| V22 | ||
| W22 | ||
| X22 | ||
| Y22 | ||
| Z22 | ||
| AA22 | ||
| A23 | ||
| B23 | ||
| C23 | ||
| D23 | ||
| E23 | ||
| F23 | ||
| G23 | ||
| H23 | ||
| I23 | ||
| J23 | ||
| K23 | ||
| L23 | ||
| M23 | ||
| N23 | ||
| O23 | ||
| P23 | ||
| Q23 | ||
| R23 | ||
| S23 | ||
| T23 | ||
| U23 | ||
| V23 | ||
| W23 | ||
| X23 | ||
| Y23 | ||
| Z23 | ||
| AA23 |