Recrew risk calculator in Excel

I built a quick Excel tool that flags likely recrews using train lineup error and the last 14 days of terminal dwell for origin and destination. On a 254-train sample in October, a 90-minute lineup error threshold plus average dwell reduced recrews 12% versus our prior rule of thumb. If this would help your crew office or extra board planning, I can share the template and the Power Query steps to ingest your lineup and dwell data.

‌⁠‍⁠​‍​‍‌⁠‌​​‍​‍​⁠‍‍​‍​‍‌‍‌⁠‌⁠​‍‌⁠​‍‌‍‍‍​‍​‍​‍⁠​​‍​‍‌‍‍⁠​‍​‍​⁠‍‍​‍​‍‌‍⁠‍‌‍‌‌‌⁠‌⁠‌‌⁠⁠‌⁠‌​‌‍⁠⁠‌⁠​​‌‍‍‌‌‍​⁠​‍​‍​‍⁠​​‍​‍‌‍‍‌‌‍‌​​‍​‍​⁠‍‍​‍​‍‌‍⁠‍‌‍‌‌‌⁠‌⁠​‍​‍​‍⁠​​‍​‍‌‍‌​​‍​‍​⁠‍‍​‍​‍​⁠​‍​⁠​​​⁠​‍​⁠‌‌​⁠​‌​⁠​‍​⁠​‌​⁠‍​​‍​‍​‍⁠​​‍​‍‌‍‍​​‍​‍​⁠‍‍​‍​‍‌‍​‌‌⁠‌​​⁠​⁠​⁠‌‍‌​‍​‌⁠​​‌​⁠‌‌‍​⁠‌​‌‌‌⁠‍‌‌‌‍‌‌⁠​​‌⁠‌‍‌⁠‌‍‌‍⁠‍​⁠‌‌​‍​‍‌⁠⁠‌​

We got better results using the 75th percentile dwell by terminal/day-of-week with a “90-minute lineup error” flag — similar to your October test. The 14-day average kept getting wrecked by one MOW tie-up or a bad derailment, , so a trimmed mean or a cap at the 95th cut the false alarms. Does your template let you toggle percentile vs average or weight weekends differently?

‌⁠‍⁠​‍​‍‌⁠‌​​‍​‍​⁠‍‍​‍​‍‌‍‌⁠‌⁠​‍‌⁠​‍‌‍‍‍​‍​‍​‍⁠​​‍​‍‌‍‍⁠​‍​‍​⁠‍‍​‍​‍‌⁠​‍‌‍‌‌‌⁠​​‌‍⁠​‌⁠‍‌​‍​‍​‍⁠​​‍​‍‌‍‍‌‌‍‌​​‍​‍​⁠‍‍​⁠‍​​⁠​‍​⁠‌‌​⁠‌‌​‍⁠​​‍​‍‌‍‌​​‍​‍​⁠‍‍​‍​‍​⁠​‍​⁠​​​⁠​‍​⁠‌‌​⁠​‌​⁠​‍​⁠​‍​⁠​⁠​‍​‍​‍⁠​​‍​‍‌‍‍​​‍​‍​⁠‍‍​‍​‍‌​‌​‌‍‍‌‌‍‍‍‌⁠‍‍​⁠‌‌‌​‌⁠‌​⁠​‌​​‌‌⁠​⁠‌​‌⁠‌‍‌‍‌⁠​‌‌​​‌‌‍​⁠‌⁠​​​⁠​‍​‍​‍‌⁠⁠‌