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

Why do this by hand first#

OPR (Offensive Power Rating) is a linear-algebra estimate of how many points each team contributes to its alliance score on average. Work a tiny case by hand and there is nothing left to take on faith, which makes the spreadsheet version much easier to trust and to debug later.

The toy system#

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

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

Three equations, three unknowns, so the system is exactly determined. 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. That system is overdetermined, and it is usually inconsistent as well, meaning no set of values satisfies every match exactly. OPR takes the values that minimize the sum of squared errors instead. In matrix form, let M be the match-design matrix (rows = alliance-matches, columns = teams, 1 if the team is on that alliance) and let s be 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 one MMULT(MINVERSE(MMULT(TRANSPOSE(M),M)), MMULT(TRANSPOSE(M),s)) block does all of the OPR math. For component OPR (e.g., "coral OPR"), keep M identical but swap s for the coral-points-only column from the TBA score breakdown. That gives you an estimate of each team's coral contribution as well as its total points.

Reality check#

OPR assumes contributions are additive and independent. Defense breaks that assumption: a defender lowers the opponent's score, so a defensive robot shows up with a deceptively low or negative OPR. Cooperative tasks break it too. So OPR is a fast baseline you can build without any scouting, but it is not ground truth, and you should cross-check it against your own observed data. 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.