Septic Service: Pump-Out Quote With Gallon Overage

All versions

Septic pump-outs are usually priced as a base fee covering a standard tank, with an overage for larger tanks billed per 100 gallons. This recipe takes the tank size and the included gallons, converts the excess to whole 100-gallon blocks and prices the overage on top of the base.

Quick formula: Base fee plus the overage blocks times the block rate:

=D2+ROUNDUP(MAX(0,B2-C2)/100,0)*E2

A 1,500-gallon tank with 1,000 included at a $325 base and $18 per block quotes $415.

Functions used (tap for the full reference guide):

ROUNDUP MAX

The example

Three tanks of different sizes. A tank at or under the included gallons pays only the base.

	A	B	C	D	E	F	G
1	Case	Tank gal	Included	Base $	Per 100 gal	Total	Overage
2	Standard	1,500	1,000	$325	$18	$415	$90
3	Small tank	1,000	1,000	$325	$18	$325	$0
4	Large tank	2,000	1,000	$325	$18	$505	$180

The formula

Excess gallons, rounded up to blocks of 100, times the block rate, added to the base:

=D2+ROUNDUP(MAX(0,B2-C2)/100,0)*E2 // total quote; overage = ROUNDUP(MAX(0,Tank-Included)/100,0)*Rate

How it works

Here is the logic, one piece at a time:

B2-C2 is the gallons above what the base fee includes.
MAX(0,...) keeps a smaller tank from producing a negative overage.
Dividing by 100 and rounding up counts started blocks, since the truck still pumps the partial block.
Multiplying by the rate in E2 and adding the base in D2 yields the quote.

Quote from the tank size in the county permit, not the homeowner's guess.

Try it: interactive demo

Interactive

Enter the tank size, included gallons, base fee and rate.

Tank size (gallons)

Gallons included

Base fee ($)

Fee per 100 gal ($)

Variations

Use a different block size

Change 100 to 250 or 500 if your pricing steps are larger.

=D2+ROUNDUP(MAX(0,B2-C2)/250,0)*E2

Add a hard-to-reach surcharge

Add a flat fee when the lid is more than a set distance from the truck.

=D2+ROUNDUP(MAX(0,B2-C2)/100,0)*E2+IF(Hose>100,75,0)

Pitfalls & errors

Compartmented tanks may need each compartment pumped; check how the permit lists capacity.

Without MAX, a tank smaller than the included gallons would subtract from the base.

Record the actual gallons pumped from the truck meter and compare with the permit size to catch misquotes.

Practice workbook

📊

Download the free Septic Service: Pump-Out Quote With Gallon Overage practice workbook

Edit the yellow tank, included, base and rate cells; total and overage recalculate.

Frequently asked questions

Why round the overage up?

A partial 100-gallon block still takes pumping time and disposal, so billing whole blocks is standard.

Can I price by pumped gallons instead?

Yes, replace Tank with the metered gallons from the truck.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: ROUNDUP MAX