Excel is still the everyday tool of most IE departments. These ten formulas cover most of the work: bulletins, capacity studies, production reports and planning.
1. SUM and SUMIFS - totals with conditions
=SUMIFS(Output, Line, "Line-5", Date, TODAY()) adds today's output of Line 5. Use SUMIFS instead of filtering and adding by hand.
2. COUNTIFS - count with conditions
=COUNTIFS(Designation, "Operator", Status, "Absent") counts absent operators.
3. AVERAGEIFS - average cycle time
=AVERAGEIFS(CycleTime, Operation, "Collar attach") gives the average time of one operation from a list of study records.
4. XLOOKUP - find a value (Excel 365 / 2021)
=XLOOKUP(A2, Ops[Operation], Ops[SMV], "Not found") brings the SMV of the operation in A2 from your operations list. It looks left or right and has a built-in "not found" value.
5. INDEX + MATCH - the classic lookup
=INDEX(Ops[SMV], MATCH(A2, Ops[Operation], 0)) does the same in any Excel version. Always use 0 for exact match.
6. IFERROR - hide errors cleanly
=IFERROR(B2/C2, 0) shows 0 instead of #DIV/0! when there is no output yet.
7. ROUNDUP - machines and manpower
=ROUNDUP(SMV*Target/(60*Efficiency), 0) - you cannot plan 3.2 machines, so round up.
8. MAX and MIN - find the bottleneck
=MAX(CycleTimes) returns the bottleneck time; combine with =INDEX(Ops, MATCH(MAX(CycleTimes), CycleTimes, 0)) to show its name.
9. TEXT - clean labels
=TEXT(A2, "dd-mmm") turns a date into "29-Sep" for report headings.
10. NETWORKDAYS.INTL - working days with Friday off
=NETWORKDAYS.INTL(Start, End, 16, Holidays) counts working days when Friday is the weekly holiday (weekend code 16 = Friday only; 7 = Friday and Saturday). Use it for order lead time.
Tip: Turn every data list into an Excel Table (Ctrl + T). Formulas then use names like
Ops[SMV]and grow automatically when you add rows.