Plumbing Formulas for Excel

The MAX wrapper is the part every spreadsheet on the internet leaves out. A two-bath house is 18 DFU, which the building-drain table carries on 2 in pipe — but a water closet floors a building drain at 3 in regardless. Note the floor applies to the building drain, not to a horizontal branch. Use the branch column for branches and the stack columns for stacks; a tall stack legitimately carries more than a short one.

Returns inches per foot. IPC 2021 has only two rows that matter in a house — 1/4 in/ft up to 2-1/2 in, 1/8 in/ft from 3 to 6 in — so the nested IF is honestly the whole table. Piping upstream of a grease interceptor stays at 1/4 in/ft whatever the size, which the formula does not know about; add that condition yourself if it applies.

The fall cell is the one that decides whether a run clears a footing or fits a joist bay. 1/4 in/ft is 2.08% and 1/8 in/ft is 1.04%. The same 40 ft run drops 10.0 in at 1/4 and 5.0 in at 1/8 — which is usually why a long run goes up a size.

Two cells, because you want the velocity as well as the flow. B2/48 is the hydraulic radius of a half-full pipe in feet — diameter over 4, then inches to feet. The PI()/8 term is half of a full circle's area. Note the exponent needs its parentheses: ^(2/3) is a cube root of a square, while ^2/3 is a square divided by three, and Excel will not warn you. 448.831 converts cubic feet per second to gpm; it is the one constant here we carry rounded rather than as 60 × 7.48052, so that a sheet lands on exactly what the calculator prints. Watch the velocity cell rather than the gpm one: below about 2 ft/s solids drop out, which is why oversizing a drain at a flat grade can make it worse rather than better.

Half the drain diameter, floored at 1.25 in, rounded up to a size you can actually buy — then one size larger again past 40 ft of developed length. The 1.25 in floor only ever binds at 2 in drains and below; at 3 in and up the halving already clears it. The 40 ft rule upsizes the whole run, not just the excess, which is the part the MATCH offset is doing.

An exact MATCH, not an approximate one — a trap arm is a listed size, so a 0 as the third argument is correct here and a 1 would silently return the wrong row for an in-between value. There is a floor as well as a ceiling: the vent must also sit at least two pipe diameters downstream of the trap weir. Self-siphoning fixtures, water closets above all, are not length-limited at all.

Two devices, two units, and sizing one with the other's method gives a plausible number that means nothing. The hydromechanical trap is rated in gpm; the gravity interceptor is rated in gallons. PDI-G101 defines grease capacity as exactly 2 lb per gpm, so that cell is a definition rather than a lookup. The MAX against 1,000 gal matters more than the arithmetic — below roughly 65 seats the local FOG minimum governs and the calculation never binds.

Four cells rather than one, because the first flush comes off the gross yield BEFORE the coefficients are applied — divert the dirtiest water first, then lose a share of what is left to the screen and the filter. Doing it in the other order, or skipping it, overstates the yield by 2.8% on this roof. Write the constant as 7.48052/12 rather than the 0.62 you will see quoted everywhere — it is one cubic foot of water spread an inch deep over a square foot, and letting Excel do the division keeps the full precision. Area is the horizontal projection of the roof, not its slope length, because rain falls vertically. The final MIN is the whole design: a tank bigger than the roof can refill just sits part empty.

Runoff is strictly linear in area and rainfall, so 41.56 gpm per 1,000 ft² at 4 in/hr scales exactly — the one place a per-square-foot rule of thumb is safe. The capacity line is the same Manning equation as the drain formula above but at FULL bore rather than half, which is why the area term is PI()/4 rather than PI()/8. This publishes the flow, not the code size: IPC Tables 1106.2 and 1106.3 govern leader and horizontal storm drain sizing and are deliberately not reproduced, the same call made for the vent-stack table.

The MAX is the whole tank calculation: on ordinary numbers the published minimum governs up to four bedrooms and retention only takes over at five, so a sheet that computes retention alone undersizes almost every house it is used on. The soil rate is the real variable — the identical house needs 375 ft² of field in sand and 1,875 ft² in slow clay, a 5x swing, while the tank does not move at all. Septic is not IPC territory: it falls to the IPSDC where adopted and to state and county health departments everywhere else, so treat every input here as a starting value and confirm the local ones. Setbacks, water table and a witnessed perc test are out of scope.

The storage line looks pointless and is the most important one here: the hold is 24 hours, so maximum storage always equals exactly one day of yield. Untreated grey water goes septic past a day, so unlike a rainwater cistern it cannot bridge between events at all — it is a surge vessel, and the design work moves to the distribution field. The washer divides by 7 because it is per load per week while the others are per person per day; mixing those two bases is the easy error. Planting choice swings the area 4x, from 608 ft² of cool-season lawn to 2,433 ft² of drought-tolerant planting. Kitchen sink and dishwasher are deliberately absent — that is black water in most jurisdictions.