Courier: Redelivery Attempt Fees

Excel Formulas › Courier & Delivery

All versions

The first attempt is included in the rate; every one after that costs you a truck roll. The only wrinkle is arithmetic — subtracting one from an attempt count can go negative or zero, and a negative fee on an invoice is worse than no fee at all.

Quick formula: Attempts beyond the first, floored at zero, times the redelivery rate:

=MAX(0,B2-1)*C2

Three attempts at $6.50 bills two redeliveries — $13.00. One attempt bills nothing, not minus $6.50.

Functions used (tap for the full reference guide):

MAX SUM

The example

Three stops from one route, each with the number of attempts made and the redelivery rate on that account.

	A	B	C	D
1	Stop	Attempts	Fee each	Charge
2	Acme Supply	3	$6.50	$13.00
3	Delta Print	1	$6.50	$0.00
4	Nova Clinic	2	$6.50	$6.50

The formula

MAX is doing the guarding here, not an IF:

=MAX(0,B2-1)*C2 // attempts past the first, never below zero, x fee each

How it works

Three pieces:

B2-1 removes the included first attempt. On a single-attempt stop this is 0; on a stop logged as 0 attempts it would be -1.
MAX(0,...) clamps that at zero. It is shorter and faster than IF(B2>1,B2-1,0) and it reads as the business rule: never fewer than none.
*C2 applies the account's redelivery rate, which is usually negotiated per client rather than fixed.
SUM the charge column for the route's total recoverable redelivery revenue.

The same MAX-clamp pattern covers any “first one free” rule — waiting time, extra pieces, or included revisions.

Try it: interactive demo

Interactive

Enter the attempts logged and the redelivery rate.

Attempts made

Fee per redelivery ($)

Variations

Two attempts included

Change the allowance from one to two by editing a single number.

=MAX(0,B2-2)*C2

Cap the charge

Stop billing after three redeliveries and return the parcel instead.

=MIN(3,MAX(0,B2-1))*C2

Pitfalls & errors

Skipping the MAX is the classic bug. =(B2-1)*C2 on a clean one-attempt delivery produces a credit line on the invoice that nobody notices until the month closes short.

Keep the included-attempt allowance in its own cell rather than typing 1 inside the formula. Contracts differ by client, and a cell reference makes the allowance auditable.

A refused delivery is not a failed attempt. Give refusals their own code — they usually carry a return charge, not a redelivery fee, and mixing them corrupts both numbers.

Practice workbook

📊

Download the free Courier: Redelivery Attempt Fees practice workbook

Edit the yellow attempts and fee cells; the charge recalculates and the total updates.

Frequently asked questions

Why MAX instead of IF?

They give the same answer, but MAX states the rule in one term and does not need a comparison and two branches. On a long invoice sheet it is also less to get wrong when the allowance changes.

Should the first attempt ever be chargeable?

Only if the shipper caused the failure — a bad address or no access code. That is a different fee with a different name; leave it out of the redelivery column so disputes stay easy to trace.

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: MAX SUM