Build a Strength-of-Schedule-Adjusted Ranking in a Spreadsheet

You don't need Python to build a real opponent-adjusted rating. You need a spreadsheet, a list of game results, and about twenty minutes. By the end you'll have your own ranking that accounts for strength of schedule — the same core idea behind SP+ and the basketball ratings, stripped to its essentials. We'll use 2024 college football results; a companion script (scripts/sos-adjusted-ranking-spreadsheet.py) reproduces the exact numbers so you can check your work.

The idea in one sentence

A team's true quality is its average performance plus the average quality of who it played. So we'll rate every team by scoring margin, then nudge each rating by the ratings of its opponents — and repeat until it stabilizes. That "repeat until stable" loop is the whole trick. Let's do one full pass by hand.

Step 1: Get the games into a sheet

Make a tab called Games with four columns: Home, HomePts, Away, AwayPts — one row per game. You can type them, paste them from a results page, or export them from the CFBD API (see our CFBD tutorial). One tip that matters: cap the margin at something like ±28 points so a 56-point blowout doesn't count five times more than a two-touchdown win. Add two helper columns: HomeMargin = MIN(28, MAX(-28, HomePts - AwayPts)) and AwayMargin = -HomeMargin.

Step 2: Compute each team's raw rating

On a new Teams tab, list every team once in column A. A team's raw rating is its average margin across all games — home and away. The cleanest way is to stack every team-game into one long list (team, margin) and then use AVERAGEIF:

RawRating(team) = AVERAGEIF(AllTeamColumn, team, AllMarginColumn)

That's your starting point: a pure, schedule-blind power number. Sort by it and you'll see something's off — teams that beat up on weak schedules sit too high. Time to fix that.

Step 3: The adjustment — add opponents' ratings

For each team, compute the average raw rating of its opponents (an AVERAGEIF against your games list, looking up each opponent's raw rating). Then:

AdjustedRating(team) = RawRating(team) + AverageOpponentRawRating(team)

You've now rewarded teams that earned their margins against good opponents and discounted teams that padded against bad ones. Run on the 2024 season, the top of the list looks like this:

Watch Alabama jump (its margins came against a brutal SEC slate, so adjustment raises it) and Indiana fall from a top-three raw margin to roughly eleventh (a soft schedule, so adjustment lowers it — exactly the finding in our strength-of-schedule piece). The adjustment is doing visible, sensible work.

Step 4: Iterate (the part that feels like magic)

One pass uses opponents' raw ratings. But your opponents' true quality is better captured by their adjusted ratings — which you just computed. So do it again: recompute each team's adjustment using the new adjusted ratings instead of the raw ones. Then again. After a handful of passes, the numbers stop moving — they converge — and you've got a self-consistent rating where every team's number reflects the full web of who-beat-whom.

In a spreadsheet, you can do this by copying the adjusted column into the calculation and repeating, or by enabling iterative calculation (in the spreadsheet's settings) so the formulas can reference each other in a loop. Five iterations is plenty for a stable order.

What you've actually built

This simple model captures the essential logic of the professional systems. The real ones add refinements — home-field adjustments, diminishing returns on margin, preseason priors, per-play efficiency instead of final score — but the skeleton is identical: rate, adjust for opponents, repeat. Once you've built it yourself, those famous ratings stop being black boxes.

Make it your own

• Add home-field. Subtract ~2.5 points from the home team's margin before rating; it sharpens the results.
• Tune the cap. Try ±21 or ±35 and watch the order shift. There's no single "right" cap — that's a modeling choice you now control.
• Port it to basketball. Swap in per-possession margin and the same method gives you a tempo-free hoops rating.
• Check against the script. Run scripts/sos-adjusted-ranking-spreadsheet.py and compare its CSV to your sheet. If they match, you did it right.

That's the whole point of this site: the "advanced" numbers aren't magic, and you don't need a subscription to compute them. You need results, a cap, and the patience to hit recalculate a few times.