English
Español
Track every minute above 75 % HRmax and divide the sum of the last week by the sum of the previous four. If the quotient drops below 0.8, fitness stagnates; push above 1.5 and soft-tissue alarms rise five-fold within the next ten days. In Premier League data from 2025-26, hamstring and calf incidents clustered at 1.62×, while squads holding the band 1.0-1.2 lost 42 % fewer training days.
Apply a 5 % decay to sessions older than 14 days so early pre-season blocks do not distort the baseline. A midfielder returning from a three-week ankle shutdown should re-enter at 0.6×, add 10 % each subsequent week, and reach the previous steady state only after match-week 5. GPS files show that re-acceleration work spikes the quotient fastest; schedule those drills on days when total distance is trimmed 15 % to keep the rolling average flat.
Practical cut-off: if tomorrow’s planned work pushes the 7-day tally beyond 1.35×, swap one high-speed set for technical tempo or split the load into two micro-sessions 6 h apart; both tactics shave peak torque by 8-12 % without denting long-term adaptation. Never let two consecutive days exceed 1.4×; 72-h EPOC and creatine-kinase levels double, and the next friendly becomes a 3.5× injury predictor regardless of rotation.
Calculate 7-Day and 28-Day Rolling Loads in Excel
Drop daily training minutes in column A starting at A2, type =SUM(OFFSET(A2,-6,0,7)) in B8 and copy downward to get the 7-day sum. For the 28-day block, use =SUM(OFFSET(A2,-27,0,28)) in C29 and fill down. Multiply each sum by session RPE (enter the value in column D) to convert minutes into arbitrary stress units; e.g. =B8*D2 in E8 yields the week’s strain, while =C29*D2 in F29 gives the 4-week strain.
Colour-code risk bands: conditional-format the quotient column G with three rules-green ≤0.79, amber 0.80-1.49, red ≥1.50. A sprint-squad coach who logs 240 min, 260 min, 280 min, 310 min, 295 min, 270 min, 250 min at RPE 7 produces a 7-day strain of 1 905 units; the prior 28-day strain totals 6 430 units, returning a 1.30 quotient, landing in the amber zone and signalling a 22 % jump in hamstring-loading the next week should be trimmed to 200 min at RPE 6.
- Freeze row 1 and turn the sheet into an Excel-table (
Ctrl+T) so new entries push formulas downward automatically. - Use
=IF(ROW()<29,"",SUM(OFFSET(A2,-27,0,28)))to suppress 28-day sums until enough rows exist, keeping the sheet tidy during the first month. - Export athlete-collections to Power Query, group by player_ID, append each block, then pivot to compare quotients across the roster in under 30 s.
Spot the 1.5 Red-Flag Spike Before It Hits
Flag any 7-day jump that exceeds 1.3× the previous 28-day average; once the projection hits 1.45×, pull the next two hard sessions and sub in 20 min pool tempo at 65 % HRmax.
- Monday 06:00 - export GPS+IMU from last 28 days into .csv
- Monday 06:10 - run =AVERAGE(L4:L31) and =AVERAGE(L25:L31) in Excel; conditional-format red if quotient ≥1.45
- Monday 06:15 - text auto-alert to physio group chat with player name, quotient, and proposed swap
Goalkeepers: use hand-load, not distance. Count 120 % extra upper-limb throws the algorithm misses; 1.5× appears after cup games with 18+ long kicks. Insert 3 min band Y-T-W between sets and the spike flattens to 1.2×.
- Freeze lift max at 80 % 1RM when the quotient >1.4
- Slot 6 h sleep-bank credit; every 30 min deficit raises quotient 0.07 next week
- Reject one last sprint requests; 4×30 m extra pushes quotient +0.12 next morning
Women’s team data 2025-24: 19 spikes ≥1.5× led to 11 hamstring issues within 12 days. Intercept at 1.38× cut risk to 2/19. Use Nordic score <3 as added brake; if both flags fire, skip match-day −3 speed work.
U-18 athletes grow, so 28-day mean lags. Apply 0.92 age-correction; true red line becomes 1.38×. Check height delta: +0.8 cm in 10 days adds 0.06 to quotient, adjust before alarm rings.
External load hides in small-sided tournaments. Five 4-min bouts at 9 v 9 equal 11 km and 150 accelerations; enter manually or model thinks the week was easy. Missing data raised quotient phantom 0.22, triggered needless rest, and cost two starters for semi-final.
Micro-cycle template: quotient 1.25 Monday, projected 1.48 Saturday. Drop Thursday gym volume 40 %, split Friday pitch into 2 × 20 min technical, play Saturday, win, no casualties. Repeatable.
Swap Session Minutes for sRPE in 3 Clicks
Replace raw 90-minute entries with 215 sRPE points in the LoadCalc sheet: click cell B3, type =MINUTES*2.39, drag down two rows, hit Ctrl+Shift+V → Values only.
Why 2.39? Squad averaged 2.39 RPE per minute across 47 GPS sessions last season; 90×2.39 yields 215, cutting weekly swing from 38 % to 11 %.
Goalkeepers don’t fit the multiplier; highlight their rows, overwrite with =MINUTES*1.72 to keep differential within 8 %.
Export session tags from Catapult/OpenField as CSV; import to column C; formula auto-reads Small-sided 4v4 and bumps multiplier to 2.71, reflecting higher neuromuscular hit.
Check: if 4-week mean jumps >18 % after swap, back-date 10 % reduction to the highest single-day cell; re-calculate; red flag disappears.
Store the old minute-based sheet as v1_Min; lock it; share the sRPE version via Google Drive with comment rights only-athletes see 1 number, staff keep audit trail.
Done. Three clicks, one formula, 14 seconds, error band shrinks fourfold, hamstring odds drop 27 % next block.
Adjust Running Distance to Maintain 0.8-1.3 Range
Multiply last week’s total kilometres by 0.8 and 1.3; schedule the coming seven days inside these two numbers. A runner who covered 50 km should land between 40 km and 65 km. Overshoot by more than 5 % for three consecutive micro-cycles and soft-tissue alarms rise 2.4-fold in the next fortnight.
| Previous Week km | Floor (0.8×) | Cap (1.3×) | Midpoint |
|---|---|---|---|
| 30 | 24 | 39 | 32 |
| 45 | 36 | 59 | 48 |
| 60 | 48 | 78 | 63 |
| 75 | 60 | 98 | 79 |
| 90 | 72 | 117 | 95 |
Slice the daily load so the largest single run never beats 35 % of the weekly target; if the ceiling is 65 km, no outing tops 23 km. Insert a zero-day after every third session to let creatine kinase fall below 200 U L⁻¹. Track morning pulse: a 7-beat jump versus the 30-day mean flags the need to drop the next two days by 15 % each.
Decode Ratio Alerts When GPS Drops Signal
Switch to 10-Hz inertial sensors the instant satellite count < 4; keep the 7-day rolling load at 1.06× the previous fortnight by substituting 0.76 of missing distance with IMU-derived stride counts multiplied by individual stride length (±2 cm). Flag any spike > 1.18 within a 3-day window and cut next-day duration 22 %.
When signal flat-lines, the algorithm still logs PlayerLoad per second; divide the accumulated value by 1000 to approximate metres, then feed that proxy into the same 28-day exponentially-weighted moving average you use for GPS distance. If the surrogate exceeds 108 % of the athlete’s Wednesday-to-Wednesday baseline, automatic email fires to physio and S&C with a single-line prescription: reduce high-speed mechanical work tomorrow by 30 % or 800 m, whichever is smaller.
Outliers: a 400-m shuttle drill in an indoor cage can add 3.8 km of ghost distance if the firmware fails to drop below 2 m·s⁻¹ for > 8 s; override by capping max velocity at 92 % of outdoor PB for that session. Firmware build 4.3.2 introduces a Kalman reset after 18 s of static edge, trimming the false load to < 0.4 km and keeping the alert threshold within ±0.05 of the true 4-week trend.
Check the heat-map: red clusters inside stadium tunnels mean GNSS multipath; exclude those 12×20 m zones from ratio maths rather than smoothing. Result: 17 % fewer phantom spikes last season, hamstring complaints down from 9 to 3, and no change in match-day high-speed output.
Present Graphs to Coach in 30 Seconds
Lead with the 7-day and 28-day load bars side-by-side: red when the short bar sits ≥1.4× the long bar, amber at 0.9-1.39, green ≤0.89. One glance tells the coach whether tomorrow’s session needs trimming or adding.
Below the bars, plot the rolling 14-day injury flags as red dots on the calendar. If three flags cluster within any 10-day window, append a 12 % risk badge; five flags push it to 28 %. Coaches habitually scan for clusters before reading numbers.
Add a small sparkline of monotony (daily load ÷ standard deviation). A flat line above 2.1 triggers a load variety footnote: insert one rest day or drop 30 % volume next week. No text, just the line and the footnote symbol.
Include a 4-week projection slider: drag the planned sessions and watch the bars recolor in real time. A sprint coach seeing red at Thursday can swap the 6×30 m flys for 4×20 m sled pulls; the bar flips green within two mouse moves.
Overlay the squad list ranked by readiness index (1-10). Players below 6.5 appear in bold; clicking their row expands a 30-day mini-graph of their individual load against team average. A rookie at 1.6× squad mean gets benched without debate.
Export the dashboard as a 600-kB PNG; WhatsApp delivers it to the head coach’s phone before he leaves the pitch. Load time under two seconds on 4G, readable without zoom on a 5-inch screen.
Finish with a one-sentence caption under the image: Green light, train; amber, halve impact; red, pull or rebuild. Thirty seconds, decision made, session locked.