OKR Sheets in Google Sheets: Scenario Routing That Works

Most OKR tutorials show you 4 rows of clean, hand-typed numbers. That's not your sheet on a Monday morning. This article covers the full pattern: scenario routing, baseline-adjusted progress, and what happens when your Current column imports as text strings with non-breaking spaces.

The Tab Layout That Makes OKR Sheets Work

A minimal OKR sheet lives across 3 tabs. One for tracking, one for assumptions, one for the scenario switcher.

OKR_Tracker — the tab you look at daily:

Revenue_Assumptions — 3 scenario columns, one row per driver:

Scenario — a single cell, B1, containing a dropdown validated to Bear,Base,Bull.

The CHOOSE formula in any calculated cell reads that dropdown and routes to the right column: =CHOOSE(MATCH(Scenario!$B$1,{"Bear","Base","Bull"},0), Revenue_Assumptions!B2, Revenue_Assumptions!C2, Revenue_Assumptions!D2).

When your surgical director (or CFO, or head of ops) says "run the pessimistic case," they flip one cell. Every downstream projection updates. That's the whole mechanism — and it's about 20 minutes to wire up once you have the assumptions tab structured.

Baseline-Adjusted Progress Your OKR Sheet Actually Needs

The naive progress formula is =F2/E2 — Current divided by Target. It produces a number, but it lies.

If your baseline was $650k and your target is $1.4M, and you're currently at $1.087M, the naive formula says you're at 77.6% of target. But you started at $650k, not zero. You've actually closed 59% of the delta you need to close: =(F2-D2)/(E2-D2) = (1,087,000 - 650,000) / (1,400,000 - 650,000) = 58.3%.

The difference isn't cosmetic. Using naive progress against a $5M ARR base target where the baseline was $4.35M turns a 77% headline into a 52% real progress number. According to a 2024 PwC CFO Pulse survey, 67% of finance leaders cite misleading progress metrics as a top contributor to late-stage planning surprises. Building the right denominator in costs 4 characters.

Put this in H2 of OKR_Tracker:

=IFERROR((E2-D2)/(F2-D2), "—")

Wait — that's inverted. The correct formula:

=IFERROR((F2-D2)/(E2-D2), "—")

The IFERROR matters. When E2 and D2 are identical (someone set target = baseline, which happens on new objectives before targets are finalized), you get a divide-by-zero. Without the wrapper, that cell goes red and your progress column looks broken. With it, you get a clean dash.

What Happens When Your Data Is Actually Messy

The Current column (F) is where the OKR sheet breaks in practice. If your actuals are hand-typed, you're fine. If they're imported from a CRM, ERP, or anything that exports CSV, you'll hit this regularly:

The cell reads 1087000, and =F2-D2 returns #VALUE!.

The cell is a text string. It looks like a number but it isn't. A quick check: =ISNUMBER(F2) returns FALSE. You can usually fix a whole column with:

=VALUE(TRIM(SUBSTITUTE(F2, CHAR(160), " ")))

The SUBSTITUTE is the part most cleaning guides skip. CHAR(160) is a non-breaking space — it looks identical to a regular space in the cell, and TRIM alone won't remove it. According to Google Sheets documentation, TRIM only removes standard ASCII space characters (code 32). Non-breaking spaces (code 160) pass straight through. If your actuals import from an EHR, CRM, or any system that generates Unicode output, this is probably happening to you already.

For the full case — mixed formats, blank rows, text that reads "108 %" with a trailing space before the percent sign — wrap the whole thing:

=IFERROR(
  VALUE(
    TRIM(
      SUBSTITUTE(
        SUBSTITUTE(F2, CHAR(160), ""),
        "%", ""
      )
    )
  ) / IF(SEARCH("%", F2&""), 100, 1),
  ""
)

This handles: percentage strings with spaces, non-breaking spaces, blank cells, and straight numeric values. Drop it in a helper column (call it Current_Clean) and point your progress formula there instead of directly at the import column.

For rows where the confidence rating hasn't been set yet — new objectives with no G2 value — the CHOOSE routing will error. Wrap it:

=IFERROR(
  CHOOSE(
    MATCH(G2, {"Bear","Base","Bull"}, 0),
    Revenue_Assumptions!B2,
    Revenue_Assumptions!C2,
    Revenue_Assumptions!D2
  ),
  Revenue_Assumptions!C2
)

No confidence set defaults to Base. That's a reasonable fallback, and it means a half-populated tracker doesn't break every row below the gap.

The OKR Sheet Formulas That Degrade Gracefully

Put together, a production-grade row in OKR_Tracker looks like this:

H2 — Baseline-adjusted progress:

=IFERROR((Current_Clean-D2)/(E2-D2), "—")

Scenario-routed target (if targets vary by scenario):

=IFERROR(
  CHOOSE(MATCH(G2,{"Bear","Base","Bull"},0),
    Targets!B2, Targets!C2, Targets!D2),
  Targets!C2
)

Gap to target in dollars:

=IFERROR(E2 - Current_Clean, "—")

The IFERROR wrappers aren't defensive programming theater — they're the difference between a tracker that fails silently (shows dashes) and one that fails loudly (propagates errors across 40 rows and makes the whole sheet unusable before a board meeting).

As of April 2026, the CHOOSE-with-MATCH pattern is the cleanest way to do scenario routing in Google Sheets without resorting to Apps Script or named ranges that break when tabs are renamed. A single confidence dropdown wired to 3 columns of assumptions will handle every OKR scenario your leadership team asks for.

Where ModelMonkey Fits In

The formulas above take about an hour to build. The ongoing maintenance — updating actuals, verifying the Current_Clean column is still parsing correctly after a new CSV format, adding rows when objectives change mid-quarter — is what consumes the next 5 hours per quarter.

If your OKR tracker lives in Google Sheets, ModelMonkey can read the OKR_Tracker and Revenue_Assumptions tabs and update actuals, add new objectives, or rewrite the Current_Clean formula if your import format changes. Type "update Q2 actuals from this table" and paste your export. It handles the formula inspection without requiring you to describe the sheet structure from scratch each time.

Try ModelMonkey free for 14 days — it works in both Google Sheets and Excel.