Practical expected-totals model to improve over under betting

This guide shows how to construct a lightweight expected-totals model using public statistics—xG, shots, pace, and offensive/defensive ratings—to help identify value in over under betting markets. It’s aimed at bettors who want a transparent spreadsheet they can test, tweak and use pre-match or in-play. The method prioritises simplicity and explainability over complex machine learning so you can audit every cell and adapt to different sports.

Why a compact expected-totals model helps with over under betting

Over under betting asks whether a game’s total goals/points will be above or below a line set by bookmakers. A small, auditable expected-totals model converts observable team data into an expected total and a probability distribution that you can compare with market odds. Benefits include:

• Clear inputs you can source from public sites (xG, shots per game, pace, offensive/defensive ratings).
• Fast spreadsheet calculations for many matches.
• Ability to validate and calibrate model bias against historical results.

Spreadsheet setup, core formulas and a football worked example

Required inputs and recommended cell layout

• Home_xG (A2), Away_xG (B2)
• Home_shots (C2), Away_shots (D2)
• Home_pace (E2), Away_pace (F2) — tempo metric or possessions
• Home_off_rating (G2), Away_off_rating (H2), Home_def_rating (I2), Away_def_rating (J2)
• League averages: League_xG_avg (single cell), League_pace_avg

Keeping these consistent across rows lets you copy formulas for many fixtures.

Core Excel/Sheets formulas (clear, copyable)

Start by producing attack and defence adjusted figures for each side. Example formulas assume the cell names above.

• Home_attack_adj = A2 * (G2 / League_off_avg)
  
  (Excel: =A2*(G2/League_off_avg))
• Away_attack_adj = B2 * (H2 / League_off_avg)
  
  (Excel: =B2*(H2/League_off_avg))
• Home_def_adj = (J2 / League_def_avg)
  
  (Excel: =J2/League_def_avg)
• Away_def_adj = (I2 / League_def_avg)
  
  (Excel: =I2/League_def_avg)
• Home_expected_goals = Home_attack_adj Away_def_adj (E2 / League_pace_avg)
  
  (Excel: =A2(G2/League_off_avg)(J2/League_def_avg)*(E2/League_pace_avg))
• Away_expected_goals = Away_attack_adj Home_def_adj (F2 / League_pace_avg)
  
  (Excel: =B2(H2/League_off_avg)(I2/League_def_avg)*(F2/League_pace_avg))
• Match_expected_total = Home_expected_goals + Away_expected_goals
  
  (Excel: =+)

Worked football example (pre-match numbers)

Example inputs: Home_xG 1.6, Away_xG 1.1, Home_off_rating 105, Away_off_rating 98, Home_def_rating 102, Away_def_rating 99, Home_pace 51, Away_pace 48. League averages: off 100, def 100, pace 50.

• Home_expected_goals = 1.6 (105/100) (99/100) * (51/50) ≈ 1.68
• Away_expected_goals = 1.1 (98/100) (102/100) * (48/50) ≈ 1.05
• Match_expected_total ≈ 2.73

With Match_expected_total ≈ 2.73, convert to probabilities using a Poisson or negative binomial assumption (covered later) and compare the implied market total (for example 2.5) to find potential value in over/under betting.

Next, the guide will apply the same spreadsheet framework to a basketball example (adjusting for possessions and pace differences), explain simple in-play adjustment rules, and show how to validate and stake the model output.

Basketball worked example and core spreadsheet formulas

Basketball is higher-scoring and better modelled with possessions/pace and offensive/defensive ratings (points per 100 possessions). Use the same spreadsheet pattern but compute expected points rather than goals.

Required inputs (example cell names)

• Home_off_rating (A2) — points per 100 possessions
• Away_off_rating (B2)
• Home_def_rating (C2) — opponent points allowed per 100 possessions
• Away_def_rating (D2)
• Home_pace (E2), Away_pace (F2) — possessions per 48/40 minutes
• League_pace_avg, League_off_avg (single cells)

Core formulas (Excel/Sheets)

• Home_off_adj = A2 (A2 / League_off_avg) — optional self-normalisation if needed (Excel: =A2(A2/League_off_avg))
• Home_expected_points = (A2/100) (E2 / League_pace_avg) (D2/League_off_avg) * League_points_per_game
  
  (Excel example: =(A2/100)(E2/League_pace_avg)(D2/League_off_avg)*League_pts_per_game)
• Away_expected_points = (B2/100) (F2 / League_pace_avg) (C2/League_off_avg) * League_pts_per_game
  
  (Excel example: =(B2/100)(F2/League_pace_avg)(C2/League_off_avg)*League_pts_per_game)
• Match_expected_total = Home_expected_points + Away_expected_points

Worked example

• Inputs: Home_off 112, Away_off 106, Home_def 108, Away_def 110, Home_pace 99, Away_pace 96, League_pace 97, League_pts_per_game 220.
• Home_expected_points ≈ (112/100)(99/97)(110/100)*220 ≈ 112.8 (note: formula multiplies components to get expected points allocation)
• Away_expected_points ≈ (106/100)(96/97)(108/100)*220 ≈ 107.4
• Match_expected_total ≈ 220.2

For basketball totals, use a normal approximation (see next section) to convert this expected_total to an over/under probability.

Choosing a probability model and converting to market probabilities

Pick a distribution that suits the scoring regime:

• Football (low counts): Poisson or negative binomial. Poisson is simplest: use the match expected goals (λ) and compute P(total > line) = 1 − sum_{k=0}^{floor(line)} e^{−λ} λ^k / k!. Excel: =1 – POISSON.DIST(floor(line), λ, TRUE).
• Football with overdispersion: use negative binomial by estimating a dispersion parameter from history; spreadsheets can use iterative fitting or approximate with variance > mean.
• Basketball (high counts): normal approximation is practical. Estimate mean = model_expected_total and sd from historical residuals for similar matchups or use league-season sd. Excel: =1 – NORM.DIST(line, mean, sd, TRUE).

Always calibrate the variance/dispersion term on historical data — probabilities are sensitive to sd choices.

Simple, actionable in-play adjustment rules

Keep in-play adjustments straightforward so they’re implementable live with spreadsheet formulas:

• Time-scaling baseline: compute pre-match per-minute rate = Match_expected_total / game_length_minutes. Remaining_base = pre_match_total – (elapsed_minutes * per_minute).
• Score and event adjustments: convert on-field events to short-term rate changes. Example rule: for each team, update expected_remaining_goals = Remaining_base (1 + alpha (current_xG_rate / pre_match_xG_rate – 1)), where alpha ∈ [0.2,0.5]. In Excel: =Remaining_base(1+0.3(current_xG_per_min/pre_match_xG_per_min-1)).
• Shot and big-event weighting: treat high-xG shots or red cards as discrete shocks — add/subtract a fixed expected-goals delta (e.g., +0.18 xG for a clear high-xG chance) rather than trying to fully re-run models in-play.
• Recompute distribution: replace pre-match mean by current_score + expected_remaining and re-evaluate over/under probability with Poisson (football) or normal (basketball).
• Practical thresholds: only react to in-play model edge changes larger than a grit threshold (e.g., implied probability swing ≥ 6–8%) to avoid overtrading on noise.

Model validation and backtesting tips

Robust validation separates useful edges from luck. Focus on:

• Out-of-sample testing: split historical fixtures into training and holdout sets. Tune variance/dispersion and alpha parameters only on training data; report holdout performance.
• Calibration: group predictions by model-implied probability (10% bins) and compare average actual frequency to implied probability. Use calibration plots and Brier score (Excel: =AVERAGE((pred – outcome)^2)).
• Sharpness and discrimination: ROC/AUC or simple hit rates for over vs under decisions. Track expected value (EV) per bet: EV = model_prob * (odds−1) − (1−model_prob).
• Transaction costs and limits: include vig/margin implied by market odds and simulate real-world bet sizing limits.
• Recordkeeping: log date, fixture, inputs, model probability, market odds, stake, result, and running bankroll. This enables honest ROI calculation and learning.

Staking strategy and identifying value

Two practical staking approaches work well for a simple expected-totals model:

• Fractional Kelly: compute Kelly fraction f = (bp – q)/b where b = decimal_odds − 1, p = model probability, q = 1 − p. Use fractional Kelly (e.g., 0.2–0.5 of f) to reduce volatility. Excel: =MAX(0,( ( (odds-1)p – (1-p) )/(odds-1) ))*0.5 for half-Kelly.
• Flat-percent staking: bet a fixed small % of bankroll (1–2%) on each identified value bet. Easier and often more robust for small sample sizes.

Value threshold: require model_implied_prob − market_implied_prob ≥ edge_threshold (typical starting threshold 4–6 percentage points). Combine with minimum odds (e.g., >= 1.80) and liquidity/limit checks.

Final remarks and next steps

Build the spreadsheet, backtest with clear records, and iterate conservatively. Prioritise calibration of variance and disciplined staking over chasing ever-more-complex inputs. With consistent testing and honest recordkeeping you’ll know whether the model produces a repeatable edge before scaling stakes.