Quarterback Return Impact: Spreadsheet Template and Dataset for Coaches & Analysts

Quick hook: Fixing the time-sink coaches and analysts face after a QB injury

When a starting quarterback returns from injury, coaching staffs and analytics teams scramble to answer the same questions: How has the QB’s completion % changed? Is the offense producing the same expected points added (EPA) per play? What is the net win impact of the return? Searching for reliable, reproducible answers shouldn't be a weeks-long project. This guide gives you an actionable Excel/Google Sheets spreadsheet template, formulas, sample data, and methods to quantify QB return effects for NCAA programs and other levels in 2026.

The elevator summary (most important first)

Use the included template to calculate three coach-ready KPIs after a QB returns from injury:

• Completion % with Bayesian smoothing to avoid small-sample noise.
• EPA per play (team and QB-level) using a lookup table or play-by-play pre/post comparison.
• Win-impact (WPA / net win probability change) using a logistic approximation or calibrated lookup table.

You'll also get comparison logic (pre-injury vs. post-return), opponent adjustment, and guidance on interpreting results when sample sizes are small — essential in late 2025 / early 2026 when transfer portal churn and schedule imbalance make direct comparisons misleading.

Why this matters in 2026: trends and context

Two developments have changed how teams should measure QB returns in 2026:

• Wider availability of college-level play-by-play and tracking proxies. By late 2025, more collegiate datasets were standardized for public use, enabling team-level EPA models beyond pro datasets.
• Analysts increasingly combine traditional counting stats with model-based metrics (EPA, WPA) to inform in-game decisions and recovery protocols. That trend makes a reproducible spreadsheet model a practical coach tool.

What this template includes (overview)

• Play-by-play sheet: canonical columns and sample data to paste from NCAA box data.
• EPA lookup table: expected points by down/yardline/to-go bucket.
• Formulas sheet: cell formulas you can copy into Excel or Google Sheets.
• Summary dashboard: pre-injury vs post-return metrics, confidence intervals, opponent-adjusted deltas.
• Win-impact approximation: logistic function or lookup-based WPA estimate for each play.

Template structure and sample data (paste-ready)

Start with a single PlayByPlay sheet. Use this column order to make formulas portable:

1. Date (A)
2. GameID (B)
3. Quarter (C)
4. TimeRemainingSec (D)
5. Team (E)
6. Opponent (F)
7. Down (G)
8. ToGo (H)
9. YardLine (I) — offense yards from own end-zone
10. PlayType (J) — Pass/Run/Drop/FieldGoal/Punt
11. PassResult (K) — Complete/Incomp/INT/TD/NB (NA)
12. YardsGained (L)
13. AirYards (M) — blank for runs
14. EPA_pre (N) — expected points before play (lookup)
15. EPA_post (O) — expected points after play (lookup)
16. EPA_play (P) — =O2-N2
17. QB (Q) — name
18. IsStarter (R) — TRUE/FALSE for the QB who returned
19. ScoreDiffBefore (S) — team points - opponent points before play

Sample rows (CSV-ready)

Date,GameID,Quarter,TimeRemainingSec,Team,Opponent,Down,ToGo,YardLine,PlayType,PassResult,YardsGained,AirYards,EPA_pre,EPA_post,EPA_play,QB,IsStarter,ScoreDiffBefore
2025-10-01,G1,2,330,OKLA,TEXA,1,10,35,Pass,Complete,14,10,0.45,1.15,0.70,John Mateer,TRUE,3
2025-10-01,G1,2,290,OKLA,TEXA,1,10,25,Run,NA,3,,0.60,0.40,-0.20,John Mateer,TRUE,6
2025-11-15,G8,3,800,OKLA,UTAH,2,7,50,Pass,Incomp,0,8,0.10,-0.50,-0.60,Backup QB,FALSE,-1
2026-01-10,G14,1,900,OKLA,ALAB,1,10,30,Pass,Complete,22,18,0.55,1.40,0.85,John Mateer,TRUE,0
  

Core formulas explained (copy into Excel/Sheets)

Below are copy-paste-ready formulas. Replace row numbers with your sheet's rows. Columns correspond to the layout above.

1) EPA per play

EPA needs lookup tables or a model. Two practical options:

• Quick approach: Use a down/yardline/to-go bucket lookup table. Add a sheet called EPA_table with keys like Down|ToGoBucket|YardlineBucket and values for expected points. Then:

Cell N2 (EPA_pre): =VLOOKUP(G2 & "|" & Bucket(H2) & "|" & Bucket(I2), EPA_table!$A:$B, 2, FALSE)
Cell O2 (EPA_post): =VLOOKUP(G_after & "|" & Bucket(H_after) & "|" & Bucket(I_after), EPA_table!$A:$B, 2, FALSE)
Cell P2 (EPA_play): =O2 - N2

Where Bucket(x) is a formula or helper column that groups ToGo and YardLine into reasonable bins (e.g., 1-3, 4-7, 8+; or 0-20, 21-40, 41-59, 60-80, 81-100).

For automated post-play keys, you can compute:

YardLine_after = IF(J2="Pass", I2 + L2, I2 + L2)
ToGo_after = IF(L2 >= H2, NextSeriesToGoValue, MAX(1,H2 - L2))
Down_after = IF(L2 >= H2, 1, MIN(4,G2+1))

2) Completion % (with Bayesian smoothing)

A small number of post-injury attempts can make completion % swing wildly. Use a Beta prior to smooth. Choose a prior weight (n0) — 30 attempts is a conservative prior for college. If league average completion is 0.62 in 2025, then:

alpha_prior = 0.62 * n0
beta_prior = (1-0.62) * n0
successes = COUNTIFS(Q:Q, "John Mateer", J:J, "Pass", K:K, "Complete")
attempts = COUNTIFS(Q:Q, "John Mateer", J:J, "Pass")
Posterior completion % = (alpha_prior + successes) / (alpha_prior + beta_prior + attempts)

In Excel/Sheets you can implement directly in a summary cell:

= (0.62*30 + SUMPRODUCT((Q2:Q1000="John Mateer")*(J2:J1000="Pass")*(K2:K1000="Complete"))) / (30 + SUMPRODUCT((Q2:Q1000="John Mateer")*(J2:J1000="Pass")))

3) Team EPA per play and QB-contribution

Aggregate team EPA per play and QB-influenced share:

Team_EPA_per_play = SUMIF(E:E, "OKLA", P:P) / COUNTIF(E:E, "OKLA")
QB_EPA_per_play = SUMIFS(P:P, Q:Q, "John Mateer") / COUNTIFS(Q:Q, "John Mateer")
QB_share_of_team_EPA = QB_EPA_per_play / Team_EPA_per_play

4) Win-impact (approximate WPA per play)

Gold-standard win probability models require large calibration sets. For in-sheet approximations in 2026, use a simple logistic model with these predictor inputs: ScoreDiffBefore (S), TimeRemainingSec (D), Possession (Team), FieldPosition (I). A pre-calibrated set of coefficients (recommended starting point — recalibrate with your data) is:

WP = 1 / (1 + EXP(-(B0 + B1*ScoreDiff + B2*(TimeRemainingMin) + B3*(YardLine/100) + B4*IsHome)))
Suggested coefficients: B0 = 0.05, B1 = 0.12, B2 = -0.008, B3 = 0.9, B4 = 0.03

In Excel (convert seconds to minutes):

=1/(1+EXP(-($B$0 + $B$1*S2 + $B$2*(D2/60) + $B$3*(I2/100) + $B$4*IF(TeamIsHome,1,0))))
Cell WINPROB_pre = formula with EPA_pre context
Cell WINPROB_post = formula with EPA_post context
Cell WPA_play = WINPROB_post - WINPROB_pre

Then aggregate WPA across the post-return window to estimate net win impact of the QB's presence.

Pre-injury vs post-return comparison workflow

Follow these steps for rigorous comparison:

1. Define windows: e.g., the 6 games before injury vs first 6 games after return (adjust for sample size).
2. Filter your PlayByPlay sheet by QB and date ranges.
3. Compute smoothed completion %, EPA/play, and WPA totals for each window.
4. Adjust for opponents: compute opponent-adjusted EPA by subtracting opponent average EPA allowed per play (league or schedule-weighted).
5. Statistically test differences: for completion % use a z-test on proportions (account for Bayesian prior); for EPA/play use Welch's t-test if samples moderate (or bootstrap if small).

Example Excel summary formulas

Pre_attempts = COUNTIFS(Q:Q, "John Mateer", A:A, "<="&InjuryDate)
Post_attempts = COUNTIFS(Q:Q, "John Mateer", A:A, ">"&ReturnDate)
Pre_EPA_per_play = SUMIFS(P:P, Q:Q, "John Mateer", A:A, "<="&InjuryDate)/Pre_attempts
Post_EPA_per_play = SUMIFS(P:P, Q:Q, "John Mateer", A:A, ">"&ReturnDate)/Post_attempts
EPA_delta = Post_EPA_per_play - Pre_EPA_per_play

Small-sample adjustments and best practices

Key limitations and how to mitigate them:

• Small sample noise: Use Bayesian smoothing for rates and shrinkage toward team or league means for EPA.
• Opponent quality: Weight opponent adjustments by opponent's EPA allowed per play — schedule strength matters more in college.
• Role changes: If the offense changed scheme or personnel after return, quantify scheme shift using play-type mix (pass%/run%) and air-yard distribution.
• Contextual confounding: Special circumstances (weather, bowl games, playoff pressure) should be flagged and optionally excluded for unbiased estimation.

Interpreting results — practical coach-focused thresholds

Use these heuristics tailored for NCAA-sized samples in 2026:

• Completion %: A smoothed drop of 3–5 percentage points over a 6-game post-return window signals a meaningful change; validate with opponent adjustment.
• EPA/play: A delta of +/- 0.05 EPA/play equates to roughly 0.5-1 expected win over a full 12-game season (rule of thumb — context dependent).
• Net WPA: Positive aggregate WPA in the post-return window suggests the QB is adding immediate game-winning value; negative WPA indicates decline that may necessitate rotation or play-calling change.

Advanced options for analysts

For teams with tracking or richer play tags (RPO indicator, pass concept), extend the template:

• Split EPA by route depth (AirYards bins) to detect reduced arm strength or timing issues post-injury.
• Model lagged recovery: add a game-number-since-return variable and fit a linear or exponential recovery curve to EPA/play.
• Predictive alerting: add conditional formatting to the dashboard to flag when smoothed metrics cross thresholds (e.g., smoothed completion < league mean - 0.04).

Real-world example: Oklahoma QB return scenario (how to apply to a real roster)

Suppose an NCAA starter (e.g., John Mateer) returns from a hand injury in 2026. Load play-by-play for 6 games pre-injury and 6 games post-return into PlayByPlay. Run the template to get:

1. Smoothed completion % pre vs post (Bayesian posterior).
2. EPA/play for both windows and opponent-adjusted EPA delta.
3. Aggregate WPA for each period and net impact on wins.

If the QB shows a drop in deep-pass air-yard EPA but stable short-pass EPA, the coaching staff can emphasize short passing concepts while ramping up deep shots in controlled reps during practice. That is the actionable insight this template is designed to produce quickly.

Implementation checklist for coaches & analysts

• Collect play-by-play CSVs for the relevant seasons/games and paste into the PlayByPlay sheet.
• Fill the EPA_table sheet using league/season average values or derive from historical NCAA play-by-play.
• Set your smoothing priors (n0 for completion %, prior mean for EPA/play).
• Run the summary and inspect both raw and smoothed metrics.
• Document confounders (injury severity, scheme change) in a notes field for presentation to coaches.

Methodology notes & transparency

Model assumptions: The simplified EPA lookup and logistic WPA approximations work for rapid assessment, not as absolute truth. They trade precision for speed and reproducibility in environments (like many NCAA programs) where full tracking-level calibration is unavailable.

Recalibration recommendation (2026): Recalibrate EPA_table and logistic coefficients annually with the latest season’s play-by-play. Late 2025 data drift (rule changes, play-calling evolutions) means 2024 coefficients are less optimal for 2026 analysis.

Actionable transparency: always present both raw and smoothed metrics, and flag when sample sizes are < 50 pass attempts post-return.

Limitations and cautions

• Small post-return sample sizes can produce misleading point estimates — use confidence intervals and smoothing.
• Injury recovery is multidimensional: metrics measure on-field production but not practice readiness or long-term medical risk.
• EPA and WPA are context-dependent. Use opponent adjustments to prevent schedule-induced misinterpretation.

How to extend the template to Google Sheets

All formulas above are compatible with Google Sheets, except VBA macros. Use Apps Script only if you want automated data pulls. For VLOOKUP speed, consider INDEX/MATCH or the new XLOOKUP in Excel. For Sheets, use FILTER + AVERAGE instead of SUMPRODUCT for readability.

Actionable takeaways (what to do in the next 48 hours)

1. Download or create the PlayByPlay CSV for the last 18 months of your team and opponents.
2. Paste it into the template and populate EPA_table with league-season averages.
3. Compute smoothed completion %, EPA/play, and aggregate WPA for the pre/post windows.
4. Share a one-page dashboard with coaches highlighting: delta in EPA/play, smoothed completion delta, and net WPA (with opponent adjustment).
5. If metrics suggest performance drop, present targeted practice focus (e.g., short passing schematic emphasis) rather than immediate lineup change — unless WPA shows consistent negative results.

Next steps and call-to-action

Want the copy-and-paste template? Use the column map and formulas in this article to build your own workbook. For a ready-made file and example dashboards tailored to NCAA teams, subscribe to our analytics newsletter or contact our data team for a premade Excel/Sheets workbook you can deploy inside 1 hour. Leave your email or request a download link — we'll send the workbook plus a short walkthrough video demonstrating pre/post workflow using a 2025-2026 game sample.

In 2026, quick, robust, and reproducible quarterback-return analysis is a competitive advantage. Build the spreadsheet, validate with your historical data, and iterate annually — that's how coaches turn stats into winning adjustments.

Related Reading

• How Receptor Science Could Transform Aromatherapy for Deeper Calm
• 50 MPH on Two Wheels: How Fast E‑Scooters Like VMAX Change City Riding
• From FedRAMP to Creator Trust: Why Enterprise Security Matters for Voice Platforms
• Deploying FedRAMP-Approved AI: Lessons from BigBear.ai’s Platform Acquisition
• Collectible Car Badges, Small Trim and Auction Finds: How to Spot Value Like an Art Buyer