Project 2: Compute OPR by Hand, Then in a Spreadsheet

Why do this by hand first

OPR (Offensive Power Rating) is just a linear-algebra estimate of how many points each team contributes to its alliance score on average. Computing a tiny case by hand removes the magic so you can trust (and debug) the spreadsheet version later.

The toy system

Imagine three teams A, B, C and three 2-robot matches. Each match's alliance score equals the sum of the two robots' contributions:

Match 1: A + B = 40
Match 2: A + C = 30
Match 3: B + C = 50

This is exactly determined (3 equations, 3 unknowns). Solve it:

• Add all three: 2A + 2B + 2C = 120, so A + B + C = 60.
• Subtract each match: C = 60 - 40 = 20; B = 60 - 30 = 30; A = 60 - 50 = 10.

So OPR(A)=10, OPR(B)=30, OPR(C)=20. Sanity check Match 1: 10 + 30 = 40. Correct.

Why real OPR needs least squares

At a real event each team plays roughly 6-12 qualification matches, so you get far more equations than teams. The system is overdetermined and usually inconsistent (no exact solution), so OPR finds the values that minimize the sum of squared errors. In matrix form, with M the match-design matrix (rows = alliance-matches, columns = teams, 1 if the team is on that alliance) and s the vector of alliance scores, the least-squares OPR vector solves the normal equations:

(Mᵀ M) · opr = Mᵀ s

Doing it in Google Sheets

You do not need to invert anything manually. Sheets has the matrix functions:

1. Build matrix M: one row per alliance-match, one column per team, with 1s for the three teams on that alliance.
2. Put the alliance scores in vector s.
3. Compute A = MMULT(TRANSPOSE(M), M) and b = MMULT(TRANSPOSE(M), s).
4. Solve opr = MMULT(MINVERSE(A), b).

That single MMULT(MINVERSE(MMULT(TRANSPOSE(M),M)), MMULT(TRANSPOSE(M),s)) block is the entire OPR engine. For component OPR (e.g., "coral OPR"), keep M identical but swap s for the coral-points-only column from the TBA score breakdown. Now you can estimate each team's coral contribution, not just total points.

Reality check

OPR assumes contributions are additive and independent, which breaks for defense (a defender lowers the opponent's score, so it shows up as a deceptively low or negative OPR) and for cooperative tasks. Treat OPR as a fast, no-scouting baseline you cross-check against your own observed data, not as ground truth. The Blue Alliance publishes OPR/DPR/CCWM per event so you can compare your hand-built numbers against theirs to confirm your matrix is correct.