OEE Calculator in a Spreadsheet: AI Sorts Downtime From Notes

OEE is availability multiplied by performance multiplied by quality, each worked out from one shift's counts in a spreadsheet. A small factory can then use AI to read operators' free-text stoppage notes and assign each a downtime reason code, so the minutes total by cause and the biggest loss becomes obvious.

Keep the two jobs apart. Formulas should do the sums, because they give the same answer every run and can be checked. The AI should do the reading, because deciding that "waiting on coil from stores" is a material shortage is a language task. Once you split it that way, a shift's OEE takes minutes, not an afternoon.

Follow me on Instagram@sagnikteaches

The three ratios, with one shift worked end to end

Take a small press line on a single shift. All figures are illustrative.

Connect on LinkedInSagnik Bhattacharya

Now the three ratios.

Subscribe on YouTube@sagnikteaches

• Availability is run time over planned production time: 387 / 450 = 86.0%. It captures every stop long enough to log.
• Performance is (ideal cycle time x total parts) over run time: (0.6 x 560) / 387 = 336 / 387 = 86.8%. It captures running slower than the ideal rate and any tiny stops nobody logged.
• Quality is good parts over total parts: 532 / 560 = 95.0%.

Multiply: 0.860 x 0.868 x 0.950 = 70.9%. There is a quick cross-check that every OEE sheet should carry: good parts x ideal cycle time / planned production time = (532 x 0.6) / 450 = 319.2 / 450 = 70.9%. The two routes give the same answer when the inputs are consistent, and when they do not, one of your inputs is wrong.

The useful reading of 70.9% is not the headline. It is that roughly 14% of planned time was lost to stops, 13% of the remaining time to slow running, and 5% of parts to scrap. Which of those is worth chasing is a downtime question, and that is where the notes come in.

The sheet layout that survives a year of use

Two tabs are enough. The first holds one row per shift per line; the second holds one row per stoppage.

Tab 1: Shifts

The MIN(1, ...) cap on performance matters. If your ideal cycle time is set too generously, a good shift can compute above 100%, and that tells you the ideal figure needs revisiting, not that the machine is superhuman. Add a conditional-format rule that colours any performance above 100% so someone looks.

Tab 2: Stops

The point of keeping the note as typed is that operators will not fill in a dropdown at the end of a rushed shift, but most will scribble a line. Letting them write freely and sorting afterwards is how you get a complete log. If your plant already records stoppages in a different system, export to this shape and the rest of the routine is the same. For general help building the sheet formulas with an assistant, see using ChatGPT with Excel.

Choose your reason codes before you ask the AI to use them

The AI will sort into whatever categories you give it. If you give none, it invents new ones each week and the totals never line up. Write the list down, with a one-line test for each, and treat it as a fixed vocabulary.

Two rules help the sort stay stable. First, one stop gets one code, and when a note describes two causes, the code is the cause that started the stop. Second, anything the AI cannot place goes to OTHER with Low confidence rather than being forced into the nearest category.

There is also a policy choice. Many plants count very short stops, often those under a few minutes, as a performance loss rather than an availability loss, because logging them is impractical. Pick a threshold if you want one, write it on the sheet, and apply it to every line. The example above logs everything, so every stop sits in availability.

The prompt that sorts the notes, with its output

Paste the day's stop rows (date, line, note, minutes) into the chat with the reason code table. Ask for structured output you can paste back.

You are classifying production stoppage notes written by machine operators.

Allowed reason codes (use exactly these): BREAKDOWN, CHANGEOVER, MATERIAL, JAM, QUALITY HOLD, NO OPERATOR, UTILITIES, CLEANING, OTHER.

For each row return: row number, reason code, confidence (High, Medium or Low), and a rewritten note of at most 8 words.

Rules:
- One code per row. If two causes are mentioned, choose the one that started the stop.
- Use OTHER with Low confidence if the note is unclear. Do not guess.
- Do not change the minutes.
- Return a table, then a second table that totals minutes by reason code, then the grand total of minutes.

Rows:
1 | Press 2 | jam at feeder, cleared | 5
2 | Press 2 | changed to 12mm die, took ages | 28
3 | Press 2 | waiting for steel coil from stores | 12
4 | Press 2 | motor tripped, electrician reset it | 9
5 | Press 2 | first off check held us up | 6
6 | Press 2 | sensor wiped, back on | 3

A realistic reply (illustrative):

The totals table should show JAM 8, CHANGEOVER 28, MATERIAL 12, BREAKDOWN 9, QUALITY HOLD 6, grand total 63. That matches the 63 minutes logged against the shift, and that match is your first check on every run.

What you would fix in that reply: row 6 might genuinely be a sensor fault rather than a jam, and the operator who wrote it can say. Rows marked Medium are worth a glance each; rows marked Low are worth a question the next morning. A reviewer who spends two minutes on the Medium and Low lines is doing the job a person should do, the cases where context matters.

Checks before you trust the totals

An AI sort can look right and still be wrong, so build three checks into the sheet rather than relying on attention.

1. Minutes reconcile. The totals the AI returns must equal the sum of the minutes you pasted. If not, rows were dropped or altered. A cell that reads =SUM(Stops!E:E)-SUM(coded minutes) should show zero.
2. Every code is on the list. Use data validation on the Reason code column, or a COUNTIF against the code list. Variants such as "Mechanical fault" or "Changeover/setup" slip in when the prompt is paraphrased.
3. Spot sample. Each week, pick five rows at random, read the original note and decide whether you would have coded it the same. If you disagree with more than one in five, tighten the code definitions, not the prompt wording.

When you move from a single day to a full week, paste in batches by line or by day so the output stays short enough to read in full. Very long outputs are where models start dropping rows.

Turning coded stops into a Pareto you can act on

Once the Reason code column is filled, a pivot table (or SUMIFS by code) gives minutes lost by cause. Sort largest first and add a cumulative percentage column. This is a Pareto table, and it is the single most useful output in the workbook, because it tells you where the first hour of improvement work should go.

Illustrative figures for one line over five shifts:

The reading is concrete. Changeover is two-fifths of the lost time. A tidy-up of how tooling is staged beside the press, or a standard changeover checklist, is a better first move than a maintenance contract. The OTHER bucket is 5%, which is small enough to review by hand each Friday and learn from. Once a few weeks are coded, the same table is the starting point for reading downtime logs for repeat stops on one machine or one product.

You can also ask the AI to describe the pattern in two sentences for the weekly team meeting: "Changeover took 142 of 355 lost minutes. Three of the five changeovers on Tuesday and Wednesday were to the 12 mm die." That sentence only appears if the tool has the dates and notes; ask for it after the table, and read it against the table before you repeat it to anyone.

Four other places this pattern earns its keep

A bakery with one oven line

Notes such as "waiting for the dough to prove" and "tray rack not back from wash" sort into MATERIAL and a new code you add, WAITING ON PRIOR STEP. The bakery's lesson is often that the oven is not the constraint at all; the proving room is. Add a code when a cause recurs three weeks in a row and is not well described by the existing ones.

A plastics moulder with several machines

Add a Machine column and pivot by machine and code. One machine showing BREAKDOWN at three times the rate of its neighbours points at a maintenance conversation, not a general one.

A packing line that runs different pack sizes

The ideal cycle time changes by product. Keep a small lookup tab of product to ideal cycle and let column H fetch it with XLOOKUP or VLOOKUP. Without it, the performance ratio is meaningless on mixed days.

A job shop with no counters

If parts are not counted by machine, OEE performance is a guess. Start with availability only and the downtime codes, which need nothing but a clock and a note, and add counts when someone is ready to keep a tally. A partial measure kept every day beats a perfect one tried for a week. The ideas behind that are in predictive maintenance for a small workshop.

When a note defeats the AI

Operators write the way people talk, and a few patterns reliably cause wrong sorts.

• Shorthand and local names. "Waited on the B-bay trolley again" tells a model nothing. Provide a short glossary at the top of the prompt: machine nicknames, abbreviations, names of recurring materials.
• Notes that describe a symptom, not a cause. "Line stopped, no idea why" is OTHER. Do not let the model infer a cause. Next week's habit change, "write what you did to restart it", fixes more than a better prompt does.
• Two events in one note. "Jam at 10:15 then changed die" is two stops. Ask operators to log them separately, or split the row before you paste.
• Different spellings of the same cause. "Coil", "steel", "stock" and "raw" will all land correctly once the code definitions say what counts as MATERIAL, which is a reason to write the definitions in the prompt every time.

The honest limit is that the AI cannot see the line. It reads the note. If the note is poor, the code is poor. The routine improves as the notes improve, and the Low-confidence list is the quickest way to show an operator which notes help. Linked work on the quality side is in AI quality control for small manufacturers.

Setting the ideal cycle time without flattering yourself

Performance is only as honest as the ideal cycle time behind it. There are three common sources, and each leads to a different reading of the same shift.

• The machine's rated speed. Gives the lowest performance figures, because few plants run at plate speed for a full shift. Good for showing the theoretical gap.
• The routing or quoted time. The time you priced the job on. A shortfall here is a margin problem as well as an OEE one.
• Best demonstrated rate. The fastest sustained rate the line has achieved, for example its best hour of the last month. The most realistic, and the one that tends to be accepted on the shop floor.

Pick one source per product and keep it. Changing it mid-month makes the trend meaningless. If you want an AI check, paste a month of hourly counts and ask it to find the best sustained hour for each product, then confirm the figure with the supervisor before it goes into the lookup tab.

A quick sum on what a better changeover is worth

Using the Pareto above, five shifts of 450 planned minutes give 2,250 minutes, and 355 of them were lost to stops, so availability was (2,250 less 355) / 2,250 = 84.2%. Changeover accounted for 142 of those minutes. Suppose a staged-tooling routine cuts changeover time by a quarter: 35.5 minutes return to run time, availability moves to 1,930.5 / 2,250 = 85.8%, and at 0.6 minutes per part that is about 59 extra parts across the week. Do the sum with your own numbers before spending anything. If each part earns a few cents of margin, a day of redesign work may never pay back, while on a high-margin part it easily does.

The fortnight the numbers looked better than the line

In one illustrative case, a plant's OEE rose from 62% to 71% in a fortnight with no change on the shop floor. A new shift lead had begun logging only stops longer than ten minutes, because the note column was a nuisance. Availability jumped as the small stops vanished from the log. Performance should have fallen to absorb them, but the ideal cycle time had also been edited upward by mistake on two products, so it did not.

The AI sort could not catch this, because it saw only the notes it was given. The sheet caught it once someone looked: the count of stop rows per shift had halved, and performance hit the cap on four days. The remedy was to agree the logging threshold, put it in a cell on the sheet, and show the count of stops per shift beside OEE, so a sudden fall in the count draws a question. A measure that rises because the recording changed is worse than no measure at all.

A weekly routine that takes about 20 minutes

1. End of each shift: the shift lead enters counts, rejects and the stop notes on the sheet. Five minutes.
2. Friday: filter the Stops tab for the week, copy the uncoded rows, paste into the chat with the standing prompt. Review the table, the totals and any Low rows. Ten minutes.
3. Paste back the codes, run the three checks, refresh the Pareto. Five minutes.
4. Monday meeting: read the top two causes, not the whole table, and agree one action for each.

Keep the standing prompt as the instructions of a saved Project (ChatGPT or Claude), with the code table as a project file, so nobody rewrites them from memory. OpenAI is retiring custom GPTs across its plans, so avoid building a routine that depends on one. A broader way to choose between assistants is in the AI guide.

After a month, ask three questions of the numbers. Is OEE moving for the right reasons, or did someone change the ideal cycle time? Has the share of OTHER fallen below 10%? Did the action you picked for the top cause change that cause's minutes? If yes, extend to a second line. If not, the notes or the codes need another look before more automation helps.

OEE spreadsheet questions from small factories

What is a good OEE figure for a small factory?

There is no single right figure, because it depends on the process, the product mix and how strictly you define planned time. Track your own line for a month, find the weakest of availability, performance and quality, and aim to lift that one. Comparing with another plant's number is only fair if both define time the same way.

Should planned breaks count as downtime?

No. OEE is normally measured against planned production time, so scheduled breaks, planned maintenance and no-demand periods come out first. If you leave them in, the figure looks worse but it measures your rota, not your machine. Decide once, write the rule down and keep it for every shift.

Can AI calculate the OEE itself?

It can, but the arithmetic is better done by spreadsheet formulas, which give the same answer every time and can be audited. Use the AI for the part a formula cannot do: reading free-text notes and assigning a reason code. Then check its minutes add up to the logged downtime.

How many downtime categories should I use?

Between six and ten covers most small plants. Fewer hides the problem, more makes operators and the AI inconsistent. Keep an Other category, review everything that lands in it every week, and promote a repeated Other reason into its own category only when it recurs.

Is it safe to paste operator notes into a chat assistant?

Notes about stoppages rarely hold personal data, but check them for customer names, part numbers you treat as confidential and anything about named staff. Use a business plan, or turn off training in a consumer account (in ChatGPT the setting is "Improve the model for everyone" under Data controls), and remove names before pasting.

Further reads

• AI Maintenance Costs: What You Pay After an Automation Goes Live — What running an automation costs once it is live.
• How to Add Human Approval Steps to AI Automations — Keep a person checking AI sorting before totals are used.
• Daily Production Report: Summarise Counts, Scrap and Stops With AI — Summarise each day's counts, scrap and stops for the team.
• AI Shift Scheduling vs a Rota Spreadsheet for Small Teams — Rota changes that cut no-operator downtime.
• Small Factory CMMS With AI: Does the AI Layer Earn Its Cost? — When breakdown minutes justify a maintenance system.
• Automation in Clothing Manufacturing: What a Small Workshop Can Automate — What a small workshop can automate first.
• AI in Plant Engineering: Breakdown Logs and Maintenance Plans — Turn a messy breakdown log into ranked repeat faults and a draft preventive maintenance plan, with the safety items left to a named engineer.
• Factory Energy Audit With AI: Find Peak Loads and Idle Waste — A step-by-step factory energy audit from a half-hourly meter export: find the peak, the idle baseload, and what to hand an auditor.
• Preventive Maintenance Schedule Template: AI Builds It From Manuals — Feed your machine manuals to AI, pull out every service interval with its page number, and turn them into a checked maintenance calendar.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: the standard OEE definition (availability x performance x quality) as used in lean manufacturing practice; Google Sheets function list and Microsoft Excel function list (SUMIFS, XLOOKUP, VLOOKUP, COUNTIF); OpenAI help centre custom GPT retirement FAQ and data-controls article; Claude privacy centre (model-training setting); all checked 5 November 2026. Figures in the examples are illustrative.