MTBF and MTTR Formulas in Excel: Build Your Own Reliability Calculator

A spreadsheet request that starts most reliability programs

A plant manager asks for "the MTBF numbers" before next month's capital planning meeting. The maintenance manager has a CMMS work-order export, a stack of failure tickets, and no single place that turns those into a number anyone can defend. The honest answer — "I can build that in about twenty minutes" — is true, but only if the formulas go into the right cells in the right order. Get the time-bucket wrong, mix calendar hours with run hours, or forget to exclude planned downtime, and the number that goes into the capital plan is quietly wrong.

This is the moment most people reach for a template. Before you do, it helps to build the logic yourself at least once — not because Excel is the long-term answer, but because understanding exactly what MTBF and MTTR are dividing, and why, is what lets you catch a bad number later instead of reporting it to your plant manager with confidence.

This article walks through the exact cell-by-cell setup: the MTBF formula, the MTTR formula, how they combine into availability, and how to extend the same sheet into OEE. It ends with a straightforward look at where a hand-built spreadsheet holds up and where it starts to cost you more time than it saves.

The MTBF formula, cell by cell

MTBF — mean time between failures — is total operating time divided by number of failures. The formula itself is one line. The setup that feeds it correctly is the part worth getting right.

Lay out your sheet with one row per failure event:

Column	Contents
A	Asset ID
B	Failure start date/time
C	Previous failure end date/time (or install/commission date for the first row)
D	Operating hours between B and C, excluding scheduled downtime
E	Repair duration for this failure (feeds MTTR, below)

Column D is where most spreadsheets go wrong. It should capture only the hours the asset was actually expected to be running — not wall-clock calendar hours. If a line runs two shifts and sits idle overnight by design, the overnight hours are not "uptime between failures"; they're excluded entirely.

With D populated for every failure event in your reporting period, the MTBF formula is:

MTBF = SUM(operating hours) / COUNT(failure events)

In Excel:

=SUM(D2:D15)/COUNTA(B2:B15)

Worked example. Say a conveyor motor logs four failures over a quarter, with operating hours between failures of 410, 505, 390, and 460 (summing column D for those rows gives 1,765 operating hours across 4 failure events). MTBF = 1,765 ÷ 4 = 441.25 hours between failures. That single cell is now defensible in a meeting, because every input that fed it is visible in the rows above it.

For a deeper walkthrough of what counts as a "failure" versus a stoppage, and how to handle assets with zero failures in the period, see how to calculate MTBF.

The MTTR formula, cell by cell

MTTR — mean time to repair — uses the same event log, just a different pair of columns. Add:

Column	Contents
F	Repair start timestamp
G	Repair end timestamp
E	=G2-F2, converted to hours

The MTTR formula is:

MTTR = SUM(repair hours) / COUNT(repair events)

In Excel:

=SUM(E2:E15)/COUNTA(E2:E15)

MTTR measures repair time, not response time. The clock in column E should start when work actually begins on the asset, not when the ticket was created — those two timestamps can differ by hours or days depending on parts availability and crew scheduling, and conflating them inflates MTTR in a way that misrepresents technician performance.

Worked example. Continuing the conveyor motor: the four repair durations are 3.5, 2.0, 6.5, and 4.0 hours. Sum = 16 hours across 4 events. MTTR = 16 ÷ 4 = 4.0 hours average repair time.

Decide up front whether "repair time" includes diagnostic time, parts-wait time, or only hands-on wrench time — and apply that definition consistently across every row, or the average becomes a blend of different things. The full breakdown of what to include and exclude is covered in how to calculate MTTR.

Combining MTBF and MTTR into an availability figure

Once MTBF and MTTR sit in their own cells, availability is a single additional formula:

Availability = MTBF / (MTBF + MTTR)

In Excel, referencing the cells where you calculated each figure above (say MTBF is in cell I2 and MTTR in cell I3):

=I2/(I2+I3)

Worked example. Using the figures above: 441.25 ÷ (441.25 + 4.0) = 441.25 ÷ 445.25 ≈ 0.991, or 99.1% availability. That figure describes the fraction of time the asset is available to run, given its historical failure and repair pattern — it says nothing about whether it was actually scheduled to run, or how well it performed while running. Those two additional layers are what OEE adds.

For the reasoning behind the formula — why it's MTBF over the sum rather than some other ratio — see availability calculation explained.

Extending the same workbook into OEE

Availability is one of three factors in overall equipment effectiveness:

OEE = Availability × Performance × Quality

Performance compares actual output rate to ideal rate; Quality compares good units produced to total units produced. Add two more columns to your log — actual units produced and good units produced per period, plus a planned/ideal cycle time — and the formulas extend naturally:

Performance = (Ideal Cycle Time × Total Units) / Run Time
Quality = Good Units / Total Units
OEE = Availability × Performance × Quality

Worked example. If the conveyor line's Performance works out to 0.90 and Quality to 0.97, then OEE = 0.991 × 0.90 × 0.97 ≈ 0.865, or roughly 86.5%. The three factors live in adjacent cells, each traceable back to its own raw inputs — which is the entire value of building this in a spreadsheet rather than copying a final number from a report.

See MTBF, MTTR, and OEE explained for how the three metrics relate conceptually, and how to calculate OEE for the full performance and quality derivations with their own worked examples.

Where the hand-built spreadsheet starts to break down

A single-tab workbook like the one above works well for one asset, or a handful of assets you're tracking by hand during a pilot period. It breaks down in a few predictable ways:

• Multiple assets, multiple tabs. Once you're past a handful of equipment items, keeping MTBF/MTTR/OEE logic consistent across copied tabs becomes its own maintenance job. A formula fixed on one tab quietly stays broken on the other nine.
• Date and shift-pattern errors. Operating-hours exclusions (column D above) are easy to get right once and easy to get subtly wrong the fiftieth time you copy a row down, especially across shift changes, holidays, or partial-week data.
• No persistence across reporting periods. A spreadsheet answers "what's my MTBF this quarter" well. It's less good at holding every quarter's number side by side for a trend view without manual archiving discipline.
• No audit trail on formula changes. If someone "fixes" a formula mid-quarter, there's often no record of what changed or when — which matters if a number gets challenged later.

None of this means the math is wrong — it means spreadsheets are a general-purpose tool being asked to do something closer to purpose-built tracking. That's a reasonable thing to outgrow, not a sign you built it wrong the first time.

It's also worth being explicit about what this kind of calculation — hand-built or templated — does not do. It doesn't generate a work order when MTBF drops below a threshold, dispatch a technician, or manage spare-parts inventory against a repair. Those are execution functions that belong in a CMMS. A reliability calculator, whether it's your own tab or a ready-made one, is a planning and reporting layer — it tells you what your numbers are and lets you model what they'd look like under different maintenance strategies. It was never meant to replace the work-order system you already run alongside it.

When a ready-made workbook saves you the setup time

If the formulas above are straightforward but the setup — the column structure, the exclusion logic, the multi-asset tabs, the OEE extension — is the part eating your afternoon, that's exactly what a pre-built MTBF/MTTR/OEE workbook is for. The underlying math doesn't change: same formulas, same worked logic shown above, just already wired into a structure that handles multiple assets and multiple reporting periods without you rebuilding column D from scratch for every new piece of equipment.

The MTBF / MTTR / OEE Calculator Workbook is built on exactly the formulas walked through in this article — nothing hidden, nothing proprietary about the math. What it saves you is the hour or two of structuring tabs, naming ranges, and testing edge cases (zero-failure assets, partial-period data, mixed shift patterns) that a one-off build tends to skip the first time through.

Whether you build it yourself this afternoon or start from the workbook, the formulas are the same three lines: MTBF, MTTR, and the availability ratio that connects them. Getting those three cells right is the foundation everything else — OEE, trend reporting, capital planning — gets built on.