Excel guide · 7 min read
Cost per mile formula in Excel
=SUM(costs)/SUM(miles). Take miles from odometer readings (end minus start), include fixed costs (payment, insurance, permits) and variable costs (driver pay, fuel, maintenance, tires), and for several trucks or months sum costs and miles separately with SUMIFS before you divide.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Month | Truck | Odo start | Odo end | Miles | Loaded mi | Driver pay | Fuel | Maintenance | Tires | Fixed | Total cost |
| 2 | Sep 1, 2026 | T1 | 412350 | 422180 | 9830 | 8460 | 6095 | 5690 | 420 | 0 | 3360 | 15565 |
| 3 | Sep 1, 2026 | T2 | 287040 | 296415 | 9375 | 7980 | 5813 | 5560 | 640 | 0 | 3360 | 15373 |
| 4 | Oct 1, 2026 | T1 | 422180 | 432905 | 10725 | 9350 | 6650 | 6240 | 1860 | 0 | 3360 | 18110 |
| 5 | Oct 1, 2026 | T2 | 296415 | 307020 | 10605 | 9140 | 6575 | 6180 | 380 | 620 | 3360 | 17115 |
| 6 | Nov 1, 2026 | T1 | 432905 | 441610 | 8705 | 7390 | 5397 | 5105 | 310 | 1240 | 3360 | 15412 |
| 7 | Nov 1, 2026 | T2 | 307020 | 5980 | 5320 | 2450 | 0 | 3360 | 17110 | |||
| 8 | Nov 1, 2026 | T3 | 158220 | 158220 | 0 | 0 | 0 | 0 | 4780 | 0 | 3360 | 8140 |
| 9 |
Download this sheet as an Excel practice file (.xlsx)
The basic formula: one truck, one month
The sheet is a three-month log for a small fleet, one row per truck per month. Miles in E come from the odometer, =IF(OR(C2="",D2=""),"",D2-C2), so a missing reading leaves the cell blank instead of inventing a number. Driver pay, fuel, maintenance and tires (G to J) are what was actually paid that month. Fixed costs come from the input block: truck payment in O1, insurance in O2 and permits and plates in O3 (a yearly cost, divided by 12), 3,360 per truck per month in O4. L adds the row up.
T1 in September cost 15,565 and drove 9,830 miles. Divide the two and format the result as currency with three decimals: a tenth of a cent per mile adds up to real money over 100,000 miles a year.
The same formula works for a personal car or a work van. Put a year of costs on top (insurance, registration, an amount for depreciation, fuel, servicing) and the year's miles underneath.
Result: 1.5834181078 shows as $1.583 with Currency format
$1.583 per mile for T1 in September: 15,565 of costs over 9,830 miles.
Fixed vs variable cost per mile
Fixed costs are paid whether the truck moves or not: the payment, insurance, permits. Variable costs grow with every mile: driver pay per mile, fuel, maintenance, tires. Split them, because they behave differently. Variable cost per mile stays roughly flat; fixed cost per mile falls as miles go up.
On the sheet, T1's 3,360 of fixed costs is =K4/E4, $0.313 a mile in October (10,725 miles) but =K6/E6, $0.386 in November (8,705 miles). Nothing got more expensive; the truck just drove less. That is why a slow month always looks worse per mile, and why a quote based on one busy month is too low.
The formula below splits the fleet's rate for the months with complete readings (the D2:D8,"<>" part is explained under missing readings). Fixed per mile plus variable per mile is the total, $1.822.
Result: spills 1 row × 2 columns
| 0.40942323314 | 1.4125710804 |
0.409 fixed and 1.413 variable: $0.41 + $1.41 = $1.82 per mile across the fleet.
Missing odometer readings and trucks that didn't move
T2 has no November closing reading (D7 is empty): the driver's log never came in. Its November costs, 17,110, are still in column L. Divide all costs by all miles and those costs count with none of their miles: =SUM(L2:L8)/SUM(E2:E8) says $2.169, about 35 cents too high.
Best fix: get the reading (the next month's start reading, the ELD or the fuel card's odometer entry). Until then, leave the row out of both the top and the bottom. SUMIFS with D2:D8,"<>" keeps only rows that have a closing reading. =SUMPRODUCT(ISNUMBER(E2:E8)*L2:L8)/SUM(E2:E8) gives the same answer by testing the miles column instead.
T3 is the opposite case: in the shop all November, 0 miles but 8,140 of costs. Those costs are real and belong in the fleet total, which is why the formula keeps T3. Per truck, though, =L8/E8 is #DIV/0!. Guard it with =IF(E8=0,"no miles",L8/E8) rather than IFERROR, which would also hide a typo or a text value.
Result: 1.8219943136 shows as $1.822 with Currency format
$1.822 per mile: 89,715 of costs over 49,240 miles, with T2's unread November left out of both.
Cost per mile per truck with SUMIFS
For one truck, sum its costs and its miles with SUMIFS and divide: =SUMIFS(L2:L8,B2:B8,"T1")/SUMIFS(E2:E8,B2:B8,"T1") gives $1.678 over three months. Do the same for T2 without the reading filter and you get $2.482, because November's 17,110 is on top with no November miles under it. With the filter it is $1.626.
In Excel 365 one formula lists every truck. UNIQUE builds the list, SUMIFS takes the list as its criteria so it returns one total per truck, and IF handles the truck with no miles.
Don't average the monthly rates to get a truck's rate. A month with few miles has a high rate and would count as much as a full month. Sum, then divide.
Result: spills 3 rows × 3 columns
| T1 | 29260 | 1.6776144908 |
| T2 | 19980 | 1.626026026 |
| T3 | 0 | no miles |
T1 29,260 miles at $1.678, T2 19,980 miles at $1.626, T3 0 miles: "no miles".
Cost per mile by month
Swap the truck column for the month column and the same pattern gives a monthly trend. Format the first column as a date (mmm yyyy). For a single month typed in a cell, =SUMIFS(L2:L8,A2:A8,O9,D2:D8,"<>")/SUMIFS(E2:E8,A2:A8,O9) gives October alone.
September $1.611 and October $1.651 look normal. November jumps to $2.706: only T1's 8,705 miles were read, while T3's 8,140 of shop and fixed costs sat on the books with no miles. That is a true picture of the fleet's November (an idle truck costs money) but not of what a running truck costs. Show both: the fleet rate for the business, the running-truck rate for quoting loads.
Result: spills 3 rows × 3 columns
| 46266 | 19205 | 1.6109346524 |
| 46296 | 21330 | 1.6514299109 |
| 46327 | 8705 | 2.7055715106 |
Sep 2026 19,205 miles at $1.611, Oct 2026 21,330 at $1.651, Nov 2026 8,705 at $2.706 (T3 idle, T2 unread).
Fuel cost per mile from MPG and price
Fuel per mile is price per gallon divided by miles per gallon: =O6/O5, $3.85 / 6.6 MPG. Use your real MPG (miles between fill-ups / gallons, see fuel cost and mileage), not the brochure figure.
Check it against the log: =SUMIFS(H2:H8,D2:D8,"<>")/SUM(E2:E8) is what fuel actually cost per mile, $0.584. The two agree, so the MPG and price inputs are realistic. If the logged figure is much higher, look for idling, a fuel card used for another truck, or a reefer unit burning fuel on its own.
Because MPG is in the bottom, small changes matter: at 7.1 MPG the same diesel is =O6/7.1, about $0.542 a mile, 4 cents less, or roughly 4,000 a year per 100,000 miles. For a car: 28 MPG at 3.29 a gallon is about 12 cents a mile.
Result: 0.58333333333 shows as $0.583 with Currency format
$0.583 of fuel per mile at 6.6 MPG and $3.85 a gallon.
Cost per loaded mile and the rate you need
Rates are usually quoted on loaded miles, but you pay for every mile, empty ones included. Divide total cost by loaded miles (F) to see what a paying mile has to cover. Deadhead here is =1-SUM(F2:F8)/SUM(E2:E8), 14.1% of all miles.
To earn a margin, divide by 1 minus the margin in O7: =SUMIFS(L2:L8,D2:D8,"<>")/SUM(F2:F8)/(1-O7) gives $2.494 per loaded mile for 15%. The offered rate in O8, $2.45, covers cost ($2.120) but falls short of the target. Dividing by (1 - margin) is right when margin means profit as a share of revenue; multiplying cost by 1.15 gives a 13% margin, not 15%.
If you're paid a flat rate per mile by an employer or a customer, put that rate in a cell and compare it with your cost per mile in the same way. The rate is whatever your agreement or your tax adviser says; the spreadsheet only does the comparison. The trucking hub has profit per load with deadhead, and the cost per mile calculator template is a one-truck version you can download.
Result: 2.1199196597 shows as $2.120 with Currency format
$2.120 per loaded mile: 89,715 of costs over 42,320 loaded miles. Break-even on a loaded-mile rate.
Common mistakes
Summing odometer readings instead of the miles between them
An odometer reading is a position, not a distance. Miles = closing reading - opening reading, and each month's opening reading should equal last month's closing one; a gap means lost miles.
Keeping costs for a period with no miles recorded
A missing reading leaves costs on top and nothing underneath, so the rate jumps. Fix the reading or leave the row out of both sums (SUMIFS with D2:D8,"<>").
Averaging monthly or per-truck rates
A low-mile month gets the same weight as a full one. Sum costs and miles separately, then divide.
Leaving fixed costs out because they're "not per mile"
Payment, insurance and permits are part of what each mile costs. Spread them over the miles actually driven, and expect the rate to rise in slow months.
FAQ
What is the formula for cost per mile?
Total costs for a period divided by miles driven in the same period: =SUM(costs)/SUM(miles). Include fixed costs (payment, insurance, permits) and variable costs (fuel, driver pay, maintenance, tires).
How do I calculate fuel cost per mile in Excel?
Divide the price per gallon by miles per gallon: =price/MPG. At $3.85 and 6.6 MPG that is about $0.583 a mile. With litres and kilometres, divide the price per litre by kilometres per litre.
How do I calculate cost per mile for each vehicle?
Use SUMIFS twice: =SUMIFS(costs,vehicle,"T1")/SUMIFS(miles,vehicle,"T1"). In Excel 365, give SUMIFS a UNIQUE list of vehicles as the criteria to get every vehicle in one formula.
Why is my cost per mile so high this month?
Usually because miles fell while fixed costs didn't, a truck sat idle, or a reading is missing so costs have no miles against them. Check each month's opening reading matches the previous closing one.
Should I use loaded miles or total miles?
Use total miles for what the truck costs to run, and loaded miles to judge a rate that is paid only on loaded miles. The loaded-mile cost is always higher because it also covers the deadhead.
Functions used
SUMAdds numbersSUMIFSAdds with several conditionsSUMPRODUCTMultiply then addIFPicks a result from a testORTrue if any test is trueISNUMBERIs it a number?LETNames values inside a formulaUNIQUERemoves duplicates, spillsHSTACKJoins arrays side by side
Related guides
- How to calculate fuel cost and mileage in Excel
- How to make a mileage log in Excel
- SUMIF with multiple criteria in Excel (SUMIFS)
- How to calculate the break-even point in Excel
- How to calculate backorder rate in Excel
- How to calculate contact center shrinkage in Excel
- How to split bills between roommates in Excel
Updated