Plumbing Formulas for Google Sheets
All 35 formulas on our Excel reference paste into Google Sheets unchanged, so
this page is not those. It is the 17 things you would build differently because you are in Sheets — one formula governing a whole column instead of one per cell,
QUERY instead of a pivot
table, and 7 named functions that turn a 189-character
interpolation into =WSFU_TO_GPM(B2).
Every constant below is pulled from the same code that runs our calculators, so a formula you paste returns the number the calculator returns. 7 of the functions used here have no Excel equivalent at all, and 4 things Excel can do are genuinely missing from Sheets — both lists are below, checked against Google's own function reference rather than assumed. The 14 paste-in tables carry 252 rows of code data between them.
Read this before you paste anything: every formula here is written for a sheet whose locale uses a full stop as the decimal separator. If yours uses a comma, Sheets changes two things — the argument separator becomes a semicolon, and the column separator inside curly-brace array literals becomes a backslash while the row separator stays a semicolon. Nothing else changes, but a formula pasted into the wrong locale fails with a parse error rather than a wrong answer, which is at least honest of it. Check under File → Settings → Locale.
Set up your workbook first
The Excel reference asks you to lay out cells. This page asks you to lay out tabs, because nothing here lives in a single cell — each build occupies one cell and governs an entire column, and every one of them reads a pasted code table by sheet name. Create these ten tabs, paste the tables from further down, and every formula on this page works without editing a single reference.
| Tab | What it holds |
|---|---|
| Schedule | The fixture schedule — one row per fixture type per room |
| Branches | One row per branch or riser: flow, material, size, and the computed columns |
| Pipe | Pasted pipe dimensions and C factors |
| WSFU | Pasted supply fixture units — also the dropdown source |
| DFU | Pasted drainage fixture units and trap sizes |
| Hunter | Pasted Hunter's curve, for WSFU_TO_GPM |
| Drain | Pasted building drain capacity, for DRAIN_SIZE |
| Vent | Pasted stocked vent sizes, for VENT_SIZE |
| Limits | The velocity ceilings and code minimums every formatting rule points at |
| Rates | Labour and material rates, or an IMPORTRANGE of the master |
One habit worth forming: put the formula in row 2 and the heading in row 1, and
write the range as B2:B with no end
row. An open-ended range is what makes the build cover rows that do not exist yet, which is the
entire advantage over filling down. Sheets caps a spreadsheet at ten million cells, so leaving
columns open-ended costs you nothing you will notice on a fixture schedule.
This is the whole difference. In Excel you write a formula and fill it down, and from that moment the sheet has as many copies of your logic as it has rows — any one of which somebody can overtype without leaving a mark. In Sheets one formula in the header row governs the entire column below it, including rows that do not exist yet. There is one copy of the logic, in one cell, and adding a fixture to the schedule cannot break it.
Fixture Schedule → WSFU on Every Row
Needs a table: WSFU fixture schedule- Goes in
- Schedule · D2
- D2:D — every row, now and later
- A
- Room or branch Hall bath
- B
- Fixture, from a dropdown Lavatory, private
- C
- Count 3
- D
- WSFU — the one formula →
8 fixture rows → 18.5 WSFU total → 19.00 gpm peak demand on flush tanks, 33.80 gpm on flushometer valves
The IF(B2:B="",,…) wrapper is what keeps the column clean — the double comma returns a genuinely blank cell rather than a zero or an empty string, so SUM(D2:D) and QUERY both behave. This is the one XLOOKUP shape that vectorises reliably: a vertical search key against a SINGLE-column return range. Give it a multi-column return range and it quietly hands back only the first column, which is the most common way an ARRAYFORMULA build goes wrong. Join on the fixture label in column B of the WSFU tab rather than the internal key in column A, because a dropdown then guarantees the spelling and the sheet stays readable to whoever inherits it. Note the totals column is the code's own, not cold plus hot — IPC assigns a fixture's total weight rather than adding the two, and a sheet that adds them overstates the load.
One Schedule, Two Code Tables That Disagree
Needs a table: Fixture drainage units — IPC Table 709.1- Goes in
- Schedule · F2
- F2:F alongside the WSFU column
- B
- Supply fixture, from the WSFU tab Water closet, private, flush tank
- E
- Drainage fixture — a SECOND column, from the DFU tab Water closet, private (1.6 gpf)
- F
- DFU — the one formula →
The same house: 18.5 WSFU on the supply side, 19 DFU on the drainage side, from 7 drainage rows
The two IPC tables do not name the same fixture the same way, and this is the quietest way a plumbing spreadsheet goes wrong. Exactly 1 of the 28 fixtures in Table 709.1 is spelled identically in the 25-row supply table — "Drinking fountain". A water closet is "Water closet, private, flush tank" on the supply side, because what matters is how it is flushed; the same fixture is "Water closet, private (1.6 gpf)" on the drainage side, because what matters is how much it discharges. So one fixture column cannot drive both lookups — and IFERROR(…,0) will cheerfully contribute zero DFU for every row named the supply way, giving you a total that looks plausible and is not. Two fixture columns, or an explicit mapping tab, and never one. While you are building, wrap with IFNA instead of IFERROR so the misses show up as errors rather than as zeros.
Bore, Velocity and Friction Across the Whole Branch Schedule
Needs a table: Pipe dimensions and C factors- Goes in
- Branches · E2, F2, G2
- three columns, each from one cell
- A
- Branch Riser 1 — 2nd floor
- B
- Design gpm 12
- C
- Material key copper-l
- D
- Nominal size 0.75
- E
- Bore, inches →
- F
- Velocity, ft/s →
- G
- psi per 100 ft →
12 gpm through 3/4" Type L copper: bore 0.785 in → 7.95 ft/s → 15.53 psi per 100 ft. The same flow through 3/4" PEX (bore 0.681 in) runs at 10.58 ft/s
Three columns, three cells, because a multi-column XLOOKUP return does not vectorise — asking for bore and C factor in one call gets you bore twice. The C2:C&"|"&D2:D trick builds a composite key elementwise inside the ARRAYFORMULA, which is how you look up on two columns without a helper column. Bore, never nominal. The velocity term squares it, so the 15% bore difference between 3/4" copper and 3/4" PEX becomes a 33% difference in velocity, which is the difference between passing and failing the 8 ft/s cold ceiling. Those ceilings — 8 ft/s cold, 5 ft/s hot — are ASPE and tube-manufacturer practice, not IPC figures.
Smallest Pipe That Passes Both Tests
Needs a table: Pipe dimensions and C factors- Goes in
- Branches · H2
- one cell per branch, or wrap in MAP for the column
- B
- Design gpm 19.00
- C
- Material key copper-l
- I
- Friction budget, psi/100 ft 23.62
19.00 gpm, Type L copper, 120 ft developed length, 28.3 psi available → budget 23.62 psi/100 ft → 1" at 7.39 ft/s and 9.92 psi/100 ft, governed by both
Sizing a water line is genuinely iterative — you guess a size, test it against both the velocity ceiling and the friction budget, and go again. This does the whole sweep in one cell: FILTER pulls the sizes stocked in that material, the two arithmetic lines compute velocity and loss for all of them at once, and multiplying the two comparisons is a logical AND across the arrays. INDEX(pass,1) takes the smallest because the pipe table is sorted ascending. Which of the two rules governs is worth surfacing — here it is both, and on a long run with low static it is nearly always friction, which is why sizing on velocity alone quietly under-sizes long branches.
Drain Size With the Water-Closet Floor, Applied to Every Row
Needs a table: Building drain capacity — IPC Table 710.1(1)- Goes in
- Schedule · H2
- H2:H
- F
- DFU on the run 19
- G
- Serves a water closet? TRUE
- H
- Minimum size — the one formula →
19 DFU at 1/4 in per ft: the table alone says 2" (capacity 21 DFU), but the run serves a water closet, so the answer is 3" — governed by the water-closet floor, not the table
Do not reach for MAX here. Inside an ARRAYFORMULA, MAX aggregates the entire array down to one number, so MAX(3,t) returns the largest drain in the whole schedule and writes it into every row. The elementwise form is IF(t<3,3,t). This is the single most useful behaviour to internalise about array formulas: anything that *reduces* — MAX, MIN, SUM, COUNT — collapses the array instead of walking it. The rule being applied is that no building drain serving a water closet may be smaller than 3 in regardless of what the DFU table permits, and the ,1 on the XLOOKUP is match_mode "exact or next larger", which is what a capacity lookup always wants.
A fixture schedule is only half the job — you need the load reaching each branch, riser and floor, and you need it to be right after somebody adds a bathroom. Excel's answer is a PivotTable, which is a snapshot: it does not refresh when a row appears, and half the sheets in the trade are quietly reporting last week's totals. QUERY is a formula, so it cannot be stale.
DFU Total by Branch, Floor or Riser
No table needed- Goes in
- Rollup · A1
- spills as far as the groups go
- Schedule!A
- Branch or floor Riser 1 — 2nd floor
- Schedule!F
- DFU per row 6
The example house rolls up to 19 DFU total, and each branch's subtotal updates the moment a fixture row is added
The trailing 0 says the range has no header row, which is why the range starts at row 2 and the label clause supplies the headings instead. where A is not null is what keeps the thousands of empty rows below your data out of the result. The trap worth knowing: QUERY infers one data type per column and nulls out the minority — so a column holding both 3 and 1-1/2" will silently drop one or the other. Keep sizes numeric in the schedule and render the fraction for display elsewhere, or wrap the column in TO_TEXT before it reaches QUERY.
Riser Load Straight Into Peak Demand
Needs a table: Hunter's curve — WSFU to GPM- Goes in
- Rollup · D1
- two columns, spilling
- Schedule!A
- Riser Riser 1
- Schedule!D
- WSFU per row 2.2
18.5 WSFU on the whole house → 19.00 gpm, and every riser's own gpm appears beside its WSFU without a single helper cell
This is the composition that has no Excel equivalent worth writing: a QUERY result piped through MAP into a named function you defined yourself, all in one cell. CHOOSECOLS(t,2) pulls the summed column out of the query result, MAP walks it, and HSTACK puts the two side by side. MAP rather than ARRAYFORMULA because WSFU_TO_GPM interpolates between anchors and branches internally — exactly the case where ARRAYFORMULA stops vectorising and starts returning the first row's answer for everything. Define WSFU_TO_GPM first; the named-function section below has it.
Trap Sizes for the Take-Off
Needs a table: Fixture drainage units — IPC Table 709.1- Goes in
- Rollup · G1
- two columns, spilling
- Schedule!E
- Drainage fixture Lavatory
- Schedule!C
- Count 3
The example house needs traps at 1-1/4", 1-1/2", 2" — the water closets trap themselves and correctly return nothing
The { } braces build a two-column array on the fly out of a computed column and a real one, which is how you QUERY over something that does not exist in the sheet. Column names become Col1, Col2 once you do that, not A and B. Water closets and bathroom groups have no trap-size entry because they are integral-trap fixtures, so they come back blank and where Col1 is not null drops them — which is the right answer, not a bug to patch. Locale note: inside { } the column separator is a comma in a US-format sheet and a backslash in a comma-decimal one.
Nobody in a van types 1.5. They type 1-1/2", or 1 1/2, or 1½, and the sheet has to cope. This is the one area where Sheets and Excel genuinely trade blows: Excel 365 has TEXTBEFORE, TEXTAFTER and TEXTSPLIT, which read beautifully for a fixed-shape split and which Sheets does not have at all. Sheets has SPLIT and the REGEX family, which Excel does not have at all, and which will handle input Excel's text functions cannot. Neither wins outright — but only one of them can parse a size somebody typed three different ways.
Text Pipe Size → Number: 1-1/2" Becomes 1.5
No table needed- Goes in
- Schedule · any
- one cell, or wrap in MAP for a column
- A2
- Size as typed 1-1/2"
Handles all three shapes the trade writes: 1-1/2" → 1.5, 3/4" → 0.75, 2" → 2, 2-1/2" → 2.5
SPLIT takes a *set* of delimiters, not one, so "-/" breaks on either character and the part count tells you which shape arrived: three parts is a mixed number, two is a bare fraction, one is a whole number. CHAR(34) rather than an escaped double quote keeps the formula pasteable without an editor mangling it. This is worth building once and never again — every code table on this page keys on the decimal, and every human types the fraction.
Pull Material and Size Out of One Typed Cell
No table needed- Goes in
- Schedule · two cells
- one cell each, or MAP for columns
- A2
- What somebody actually typed 3/4" copper L
`3/4" copper L` → material `copper l` and size `3/4`, which the parser above turns into 0.75 and a `Pipe` lookup turns into a 0.785 in bore
Normalise the material with LOWER and a space-or-hyphen class, then map it to the key your pipe table uses — do not try to make the regex emit copper-l directly, because the next person will type "Type L copper" and you will be editing the regex instead of the mapping table. The size pattern is anchored at the start and deliberately non-greedy about the fraction so 1-1/2 and 3/4 and 2 all match. Regex is the reason a messy schedule is salvageable in Sheets and a retyping job in Excel — worth remembering before anyone tells you the two are interchangeable.
Back the Other Way: 1.5 Becomes the Fraction the Trade Says
No table needed- Goes in
- Schedule · any
- one cell, or MAP for a column
- A2
- Decimal size 1.5
Reproduces our own size labels exactly: 0.75 → 3/4", 1.25 → 1-1/4", 1.5 → 1-1/2", 2 → 2", 2.5 → 2-1/2", 4 → 4"
The reason to compute the label rather than format the cell is that a number format changes what you see and not what QUERY sees — a fraction-formatted 1.5 is still numeric, which is usually what you want, but a printed take-off needs real text. Keep the decimal in one column and this in another, and never let the text column feed a lookup. The fraction list stops where our pipe tables stop; anything unlisted falls through to three decimal places rather than lying about being a fraction.
A spreadsheet that quietly returns a code violation is worse than no spreadsheet, because it has your name on it. None of what follows is a formula you paste into a cell — it is what a shared sheet does on its own: flags the row that fails, refuses the fixture that does not exist, and draws the velocity so you can see the outlier without reading a column of numbers.
Flag Every Branch Over the Velocity Ceiling
No table needed- Goes in
- Branches · Format > Conditional formatting
- apply to F2:F, custom formula
- F
- Velocity, ft/s 7.95
- Limits!B2
- Cold ceiling 8
- Limits!B3
- Hot ceiling 5
At 7.95 ft/s the example branch sits just under the 8 ft/s cold ceiling and stays unflagged — but the same 12 gpm in 3/4" PEX runs 10.58 ft/s and lights up, and on a hot line the ceiling drops to 5 ft/s and even the copper fails
A conditional-format custom formula cannot reference another sheet directly — this is a documented Sheets limitation and the single most common reason a rule silently never fires. INDIRECT with the sheet name as text is the way through, and it is worth the ugliness to keep the limits in one place rather than hard-coding 8 into a formatting rule where nobody will ever find it. Anchor the column with $F2 and leave the row unanchored so the rule walks down the range. The $F2<>"" guard matters because an empty cell compares as zero and would otherwise flag every unused row.
Flag Any Run Below Its Minimum Slope
No table needed- Goes in
- Runs · Format > Conditional formatting
- apply to D2:D, custom formula
- C
- Nominal size 2
- D
- Actual slope, in per ft 0.1875
A 2" run needs 0.25 in per ft (2.08%); a 3" run legally runs at 0.125 in per ft (1.04%), which is half the fall
The step down at 3" is the most consequential line in IPC Table 704.1 and the reason this has to be a formula rather than a single threshold: a 3" drain legally runs at half the fall a 2" drain needs, so one hard-coded number will either fail every large run or pass every shallow small one. IFS reads top-down and stops at the first TRUE, so the breakpoints must stay in ascending order. Note this checks the code minimum only — it says nothing about whether the run is *too* steep, which is a real failure mode on long 4" laterals where the liquid outruns the solids.
A Dropdown That Cannot Produce a Wrong Fixture
Needs a table: WSFU fixture schedule- Goes in
- Schedule · Data > Data validation
- apply to B2:B, dropdown from a range
- B
- Supply fixture (dropdown) Lavatory, private
- E
- Drainage fixture (a second dropdown) Lavatory
25 supply fixtures and 28 drainage fixtures to pick from, and a typo becomes impossible rather than becoming a zero
Two dropdowns, because the two code tables disagree about names — and this is what makes joining on the label safe rather than reckless. Both schedule builds above look the fixture up by its spelling, so a hand-typed "Lav" would fall through IFERROR and contribute nothing at all; a dropdown sourced from the table itself means the spelling cannot be wrong. Set each rule to reject invalid input rather than warn, or the guarantee is decorative. Note the two source columns are not the same letter: the supply table ships an internal key in A and the label in B, while Table 709.1 ships the label in A and no key at all.
Draw the Velocity in the Cell Beside It
No table needed- Goes in
- Branches · K2
- one cell per row — the one place you still fill down
- F
- Velocity, ft/s 7.95
- K
- Bar against the ceiling ▇▇▇▇▇▇▁
Every branch drawn against the same 8 ft/s ceiling, so 7.95 ft/s reads as nearly full and turns red the moment it crosses
SPARKLINE is the one function on this page that does not vectorise — wrap it in ARRAYFORMULA and you get a single chart, not a column of them, so this is the one place the whole page still tells you to fill down. Fixing max to the ceiling rather than letting it auto-scale is the entire point: an auto-scaled bar makes every row look equally full and hides the outlier you were looking for. The ; separates option rows and the , separates the pairs inside them — in a comma-decimal locale those become \ and ; respectively, which is the single most confusing locale difference in Sheets.
Every estimate you have ever sent has a labour rate buried in it, and the day that rate changes you find out how many copies of it exist. A shared sheet fixes this properly: the rates live in one file, every estimate reads them live, and last quarter's quote still shows last quarter's number because it was a value when you sent it. Excel can link workbooks; it cannot do it to a file somebody else has open.
Pull the Rate Master Into Every Job Sheet
No table needed- Goes in
- Rates · A1
- spills the whole imported block
- A
- Rate label Journeyman
- B
- Value 110
One file holds the rates; every job sheet reads them, and changing the journeyman rate once updates every open estimate
Paste the whole source URL the first time and Sheets will reduce it to the key for you, then click Allow access on the prompt — it appears once per source-destination pair and never again, which is why it seems to have failed the first time somebody else opens the file. Disclose this if the source is your price book: once access is granted, any editor on the destination spreadsheet can pull *any* range from the source, not just the one you named, and the grant counts against the source's sharing limit. Keep costs and rates in a file you are content for the whole company to read, and keep margins somewhere else.
Job Price at a Margin, Reading the Shared Rates
No table needed- Goes in
- Job · B7
- one cell, per job
- B2
- Labour hours 10
- B3
- Fixture cost $850
- B4
- Material cost $450
- B5
- Target margin, percent 25
direct $2,400 → break-even $2,760 → $3,680 at a 25% margin, $920 profit. The same 25% applied as a markup gives $3,450 — a 20.0% margin and $230 less in your pocket on identical work
XLOOKUP on a label rather than a cell reference is what makes the imported block safe to reorganise — insert a row in the rate master and a positional reference like Rates!$B$2 silently starts returning the wrong number, while a label lookup follows it. Margin divides by (1 − margin); markup multiplies by (1 + markup), and they are not the same operation: a 25% markup is always a 20% margin. For reference the loaded-rate side of the same arithmetic gives $74 per hour loaded and $123 to bill at a 40% margin, which is 3.24× the wage.
Define it once: 7 functions for the whole workbook
This is the part of Sheets with no comfortable Excel equivalent. Under Data → Named functions → Add new function you give a function a name, name its arguments, describe each one, and paste a formula definition. From then on it is a function like any built-in — it appears in autocomplete with your own description, and the ugliest formula on the Excel reference collapses from 189 characters plus a helper cell to a single readable call.
Excel 365 can approximate this by naming a
LAMBDA in Name Manager, but it
cannot describe the arguments and has no equivalent of
Import function, which pulls a definition straight out of last year's workbook.
Three rules to save you a confusing five minutes: a function name cannot match a built-in
(which is why the slope function below is not called SLOPE),
cannot start with a digit, and takes no special character but the underscore. A named
range also outranks a named function of the same name.
WSFU_TO_GPM(fu, control)
Needs a table: Hunter's curve — WSFU to GPMDescription Probable peak demand in gpm for a total water supply fixture unit load, read off Hunter's curve.
- fu
- Total WSFU on the run e.g. 18.5
- control
- "tank" for flush tanks, "valve" for flushometers e.g. "tank"
=WSFU_TO_GPM(18.5,"tank") → 19.00 gpm · =WSFU_TO_GPM(18.5,"valve") → 33.80 gpm
This is the case for named functions in one line. The Excel page has to write this as a 189-character expression *plus* a helper cell holding the MATCH, and repeat both on every sheet that needs it. Here it is one definition and =WSFU_TO_GPM(B2,"tank") forever. Three behaviours are worth keeping: the keep filter drops the blank rows at the top of the flushometer column, where the curve has no valve anchors below 5 FU; below the first anchor demand scales linearly from the origin rather than clamping; and above the last it flattens rather than extrapolating. Hunter's curve is a table, not an equation, so this cannot exist without the paste.
HW_LOSS(gpm, bore, cfac)
Description Hazen-Williams friction loss in psi per 100 ft, for water in a full pipe.
- gpm
- Flow, gallons per minute e.g. 12
- bore
- INSIDE diameter, inches — not nominal size e.g. 0.785
- cfac
- Hazen-Williams C for the material e.g. 140
=HW_LOSS(12,0.785,140) → 15.53 psi per 100 ft
The only argument anyone gets wrong is the second one. Passing 0.75 instead of the 0.785 in bore understates the loss badly, because the diameter carries an exponent of 4.87 — naming the placeholder bore rather than d is doing real work here, since the dialog shows that name to whoever uses the function. C is a material property, not a constant: Copper, Type L 140, Copper, Type M 140, PEX (SDR-9) 150, CPVC, Schedule 40 150, PVC, Schedule 40 150, Galvanized steel, Schedule 40 120.
PIPE_ID(material, nominal)
Needs a table: Pipe dimensions and C factorsDescription Inside diameter in inches for a material and nominal trade size.
- material
- Material key, e.g. copper-l or pex e.g. copper-l
- nominal
- Nominal trade size in inches, as a decimal e.g. 0.75
=PIPE_ID("copper-l",0.75) → 0.785 in · =PIPE_ID("pex",0.75) → 0.681 in
The composite-key concatenation is the whole trick — it looks up on two columns without a helper column, and it works inside a named function because the function body evaluates as an array expression. Every bore in the pasted table is computed as outside diameter minus twice the wall rather than transcribed, which is what makes the numbers self-checking: PEX at SDR-9 means wall equals OD over 9, and the computed values land on the published ASTM figures. Returning text rather than an error for an unstocked size keeps a schedule readable while it is half filled in.
MIN_SLOPE_INFT(size)
Description Minimum code slope in inches per foot for a horizontal drain of a given size, IPC Table 704.1.
- size
- Nominal drain size, inches e.g. 2
=MIN_SLOPE_INFT(2) → 0.25 in per ft · =MIN_SLOPE_INFT(3) → 0.125 · =MIN_SLOPE_INFT(8) → 0.0625
Named MIN_SLOPE_INFT and not SLOPE because SLOPE is a built-in statistical function and the dialog will reject it — the same goes for anything else colliding with a built-in, anything starting with a digit, and TRUE/FALSE. Worth knowing too that a named *range* takes precedence over a named function of the same name, which produces a genuinely baffling failure if you have a range called RATE and write a function called RATE. Underscores are the only special character allowed.
VENT_SIZE(drain, length)
Needs a table: Stocked vent sizesDescription Individual, common or branch vent size per IPC 906.2 — half the drain served, with the length upsize applied.
- drain
- Size of the drain being vented, inches e.g. 3
- length
- Developed length of the vent, feet e.g. 45
=VENT_SIZE(3,30) → 1-1/2" · =VENT_SIZE(3,45) → 2", upsized because 40 ft is passed
MATCH(TRUE, sizes>=half, 0) finds the first stocked size at or above half the drain — this works here, inside LET, precisely because a named function body evaluates as an array expression without needing ARRAYFORMULA. Three rules are stacked: not less than half the drain served, never below 1.25 in, and one nominal size up for the whole run once developed length passes 40 ft. This covers individual, common and branch vents only — a vent stack or stack vent serving a multi-storey drainage stack is sized by IPC Table 906.1, which is a different table and deliberately not this function.
DRAIN_SIZE(dfu, slope, has_wc)
Needs a table: Building drain capacity — IPC Table 710.1(1)Description Minimum building drain size for a DFU load at a given slope, with the water-closet floor applied.
- dfu
- Total drainage fixture units on the run e.g. 19
- slope
- Slope in inches per foot: 0.0625, 0.125, 0.25 or 0.5 e.g. 0.25
- has_wc
- TRUE if the run serves a water closet e.g. TRUE
=DRAIN_SIZE(19,0.25,TRUE) → 3", where the table alone would have said 2"
CHOOSECOLS picks the slope column, which is cleaner than four IFs and survives somebody widening the table. The ISNUMBER filter drops the blank cells — the shallow slopes are simply not permitted for small sizes rather than being an omission, and treating a blank as zero capacity would return a size the code forbids. Then the water-closet floor: no building drain serving a water closet may be under 3 in whatever the DFU table permits, which is the line most sheets skip. Multiplying the three conditions is a logical AND that keeps working if you later wrap the whole thing in MAP.
TDH_FT(lift, psi100, length, disch_psi, vel)
Description Total dynamic head in feet: static lift plus friction plus discharge pressure plus velocity head.
- lift
- Static lift, feet e.g. 12
- psi100
- Friction loss, psi per 100 ft e.g. 2.26
- length
- Developed length, feet e.g. 40
- disch_psi
- Required discharge pressure, psi e.g. 0
- vel
- Velocity in the discharge, ft/s e.g. 4.73
=TDH_FT(12,2.26,40,0,4.73) → 14.4 ft of head
Four terms in four different units, which is exactly why this belongs in a named function rather than being retyped per job. Dividing by 0.4331 psi per foot rather than multiplying by its reciprocal (2.3089) keeps the formula short and uses exactly the constant our calculators run on, and 64.348 is 2g with g at 32.174 ft/s². The velocity-head term is genuinely small — 0.35 ft here out of 14.4 — and people leave it out, which is fine on a sump and not fine on a booster set. Feet, not psi, because that is what a pump curve is drawn in.
What each one actually has
Checked function by function against Google's published function list, not assumed — 7 functions used on this page have no Excel counterpart, and 4 Excel entries are missing from Sheets. The table is about function names: where the same job ships under a different name, or needs a different version, the note says so. That matters more than the tick, and it is where most comparison tables on the internet quietly mislead.
| Function | Google Sheets | Excel | Notes |
|---|---|---|---|
| ARRAYFORMULA | Yes | No | The whole reason this page exists. Excel's nearest equivalent is a Table column formula, which still writes a copy into every row. |
| QUERY | Yes | No | SQL over a range. Excel needs a PivotTable, which does not refresh when you add a row, or the 365-only GROUPBY. |
| IMPORTRANGE | Yes | No | Live pull from another spreadsheet. Excel links to a workbook path or a Power Query connection instead. |
| SPARKLINE | Yes | No | An inline chart in a cell. Excel's sparklines are a chart object, not a function, so they cannot be driven by a formula. |
| REGEXEXTRACT / REGEXMATCH / REGEXREPLACE | Yes | No | No regex functions in Excel at all without VBA. This is the biggest single capability gap in Sheets' favour. |
| SPLIT | Yes | No | Excel 365 does the same job under the name TEXTSPLIT. Pre-365 Excel has neither. |
| SORTN | Yes | No | Top-n in one call. Excel 365 composes it as TAKE(SORT(...)). |
| Named functions | Yes | Yes | Both can do it — Sheets under Data > Named functions, Excel 365 by naming a LAMBDA in Name Manager. Only Sheets gives each argument a description and can Import a function from another file. |
| XLOOKUP | Yes | Yes | Full parity, match_mode and search_mode included. Excel needs 365 or 2021. |
| LAMBDA | Yes | Yes | Both. Excel needs 365. |
| MAP / REDUCE / SCAN / BYROW / BYCOL / MAKEARRAY | Yes | Yes | Both. Reach for these the moment per-row logic branches and ARRAYFORMULA stops vectorising. |
| LET | Yes | Yes | Both. What makes a long build readable instead of a helper-cell chain. |
| FILTER / SEQUENCE | Yes | Yes | Both. Excel needs 365. |
| TOCOL / TOROW / WRAPROWS | Yes | Yes | Both. |
| VSTACK / HSTACK | Yes | Yes | Both. In Sheets the older { } array-literal form does the same thing. |
| CHOOSECOLS / CHOOSEROWS | Yes | Yes | Both. Handy for picking a slope column out of a pasted code table. |
| TEXTJOIN | Yes | Yes | Both, back to Excel 2019. |
| XMATCH | No | Yes | Not in Sheets. Plain MATCH covers it — match_type 1 for a curve you read between anchors, 0 for a listed size. |
| TEXTBEFORE / TEXTAFTER | No | Yes | Not in Sheets. Use SPLIT with INDEX, or REGEXEXTRACT, which is more powerful but less readable. |
| TEXTSPLIT | No | Yes | Not in Sheets under that name. SPLIT is the equivalent and takes a set of delimiters rather than one. |
| GROUPBY / PIVOTBY | No | Yes | Not in Sheets, and not missed — QUERY did this a decade earlier and reads better. |
ARRAYFORMULA does not vectorise everything
This is the one thing worth internalising before you build anything on this page. Arithmetic
vectorises. A single-column
XLOOKUP vectorises. A
multi-column return hands back only the first column.
INDEX does not vectorise at all,
and anything that reduces — MAX, MIN, SUM, COUNT — collapses the array to one value and
writes it into every row. The moment per-row logic branches, stop and reach for
MAP or
BYROW instead.
QUERY nulls out the minority data type
Give a column both 3 and
1-1/2" and QUERY picks one
type and silently discards the other. Plumbing sheets walk into this constantly, because
sizes are the thing everybody types as a fraction. Keep the decimal in the column QUERY
reads and render the fraction somewhere else, or wrap the column in
TO_TEXT first.
Conditional formatting cannot see another sheet
A custom-formula rule can only reference its own sheet, which is the commonest reason a
rule never fires and nobody can work out why. Wrap the cross-sheet reference in
INDIRECT with the sheet name
as text. It is worth the ugliness — the alternative is a velocity ceiling of
8 hard-coded into a formatting rule where nobody will ever find it
again.
Rounding and constants agree with our calculators
Sheets rounds a half away from zero, the same rule Excel uses and the same one our
calculators display with, so
=ROUND(23.615,2) gives 23.62 in
all three. The constants here also run to the full precision the code uses rather than the
familiar rounded form — 0.4085 for velocity, not 0.408 — so your sheet lands on
the calculator's answer rather than near it.
14 tables, 252 rows of code data
These are tab-separated. Copy one, click the cell you want it to start in, and paste — Sheets splits it into columns automatically, as do Excel and LibreOffice. The first three are the ones this page's builds need and the Excel reference does not have; the rest are the same tables that reference ships. Each is generated from the same code our calculators read, so it matches the tool exactly. Paste each one into the tab named in the layout table above, starting at A1.
Pipe dimensions and C factors — the Pipe tab
Every material and size our calculators know, with the bore COMPUTED as OD minus twice the wall rather than transcribed. Column F is the one every formula on this page reaches for. The composite lookups key on column A and column C together, so keep both.
| Material key | Material | Nominal | Label | OD in | Wall in | Bore in | C |
|---|---|---|---|---|---|---|---|
| copper-l | Copper, Type L | 0.5 | 1/2" | 0.625 | 0.04 | 0.545 | 140 |
| copper-l | Copper, Type L | 0.75 | 3/4" | 0.875 | 0.045 | 0.785 | 140 |
| copper-l | Copper, Type L | 1 | 1" | 1.125 | 0.05 | 1.025 | 140 |
| copper-l | Copper, Type L | 1.25 | 1-1/4" | 1.375 | 0.055 | 1.265 | 140 |
| copper-l | Copper, Type L | 1.5 | 1-1/2" | 1.625 | 0.06 | 1.505 | 140 |
| copper-l | Copper, Type L | 2 | 2" | 2.125 | 0.07 | 1.985 | 140 |
| copper-l | Copper, Type L | 2.5 | 2-1/2" | 2.625 | 0.08 | 2.465 | 140 |
| copper-l | Copper, Type L | 3 | 3" | 3.125 | 0.09 | 2.945 | 140 |
| copper-l | Copper, Type L | 4 | 4" | 4.125 | 0.11 | 3.905 | 140 |
| copper-m | Copper, Type M | 0.5 | 1/2" | 0.625 | 0.028 | 0.569 | 140 |
| copper-m | Copper, Type M | 0.75 | 3/4" | 0.875 | 0.032 | 0.811 | 140 |
| copper-m | Copper, Type M | 1 | 1" | 1.125 | 0.035 | 1.055 | 140 |
| copper-m | Copper, Type M | 1.25 | 1-1/4" | 1.375 | 0.042 | 1.291 | 140 |
| copper-m | Copper, Type M | 1.5 | 1-1/2" | 1.625 | 0.049 | 1.527 | 140 |
| copper-m | Copper, Type M | 2 | 2" | 2.125 | 0.058 | 2.009 | 140 |
| copper-m | Copper, Type M | 2.5 | 2-1/2" | 2.625 | 0.065 | 2.495 | 140 |
| copper-m | Copper, Type M | 3 | 3" | 3.125 | 0.072 | 2.981 | 140 |
| copper-m | Copper, Type M | 4 | 4" | 4.125 | 0.095 | 3.935 | 140 |
| pex | PEX (SDR-9) | 0.375 | 3/8" | 0.5 | 0.05555555555555555 | 0.389 | 150 |
| pex | PEX (SDR-9) | 0.5 | 1/2" | 0.625 | 0.06944444444444445 | 0.486 | 150 |
| pex | PEX (SDR-9) | 0.75 | 3/4" | 0.875 | 0.09722222222222222 | 0.681 | 150 |
| pex | PEX (SDR-9) | 1 | 1" | 1.125 | 0.125 | 0.875 | 150 |
| pex | PEX (SDR-9) | 1.25 | 1-1/4" | 1.375 | 0.1527777777777778 | 1.069 | 150 |
| pex | PEX (SDR-9) | 1.5 | 1-1/2" | 1.625 | 0.18055555555555555 | 1.264 | 150 |
| pex | PEX (SDR-9) | 2 | 2" | 2.125 | 0.2361111111111111 | 1.653 | 150 |
| cpvc-40 | CPVC, Schedule 40 | 0.5 | 1/2" | 0.84 | 0.109 | 0.622 | 150 |
| cpvc-40 | CPVC, Schedule 40 | 0.75 | 3/4" | 1.05 | 0.113 | 0.824 | 150 |
| cpvc-40 | CPVC, Schedule 40 | 1 | 1" | 1.315 | 0.133 | 1.049 | 150 |
| cpvc-40 | CPVC, Schedule 40 | 1.25 | 1-1/4" | 1.66 | 0.14 | 1.380 | 150 |
| cpvc-40 | CPVC, Schedule 40 | 1.5 | 1-1/2" | 1.9 | 0.145 | 1.610 | 150 |
| cpvc-40 | CPVC, Schedule 40 | 2 | 2" | 2.375 | 0.154 | 2.067 | 150 |
| cpvc-40 | CPVC, Schedule 40 | 2.5 | 2-1/2" | 2.875 | 0.203 | 2.469 | 150 |
| cpvc-40 | CPVC, Schedule 40 | 3 | 3" | 3.5 | 0.216 | 3.068 | 150 |
| cpvc-40 | CPVC, Schedule 40 | 4 | 4" | 4.5 | 0.237 | 4.026 | 150 |
| cpvc-40 | CPVC, Schedule 40 | 6 | 6" | 6.625 | 0.28 | 6.065 | 150 |
| pvc-40 | PVC, Schedule 40 | 0.5 | 1/2" | 0.84 | 0.109 | 0.622 | 150 |
| pvc-40 | PVC, Schedule 40 | 0.75 | 3/4" | 1.05 | 0.113 | 0.824 | 150 |
| pvc-40 | PVC, Schedule 40 | 1 | 1" | 1.315 | 0.133 | 1.049 | 150 |
| pvc-40 | PVC, Schedule 40 | 1.25 | 1-1/4" | 1.66 | 0.14 | 1.380 | 150 |
| pvc-40 | PVC, Schedule 40 | 1.5 | 1-1/2" | 1.9 | 0.145 | 1.610 | 150 |
| pvc-40 | PVC, Schedule 40 | 2 | 2" | 2.375 | 0.154 | 2.067 | 150 |
| pvc-40 | PVC, Schedule 40 | 2.5 | 2-1/2" | 2.875 | 0.203 | 2.469 | 150 |
| pvc-40 | PVC, Schedule 40 | 3 | 3" | 3.5 | 0.216 | 3.068 | 150 |
| pvc-40 | PVC, Schedule 40 | 4 | 4" | 4.5 | 0.237 | 4.026 | 150 |
| pvc-40 | PVC, Schedule 40 | 6 | 6" | 6.625 | 0.28 | 6.065 | 150 |
| steel-40 | Galvanized steel, Schedule 40 | 0.5 | 1/2" | 0.84 | 0.109 | 0.622 | 120 |
| steel-40 | Galvanized steel, Schedule 40 | 0.75 | 3/4" | 1.05 | 0.113 | 0.824 | 120 |
| steel-40 | Galvanized steel, Schedule 40 | 1 | 1" | 1.315 | 0.133 | 1.049 | 120 |
| steel-40 | Galvanized steel, Schedule 40 | 1.25 | 1-1/4" | 1.66 | 0.14 | 1.380 | 120 |
| steel-40 | Galvanized steel, Schedule 40 | 1.5 | 1-1/2" | 1.9 | 0.145 | 1.610 | 120 |
| steel-40 | Galvanized steel, Schedule 40 | 2 | 2" | 2.375 | 0.154 | 2.067 | 120 |
| steel-40 | Galvanized steel, Schedule 40 | 2.5 | 2-1/2" | 2.875 | 0.203 | 2.469 | 120 |
| steel-40 | Galvanized steel, Schedule 40 | 3 | 3" | 3.5 | 0.216 | 3.068 | 120 |
| steel-40 | Galvanized steel, Schedule 40 | 4 | 4" | 4.5 | 0.237 | 4.026 | 120 |
| steel-40 | Galvanized steel, Schedule 40 | 6 | 6" | 6.625 | 0.28 | 6.065 | 120 |
WSFU fixture schedule — the WSFU tab
25 fixtures. Total is not cold plus hot — the code assigns a fixture's total weight separately, and a sheet that adds the two columns overstates the load on every row. Column B is the dropdown source; column E is what the schedule multiplies.
| Key | Fixture | Cold | Hot | Total | Group |
|---|---|---|---|---|---|
| bathroom-group-tank | Bathroom group, flush tank water closet | 2.7 | 1.5 | 3.6 | Residential |
| bathroom-group-valve | Bathroom group, flushometer water closet | 6 | 3 | 8 | Residential |
| wc-private-tank | Water closet, private, flush tank | 2.2 | 0 | 2.2 | Residential |
| wc-private-valve | Water closet, private, flushometer valve | 6 | 0 | 6 | Residential |
| lavatory-private | Lavatory, private | 0.5 | 0.5 | 0.7 | Residential |
| bathtub-private | Bathtub, private | 1 | 1 | 1.4 | Residential |
| shower-private | Shower head, private | 1 | 1 | 1.4 | Residential |
| kitchen-sink-private | Kitchen sink, private | 1 | 1 | 1.4 | Residential |
| dishwasher-private | Dishwasher, private | 0 | 1.4 | 1.4 | Residential |
| clothes-washer-private | Clothes washer, private | 1 | 1 | 1.4 | Residential |
| laundry-tray-private | Laundry tray, private | 1 | 1 | 1.4 | Residential |
| bidet-private | Bidet, private | 1.5 | 1.5 | 2 | Residential |
| hose-bibb | Hose bibb / sillcock | 2.5 | 0 | 2.5 | Residential |
| wc-public-tank | Water closet, public, flush tank | 5 | 0 | 5 | Commercial |
| wc-public-valve | Water closet, public, flushometer valve | 10 | 0 | 10 | Commercial |
| urinal-1in | Urinal, 1 inch flushometer valve | 10 | 0 | 10 | Commercial |
| urinal-075in | Urinal, 3/4 inch flushometer valve | 5 | 0 | 5 | Commercial |
| urinal-tank | Urinal, flush tank | 3 | 0 | 3 | Commercial |
| lavatory-public | Lavatory, public | 1.5 | 1.5 | 2 | Commercial |
| bathtub-public | Bathtub, public | 3 | 3 | 4 | Commercial |
| shower-public | Shower head, public | 3 | 3 | 4 | Commercial |
| kitchen-sink-public | Kitchen sink, public | 3 | 3 | 4 | Commercial |
| service-sink-public | Service sink, public | 2.25 | 2.25 | 3 | Commercial |
| clothes-washer-public | Clothes washer, public | 2.25 | 2.25 | 3 | Commercial |
| drinking-fountain-public | Drinking fountain | 0.25 | 0 | 0.25 | Commercial |
Limits and constants — the Limits tab
The tab every conditional-formatting rule points at, which is the whole reason to have it: a limit hard-coded into a formatting rule is a limit nobody will ever find again. The two velocity ceilings are design practice rather than numeric IPC limits — the code requires a system free of excessive noise and erosion without naming a figure.
| Label | Value | Unit | Status |
|---|---|---|---|
| Max velocity, cold | 8 | ft/s | ASPE / manufacturer practice, not IPC |
| Max velocity, hot | 5 | ft/s | ASPE / manufacturer practice, not IPC |
| PRV threshold | 80 | psi | IPC 604.8 — required above this |
| Minimum vent size | 1.25 | in | IPC 906.2 |
| Vent length upsize | 40 | ft | IPC 906.2 |
| Min building drain with WC | 3 | in | IPC 710.1 |
| Velocity constant | 0.4085 | — | 0.4085, fuller than the published 0.408 |
| psi per foot of head | 0.4331 | psi/ft | water at 60 °F |
| feet of head per psi | 2.3089 | ft/psi | reciprocal of the above |
Hunter's curve — WSFU to GPM
IPC Appendix E Table E103.3(3), 37 rows. The flushometer column is blank at the low end because those fixtures cannot occur there. Appendix E is an appendix — enforceable only where specifically adopted — and Hunter's curve runs high for modern low-flow fixtures, so the error is toward larger pipe.
| Fixture units | Flush tank gpm | Flushometer gpm |
|---|---|---|
| 1 | 3 | |
| 2 | 5 | |
| 3 | 6.5 | |
| 4 | 8 | |
| 5 | 9.4 | 15 |
| 6 | 10.7 | 17.4 |
| 7 | 11.8 | 19.8 |
| 8 | 12.8 | 22.2 |
| 9 | 13.7 | 24.6 |
| 10 | 14.6 | 27 |
| 12 | 16 | 28.6 |
| 14 | 17 | 30.2 |
| 16 | 18 | 31.8 |
| 18 | 18.8 | 33.4 |
| 20 | 19.6 | 35 |
| 25 | 21.5 | 38 |
| 30 | 23.3 | 41 |
| 35 | 24.9 | 43.8 |
| 40 | 26.3 | 46.5 |
| 45 | 27.7 | 49 |
| 50 | 29.1 | 51.5 |
| 60 | 32 | 55 |
| 70 | 35 | 58.5 |
| 80 | 38 | 62 |
| 90 | 41 | 64.8 |
| 100 | 43.5 | 67.5 |
| 120 | 48 | 72.5 |
| 140 | 52.5 | 77.5 |
| 160 | 57 | 82.5 |
| 180 | 61 | 87 |
| 200 | 65 | 91.5 |
| 250 | 75 | 101 |
| 300 | 85 | 110 |
| 400 | 105 | 126 |
| 500 | 124 | 142 |
| 750 | 170 | 178 |
| 1000 | 208 | 208 |
Fitting equivalent lengths — L/D ratios
Equivalent feet is L/D × bore ÷ 12, so one table covers every pipe size. Overwrite the third column with your fitting counts and the developed-length SUMPRODUCT reads it directly. These are ASPE and manufacturer design values, not code.
| Fitting | L/D ratio | Your count |
|---|---|---|
| 90 degree elbow | 30 | 0 |
| 45 degree elbow | 16 | 0 |
| Tee, straight through | 20 | 0 |
| Tee, through the branch | 60 | 0 |
| Ball valve, full open | 8 | 0 |
| Gate valve, full open | 8 | 0 |
| Globe valve | 340 | 0 |
| Swing check valve | 100 | 0 |
Minimum drain slope — IPC Table 704.1
Three rows is the entire table, which is why the nested IF above is honest rather than a shortcut. The last band has no upper size, so the IF ends with a bare else rather than a third comparison.
| Up to size (in) | Minimum slope (in/ft) | Percent grade |
|---|---|---|
| 2.5 | 0.25 | 2.08% |
| 6 | 0.125 | 1.04% |
| larger | 0.0625 | 0.52% |
Trap arm maximum length — IPC Table 909.1
Measured from the trap weir to the inner edge of the vent fitting. At the three smallest sizes the arm has fallen exactly one pipe diameter at maximum length; at 3 and 4 in it has fallen half a diameter, because the permitted slope halves.
| Trap arm size (in) | Max slope (in/ft) | Max developed length (ft) |
|---|---|---|
| 1-1/4" | 0.25 | 5 |
| 1-1/2" | 0.25 | 6 |
| 2" | 0.25 | 8 |
| 3" | 0.125 | 12 |
| 4" | 0.125 | 16 |
Stocked vent sizes
Used by the vent-size MATCH to round the half-diameter result up to something you can buy. The 1.25 in first row is the IPC 916.2 floor.
| Nominal size (in) |
|---|
| 1-1/4" |
| 1-1/2" |
| 2" |
| 2-1/2" |
| 3" |
| 4" |
| 5" |
| 6" |
| 8" |
Water density by temperature
Drives the thermal-expansion fraction. A uniform 10 °F grid from 40 to 200, so an approximate MATCH lands on the row below and the interpolation is straightforward. Note the 60 °F row is where the 0.4331 psi-per-foot constant comes from.
| Temperature (°F) | Density (lb/ft³) |
|---|---|
| 40 | 62.43 |
| 50 | 62.41 |
| 60 | 62.37 |
| 70 | 62.3 |
| 80 | 62.22 |
| 90 | 62.12 |
| 100 | 62 |
| 110 | 61.86 |
| 120 | 61.71 |
| 130 | 61.55 |
| 140 | 61.38 |
| 150 | 61.2 |
| 160 | 61.01 |
| 170 | 60.79 |
| 180 | 60.57 |
| 190 | 60.35 |
| 200 | 60.12 |
Fixture drainage units — IPC Table 709.1
28 fixtures. Overwrite the last column with your counts and SUMPRODUCT the DFU column against it. The blank trap cells are the fixtures with an integral trap — both bathroom groups, every water closet row and the urinals — where the trap is part of the fixture rather than a size you choose.
| Fixture | DFU | Trap size (in) | Your count |
|---|---|---|---|
| Bathroom group (1.6 gpf water closet) | 5 | 0 | |
| Bathroom group (over 1.6 gpf water closet) | 6 | 0 | |
| Water closet, private (1.6 gpf) | 3 | 0 | |
| Water closet, private (over 1.6 gpf) | 4 | 0 | |
| Lavatory | 1 | 1-1/4" | 0 |
| Bathtub (with or without shower) | 2 | 1-1/2" | 0 |
| Shower, up to 5.7 gpm | 2 | 1-1/2" | 0 |
| Shower, over 5.7 to 12.3 gpm | 3 | 2" | 0 |
| Kitchen sink, domestic | 2 | 1-1/2" | 0 |
| Dishwashing machine, domestic | 2 | 1-1/2" | 0 |
| Clothes washer, residential | 2 | 2" | 0 |
| Laundry tray (1 or 2 compartments) | 2 | 1-1/2" | 0 |
| Bidet | 1 | 1-1/4" | 0 |
| Floor drain | 2 | 2" | 0 |
| Sink | 2 | 1-1/2" | 0 |
| Water closet, public (1.6 gpf) | 4 | 0 | |
| Water closet, public (over 1.6 gpf) | 6 | 0 | |
| Water closet, flushometer tank | 4 | 0 | |
| Urinal | 4 | 0 | |
| Urinal, 1 gpf or less | 2 | 0 | |
| Urinal, nonwater supplied | 0.5 | 0 | |
| Shower, over 12.3 to 25.8 gpm | 5 | 3" | 0 |
| Shower, over 25.8 to 55.6 gpm | 6 | 4" | 0 |
| Service sink | 2 | 1-1/2" | 0 |
| Clothes washer, commercial | 3 | 2" | 0 |
| Drinking fountain | 0.5 | 1-1/4" | 0 |
| Wash sink (per faucet set) | 2 | 1-1/2" | 0 |
| Dental lavatory | 1 | 1-1/4" | 0 |
Building drain capacity — IPC Table 710.1(1)
Maximum drainage fixture units on a building drain or sewer, by slope. Paste the blanks as genuinely empty cells — an empty cell compares as zero, so a first-TRUE capacity search skips those rows correctly, whereas a dash or the word 'n/a' breaks the comparison. Blank means the slope is not permitted at that size, not that capacity is unlimited.
| Size (in) | 1/16 in/ft | 1/8 in/ft | 1/4 in/ft | 1/2 in/ft |
|---|---|---|---|---|
| 1-1/4" | 1 | 1 | ||
| 1-1/2" | 3 | 3 | ||
| 2" | 21 | 26 | ||
| 2-1/2" | 24 | 31 | ||
| 3" | 36 | 42 | 50 | |
| 4" | 180 | 216 | 250 | |
| 5" | 390 | 480 | 575 | |
| 6" | 700 | 840 | 1000 | |
| 8" | 1400 | 1600 | 1920 | 2300 |
| 10" | 2500 | 2900 | 3500 | 4200 |
| 12" | 3900 | 4600 | 5600 | 6700 |
| 15" | 7000 | 8300 | 10000 | 12000 |
Branches and stacks — IPC Table 710.1(2)
Use the column that matches what you are sizing — a horizontal branch, a short stack or a tall one. The counter-intuitive column is the last: a TALLER stack carries more per size than a short one, because terminal-velocity annular flow only develops with height. A 2 in stack takes 10 fixture units over three branch intervals and 24 over more than three.
| Size (in) | Horizontal branch | Stack, 3 intervals or fewer | Stack, over 3 intervals |
|---|---|---|---|
| 1-1/2" | 3 | 4 | 8 |
| 2" | 6 | 10 | 24 |
| 2-1/2" | 12 | 20 | 42 |
| 3" | 20 | 48 | 72 |
| 4" | 160 | 240 | 500 |
| 5" | 360 | 540 | 1100 |
| 6" | 620 | 960 | 1900 |
| 8" | 1400 | 2200 | 3600 |
| 10" | 2500 | 3800 | 5600 |
| 12" | 3900 | 6000 | 8400 |
| 15" | 7000 |
Septic tank minimums by bedroom count
Common published minimums rather than a single national code — septic falls to state and county health departments, so confirm the local figures before relying on these. The last row has no upper bedroom count, so a first-TRUE search rather than an exact match is the right lookup.
| Up to bedrooms | Minimum tank (gal) |
|---|---|
| 3 | 1000 |
| 4 | 1250 |
| 5 | 1500 |
| 6 | 1750 |
| more | 2000 |
Unit conversion factors
Every unit carries a factor to its category's base unit — gpm for flow, psi for pressure, gallons for volume, inches for length, ft/s for velocity. Multiply into the base and divide out of it. Temperature is deliberately absent: it is an offset scale, not a ratio, so it needs its own formula and cannot use this table.
| Category | Unit | To base |
|---|---|---|
| flow | gpm | 1 |
| flow | gph | 0.016666666666666666 |
| flow | lpm | 0.26417205235814845 |
| flow | lps | 15.850323141488905 |
| flow | m3h | 4.402867539302474 |
| flow | cfs | 448.8312 |
| flow | ukgpm | 1.200949925504855 |
| pressure | psi | 1 |
| pressure | ftH2O | 0.4331 |
| pressure | mH2O | 1.4209317585245 |
| pressure | bar | 14.503773773 |
| pressure | kpa | 0.1450377377 |
| pressure | atm | 14.695948775 |
| volume | gal | 1 |
| volume | l | 0.26417205235814845 |
| volume | ft3 | 7.48052 |
| volume | m3 | 264.17205235814845 |
| volume | in3 | 0.004329004329004329 |
| volume | ukgal | 1.200949925504855 |
| length | in | 1 |
| length | ft | 12 |
| length | mm | 0.03937007874015748 |
| length | cm | 0.39370078740157477 |
| length | m | 39.37007874015748 |
| velocity | fps | 1 |
| velocity | fpm | 0.016666666666666666 |
| velocity | mps | 3.280839895 |
| velocity | kph | 0.9113444152777778 |
Every plumbing formula, identical in both
Here for completeness, because a page about plumbing formulas in Sheets should contain the plumbing formulas. All 35 paste into Sheets exactly as written — no dialect, no substitutions, no version to check. What each one means, which input cells it expects and why the constants are what they are lives on the Excel reference, and the arithmetic behind them on the plumbing formula reference.
Pipe Sizing & Water Supply
| Formula | Paste into a cell | Copy |
|---|---|---|
| Water Supply Fixture Units → Peak Demand (GPM) needs the Hunter's curve table | =IF(B2>=INDEX($F$2:$F$38,37),INDEX($G$2:$G$38,37),INDEX($G$2:$G$38,B3)+(B2-INDEX($F$2:$F$38,B3))/(INDEX($F$2:$F$38,B3+1)-INDEX($F$2:$F$38,B3))*(INDEX($G$2:$G$38,B3+1)-INDEX($G$2:$G$38,B3))) 18.16 gpm flush tank · 32.12 gpm flushometer valve | |
| Pipe Velocity | =0.4085*B2/B3^2 6.63 ft/s | |
| Hazen-Williams Friction Loss | =4.52*B2^1.852*100/(B3^1.852*B4^4.8704) 11.08 psi per 100 ft | |
| Available Pressure for Friction | =B2-B3-0.4331*B4-B5-B6 28.34 psi left for pipe friction | |
| Allowable Friction Loss per 100 ft | =B2/B3*100 23.62 psi per 100 ft allowable | |
| Developed Length & Fitting Equivalents needs the Fitting L/D ratios table | =B2+SUMPRODUCT($F$2:$F$9,$G$2:$G$9)*B3/12 80.1 ft developed from a 60 ft run with 6 elbows, 2 branch tees and a ball valve |
Drainage, Waste & Vent
| Formula | Paste into a cell | Copy |
|---|---|---|
| Drainage Fixture Units & Drain Size needs the Tables 709.1 and 710.1 table | =IF(B3,MAX(3,INDEX($J$2:$J$6,MATCH(TRUE,INDEX($K$2:$K$6>=B2,0),0))),INDEX($J$2:$J$6,MATCH(TRUE,INDEX($K$2:$K$6>=B2,0),0))) 18 DFU with a water closet → 3" (the table alone says 2" — governed by the water closet, not the table) | |
| Minimum Drain Slope | =IF(B2<=2.5,0.25,IF(B2<=6,0.125,0.0625)) 2 in → 0.25 in/ft · 3 in → 0.125 in/ft | |
| Slope as Percent & Total Fall | =B2*B3 fall in inches
=B2/12*100 slope as a percent 5.0 in of fall · 1.04% | |
| Manning's Equation — Drain Capacity | =(1.486/B4)*(B2/48)^(2/3)*(B3/12)^0.5 velocity, ft/s
=PI()/8*(B2/12)^2*E2*448.831 gpm at half full 2.39 ft/s · 26.3 gpm | |
| Vent Size needs the Stocked vent sizes table | =INDEX($F$2:$F$10,MATCH(TRUE,INDEX($F$2:$F$10>=MAX(1.25,B2/2),0),0)+IF(B3>40,1,0)) 3 in drain over 30 ft → 1-1/2" · the same drain over 60 ft → 2" | |
| Trap Arm Maximum Length needs the Table 909.1 table | =INDEX($H$2:$H$6,MATCH(B2,$F$2:$F$6,0)) 1-1/2 in → 6 ft · 2 in → 8 ft · 3 in → 12 ft | |
| Grease Interceptor Sizing | =B2*B3*B4*B5/231 sink volume, gallons
=E2*B6/B7 required flow, gpm
=E3*2 grease capacity, lb
=MAX(1000,B8*B9*B10*B11*B12) gravity interceptor, gallons 89.77 gal → 67.32 gpm → a 75 gpm / 150 lb unit · 100 seats → 1,500 gal | |
| Rainwater Yield & Cistern Storage | =B2*B3*(7.48052/12) gross yield, gallons
=MIN(E2,B2*B6*B7) first flush diverted
=(E2-E3)*B4*B5 net yield, gallons
=MIN(B8*B9,E4) cistern, gallons 4,364 gal gross − 120 gal first flush → 3,066 gal net · cistern governed by the dry spell at 1,260 gal | |
| Storm & Roof Runoff Flow | =B2*B3*7.48052/12/60 runoff, gpm
=(1.486/B6)*(B4/48)^(2/3)*(B5/12)^0.5 velocity, ft/s
=PI()/4*(B4/12)^2*E3*448.831 full-bore capacity, gpm 83.1 gpm off the roof · a 4 in drain carries 115.3 gpm at 2.91 ft/s | |
| Septic Tank & Leach Field needs the Tank minimums by bedroom table | =B2*B3 daily flow, gpd
=E2*2 retention volume, gal
=INDEX($G$2:$G$6,MATCH(TRUE,INDEX($F$2:$F$6>=B2,0),0)) code minimum
=MAX(E3,E4) tank, gallons
=E2/B4 leach field, ft2
=E6/B5 trench length, ft 450 gpd → retention 900 gal vs minimum 1,000 gal → 1,000 gal tank, governed by the minimum · field 563 ft² = 188 ft of trench | |
| Grey Water Yield & Irrigable Area | =B2*(B3+B4)+B5*B6/7 daily yield, gpd
=E2*24/24 maximum storage, gal
=E2*7/B7 irrigable area, ft2 104.3 gpd (shower 75.0, lavatory 15.0, washer 14.3) · storage 104 gal · 1,217 ft² irrigable |
Pressure & Pump Head
| Formula | Paste into a cell | Copy |
|---|---|---|
| Pressure ↔ Head of Water | =0.4331*B2 psi from feet
=B3/0.4331 feet from psi 65 psi = 150.1 ft of head | |
| Static Pressure Loss from Elevation | =0.4331*B2 10.83 psi lost over a 25 ft rise | |
| Total Dynamic Head | =B2+(B3*B4/100)/0.4331+B5/0.4331+B6^2/(2*32.174) 14.43 ft of total dynamic head | |
| Pressure-Reducing Valve Threshold | =IF(B2>80,"PRV required","No PRV required") 92 psi → PRV required · 78 psi → no PRV required | |
| Thermal Expansion Volume needs the Water density table | =INDEX($G$2:$G$19,MATCH(B3,$F$2:$F$19,1))/INDEX($G$2:$G$19,MATCH(B4,$F$2:$F$19,1))-1 expansion fraction
=B2*E2/(1-(B5+14.7)/(B6+14.7)) tank volume, gal 1.71% expansion · 0.86 gal to absorb · 4.05 gal tank → a 4.4 gal shell | |
| Pressure Tank Drawdown | =MIN(1,(B4+14.7)/(B2+14.7))-MIN(1,(B4+14.7)/(B3+14.7)) drawdown fraction
=B5*B6/E2 required shell, gal 29.53% of the shell at a 30/50 switch · a 10 gpm pump needs a 44 gal tank |
Water Heating & Gas Piping
| Formula | Paste into a cell | Copy |
|---|---|---|
| Temperature Rise & Recovery Rate | =B2*B3/(8.33*B4) gas, gallons per hour
=B5*3412.14/(8.33*B4) electric, gallons per hour 42.68 gph from a 40,000 BTU/hr gas heater at 80% · 20.48 gph from a 4.5 kW element | |
| Tankless Flow Capacity | =B2*B3/(499.8*B4) 5.40 gpm at a 70 °F rise · 4.73 gpm at 80 °F | |
| First-Hour Rating | =0.7*B2+B3 70.7 gal in the first hour from a 40 gal gas heater | |
| Mixing Valve / Tempered Water Ratio | =(B2-B3)/(B4-B3) hot fraction
=E2*B5 hot gpm drawn from the tank
=(1-E2)*B5 cold gpm blended in
=1/E2 storage multiplier 77.8% hot · 1.94 gpm hot and 0.56 gpm cold of a 2.5 gpm draw · storage multiplier 1.286 | |
| Gas Load → CFH | =B2/B3 150,000 BTU/hr on natural gas → 150 CFH |
Volume, Conversions & Waste
| Formula | Paste into a cell | Copy |
|---|---|---|
| Pipe Volume & Gallons per Foot | =PI()/4*B2^2*12/231 gallons per foot
=E2*B3 gallons in the run
=E3*8.33 weight of that water, lb
=E3/B4*60 seconds to purge it 0.02514 gal/ft · 1.26 gal in 50 ft · 10.5 lb · 38 s to purge | |
| Unit Conversion needs the Conversion factors table | =B2*INDEX($H$2:$H$30,MATCH(B3,$G$2:$G$30,0))/INDEX($H$2:$H$30,MATCH(B4,$G$2:$G$30,0)) 10 gpm = 37.8541 L/min · 60 psi = 138.5 ft of head · 1 bar = 14.5038 psi | |
| Leak & Water Waste Cost | =B2*1440/15140 gallons per day
=E2*365 gallons per year
=E3/1000*(B3+B4) water and sewer per year
=E3*B5*8.33*B6/B7/100000*B8 energy per year, gas
=E4+E5 total per year 5.71 gpd = 2,083 gal/yr · $26.04/yr cold, $47.29/yr hot on gas |
Pricing & Business
| Formula | Paste into a cell | Copy |
|---|---|---|
| Job Price — Margin, not Markup | =B2*B3+B4+B5 direct cost
=E2*(1+B6/100) break-even
=E3/(1-B7/100) price at that MARGIN
=E3*(1+B7/100) price at the same MARKUP
=E4-E3 profit at margin direct $2,400 → break-even $2,760 → $3,680 at margin ($920 profit) but only $3,450 at markup, which delivers 20.0% and $230 less | |
| Loaded Labour Rate | =B2*2080*(1+B3/100)+B4 annual cost of the tech
=E2/B5 loaded cost per billable hour
=E3/(1-B6/100) bill rate
=E4/B2 multiple of the wage
=B5/2080 utilisation $115,333 a year → $73.93 loaded → $123.22 bill rate, 3.24x the wage at 75% utilisation | |
| Whole-House Repipe Cost | =B2*B3*B4*(1+MAX(0,B5-1)*0.15) after access and storeys
=(E2+MAX(0,B6-2)*750+B7+B8)*B9 total
=E3/B2 cost per ft2
=E3*(1-0.15) band low
=E3*(1+0.15) band high $10,950 = $6.08 per ft², band $9,308–$12,592 | |
| Water Heater Replacement Cost | =B3*B4 labour
=(B2+E2+B5+B6+B7)*B8 total
=B2*B8/E3 equipment share of the job
=E3*(1-0.18) band low
=E3*(1+0.18) band high labour $440 → $2,070 total, equipment 53.1% of it, band $1,697–$2,443 |
Sources & standards: International Plumbing Code (IPC) 2021 — 604.1, 604.8, 704.1, Table 709.1, Tables 710.1(1) and 710.1(2), 906.2, Table 909.1, and Appendix E Tables E103.3(2) and E103.3(3). Velocity limits, fitting equivalent lengths, and Hazen-Williams and Manning coefficients are ASPE and manufacturer design practice, not code requirements. Google Sheets function availability, the named-function naming rules, the conditional-format same-sheet restriction and the IMPORTRANGE access model were each checked against Google's own Docs Editors Help rather than inferred — Sheets gains functions regularly, so a claim that something is missing is only as current as the day it was checked.
Plumbing code adoption is split: much of the country is on the IPC, while other states use the Uniform Plumbing Code (UPC), which differs on fixture-unit tables, drain slope allowances, and velocity limits. Confirm which code your jurisdiction has adopted, and which edition. Local amendments override the model code, and a licensed plumber plus the AHJ have final say on anything installed.
A shared sheet is more current than a saved workbook and still not current. If you copy these builds and a code table changes in a later cycle, nothing in your file will tell you — and a sheet several people can edit has more ways to drift, not fewer. That is the one real argument for working an answer here as well: the calculators are maintained against the code, and your copy is not.
Model it in a sheet. Quote it somewhere better.
A shared sheet is the right tool for everything on this page — size a whole riser schedule at once, roll the load up by floor, and let three people work the same take-off without emailing versions around. That is modelling. Producing the document a customer signs is a different job: it needs line items, versions and a record of what was agreed, and a spreadsheet everybody can edit is the worst possible place to keep a price somebody has already accepted. Model the numbers here, then let TradesQuote turn the scope into a line-item estimate your client can accept online.