Ulwick Opportunity Algorithm (With Sheets Template)

As an Amazon Associate I earn from qualifying purchases. Product links on this page are affiliate links — they cost you nothing extra.

⏱ 20 min read

The Ulwick Opportunity Score Formula and Thresholds

Tony Ulwick’s Opportunity Algorithm calculates customer need priority using the mathematical formula \(\text{Opportunity} = \text{Importance} + \max(\text{Importance} – \text{Satisfaction}, 0)\), with both inputs measured on a 1-to-10 scale. Scores greater than 12 identify underserved market outcomes, scores between 10 and 12 represent appropriately served outcomes, and scores below 10 flag overserved areas prime for cost reduction. Any score exceeding 15 marks an extreme market deficit that justifies immediate capital allocation.

A desired outcome is a stable, measurable statement describing how a customer defines success when executing a specific task, independent of any current technology.

In his 2005 book What Customers Want, Strategyn founder Tony Ulwick showed that traditional voice-of-the-customer research fails because it weights importance and satisfaction equally. Ulwick tested the algorithm across 83 business initiatives, demonstrating that successful product launches jumped from an industry baseline of 17% to an 86% success rate when teams addressed outcomes with scores above 12. If you need to turn vague customer interview transcripts into quantifiable metrics before scoring them, run them through a VOC Translation Matrix (With 5-Step Template).

The formula relies on a zero floor via the mathematical operator \(\max(\dots, 0)\). Without this threshold, high customer satisfaction would actively drag down the overall score of critical tasks. For instance, consider a core outcome rated 9 out of 10 in importance and 10 out of 10 in satisfaction. Without the floor, an unadjusted formula would calculate \(9 + (9 – 10) = 8\). A score of 8 would wrongly label a vital job as overserved, tempting engineering teams to strip resources away from the very feature keeping churn low. The zero floor caps the penalty at zero, giving that outcome a safe baseline score of 9.

The algorithm segments customer needs into three primary strategic bands based on empirical survey data:

When scores sink below 10, buyers refuse to pay a premium for incremental improvements. These low scores pinpoint features ripe for budget cuts, which you can prune using a structured TRIZ Trimming: Component Pruning Matrix (Excel Template). According to research published by Clayton Christensen in the Harvard Business Review, overserved customer tiers serve as the primary breeding ground where low-cost competitors enter an established market. Identifying clusters of scores below 10 helps product teams spot disruptive innovation threats before budget-focused competitors undercut their prices.

Once you know which tier an outcome occupies, you can write the spreadsheet formulas that automate this scoring across hundreds of survey respondents at once.

Table of Contents

• The Ulwick Opportunity Score Formula and Thresholds
• How to Standardize Importance and Satisfaction Survey Data
• Step-by-Step Google Sheets Formula Setup
• Translating Opportunity Scores into Innovation Strategy
• Copy-Paste Google Sheets Formula Template and Layout
• Sources & Further Reading

How to Standardize Importance and Satisfaction Survey Data

Tony Ulwick’s Opportunity Algorithm requires importance and satisfaction inputs to be calculated on an identical 10-point scale so that both metrics carry proportional mathematical weight in the prioritization formula.

A Likert scale is a psychometric survey rating tool that measures respondents’ attitudes or perceptions by asking them to choose from a graded range of numbered options that correspond to specific evaluation levels.

In the Outcome-Driven Innovation framework developed by Tony Ulwick at Strategyn, outcome statements must avoid subjective interpretation. Teams ask customers two distinct questions for every desired outcome statement:

1. When performing this step, how important is it that you [desired outcome]?
2. When using your current solution, how satisfied are you with your ability to [desired outcome]?

For 5-point scales, the standard labels range from 1 ("Not at all important" / "Not at all satisfied") to 5 ("Extremely important" / "Completely satisfied"). If you run customer discovery before building your questionnaire, structure your target statements using a VOC Translation Matrix (With 5-Step Template) to keep technical jargon out of the survey prompts.

Converting Top-Box Percentages to 10-Point Values

Ulwick’s algorithm does not run on arithmetic means of survey responses. A mean score blurs distribution clusters, causing an outcome with split polarizing feedback to look identical to one with moderate indifference. Instead, the framework relies on top-box proportions.

Top-two box scoring is a survey analysis method that combines the percentage of respondents who select the two highest positive ratings on a standard scale into a single summary percentage.

To convert raw survey tallies into the standardized values needed for the Opportunity Algorithm spreadsheet:

1. Isolate the respondents who answered 4 or 5 for Importance.
2. Divide that count by the total valid responses (\(N\)) to produce the top-two box percentage.
3. Multiply that percentage by 10 to establish the standardized Importance score (\(I\)).
4. Repeat the identical calculation for Satisfaction ratings of 4 and 5 to establish the standardized Satisfaction score (\(S\)).

Raw T-2 Box Calculation:
Importance % = (Count of 4s and 5s) / N
Standardized I = Importance % * 10

Satisfaction % = (Count of 4s and 5s) / N
Standardized S = Satisfaction % * 10

For example, if 168 out of 210 surveyed clinical lab directors rate an outcome statement as a 4 or 5 on importance, your top-two box percentage is 80.0%. Multiplied by 10, your standardized Importance score (\(I\)) is 8.0. If 42 of those same 210 directors rate their current tool a 4 or 5 on satisfaction, the satisfaction percentage is 20.0%, producing a standardized Satisfaction score (\(S\)) of 2.0. Both scores now sit on a clean 0.0 to 10.0 scale.

If your organization mandates 10-point Likert scales (where 1 is lowest and 10 is highest), standard practice across research teams at Qualtrics and Strategyn is to measure the top-three box percentage (ratings 8, 9, and 10). Divide the count of responses rated 8 through 10 by \(N\), then multiply by 10 to establish parity with the 5-point conversion model.

Data Hygiene Protocols

Raw survey exports contain noise that skews opportunity scores. Running formulas on unscrubbed tables produces false priority signals that mislead product roadmaps. Apply three programmatic hygiene filters before calculating top-box percentages:

• Speeding filter: Research published by the Pew Research Center indicates that respondents completing digital questionnaires at less than one-third of the median completion time generate low-reliability data. If the median survey duration across 300 respondents is 18 minutes, purge any response submitted in under 6 minutes.
• Straight-lining filter: Identify respondents who select the exact same numeric rating across 15 or more consecutive outcome prompts. Straight-lining signals automated bot submission or respondent fatigue.
• Logical contradiction filter: Flag records where a participant rates an outcome 1 on Importance ("Not at all important") but rates the identical outcome 5 on Satisfaction ("Completely satisfied") across more than 40% of the statement set.

To institutionalize these audits across your analytics workflows, cross-reference your cleanup procedures with an Audit Product Bias: 6-Step Team Checklist (Template) before passing numbers into your core model.

Once your survey rows are scrubbed and converted to standardized 10-point variables, you can pipe these values directly into the automated Opportunity Algorithm sheet architecture below.

Step-by-Step Google Sheets Formula Setup

The Tony Ulwick Opportunity Algorithm calculates market opportunity by adding an outcome’s importance score to the positive difference between its importance and customer satisfaction. The Opportunity Algorithm is an analytical formula developed by Strategyn founder Tony Ulwick in his book What Customers Want that scores customer unmet needs on a 0-to-20 scale by penalizing overserved outcomes and prioritizing high-importance, low-satisfaction jobs. When you run customer interviews or translate customer verbatims through a VOC Translation Matrix (With 5-Step Template), this formula turns subjective feedback into quantitative rankings you can sort instantly.

Single-Row Formula Setup

In Google Sheets, set up your evaluation sheet with Column A as the Desired Outcome Statement, Column B as Importance (rated 1 to 10), and Column C as Satisfaction (rated 1 to 10).

Enter this formula into cell D2:

=B2 + MAX(B2 - C2, 0)

The arithmetic executes Ulwick’s core rule: \(Opportunity = Importance + MAX(Importance – Satisfaction, 0)\). If a customer cohort rates an outcome’s importance at 9.0 and satisfaction at 3.0, the calculation yields \(9.0 + (9.0 – 3.0) = 15.0\).

The MAX(..., 0) segment stops satisfaction surpluses from distorting the score. If your product overdelivers—for instance, an importance score of 4.0 paired with a satisfaction score of 8.0—the subtraction results in -4.0. The MAX function resets that negative variance to 0, locking the final score at the baseline importance of 4.0 rather than dropping it to 0.0.

Dynamic Processing with ARRAYFORMULA

Dragging a single-row formula down hundreds of rows creates formula drift and bloats sheet recalculation times. In a typical study with 50 to 100 outcome statements evaluated across 200 survey participants, manual dragging also breaks whenever an analyst inserts a new outcome row.

Google Sheets handles array math through the Google Sheets ARRAYFORMULA function, but standard MAX functions do not vectorize down columns. Wrapping MAX inside an array formula collapses the entire range into a single universal maximum value instead of evaluating row by row.

Resolve this constraint by replacing MAX with a logical IF test. Paste this formula into cell D2:

=ARRAYFORMULA(IF(ISBLANK(A2:A), "", B2:B + IF((B2:B - C2:C) > 0, B2:B - C2:C, 0)))

The outer IF(ISBLANK(A2:A), "", ...) check scans your outcome statements in Column A. If row 42 is blank, the cell in Column D remains empty, preventing ugly zero-value rows at the bottom of your sheet. The inner IF statement evaluates the difference between Importance and Satisfaction: if the difference is greater than zero, it adds the spread to Column B; otherwise, it adds zero.

[Column A: Outcome]
       |
       v
[Column B: Importance] ---> [Subtract Satisfaction]
       |                            |
       |                            v
       |                    [Difference > 0?]
       |                     /            \
       |                 YES               NO
       |                  |                |
       |                  v                v
       +------------> [+ Spread]       [+ Zero]
                              \         /
                               v       v
                        [Opportunity Score]

This single cell handles your entire sheet down to row 1,000 automatically. When your team aggregates data to prioritize R&D projects with matrices, the output updates instantly without cell maintenance.

Automated Conditional Formatting Rules

Raw opportunity scores require instant visual categorization so stakeholders can identify gaps during sprint reviews. Ulwick defines three clear market conditions based on the final 20-point metric:

• Underserved: Scores of 15.0 and above. Customers care deeply about the outcome but cannot achieve it with current tools. These are prime targets for core product investment.
• Appropriately Served: Scores between 10.0 and 14.9. The current solutions satisfy the job relative to its importance. Maintain these features without over-allocating engineering headcount.
• Overserved: Scores below 10.0 (where satisfaction equals or exceeds importance). Customers receive more performance than they require. These areas represent cost-reduction opportunities.

Apply these rules across range D2:D using standard conditional formatting:

1. Select range D2:D.
2. Open Format > Conditional formatting.
3. Under Format rules, select Greater than or equal to, enter 15, and assign a soft green fill (#D9EAD3).
4. Add a second rule: select Is between, enter 10 and 14.99, and assign a soft yellow fill (#FFF2CC).
5. Add a third rule: select Less than, enter 10, and assign a soft red fill (#F4CCCC).

Sorting your sheet by Column D in descending order immediately clusters your top innovation vectors at the top of your screen.

GOOGLE SHEETS HEADER AND FORMULA SPECIFICATION

ROW 1 (COLUMN HEADERS):
A1: Outcome Statement
B1: Importance (1-10)
C1: Satisfaction (1-10)
D1: Opportunity Score
E1: Opportunity Status

CELL D2 FORMULA (Array Formula for Opportunity Score):
=ARRAYFORMULA(IF(ISBLANK(A2:A), "", ROUND(B2:B + IF((B2:B - C2:C) > 0, B2:B - C2:C, 0), 2)))

CELL E2 FORMULA (Array Formula for Market Status):
=ARRAYFORMULA(IF(ISBLANK(D2:D), "", IF(D2:D >= 15, "Underserved", IF(D2:D >= 10, "Appropriately Served", "Overserved"))))

DATA VALIDATION RULES:
1. Select range B2:C[LAST_ROW]
2. Criteria: Number between 1.00 and 10.00
3. On invalid data: Reject input
4. Input message: "Enter an average customer rating between 1.00 and 10.00."

CONDITIONAL FORMATTING RULES (Apply to Range D2:E[LAST_ROW]):
Rule 1 (Green / Breakthrough Opportunity):
- Format cells if: Custom formula is
- Formula: =$D2>=15
- Formatting style: Text color #0D5320, Fill color #D9EAD3

Rule 2 (Yellow / Maintain Focus):
- Format cells if: Custom formula is
- Formula: =AND($D2>=10, $D2<15)
- Formatting style: Text color #7F6000, Fill color #FFF2CC

Rule 3 (Red / Prune or Repackage):
- Format cells if: Custom formula is
- Formula: =AND(ISNUMBER($D2), $D2<10)
- Formatting style: Text color #783F04, Fill color #FCE5CD

Once these dynamic columns calculate and format your survey data, the next critical step is auditing the underlying outcome statements to verify your inputs before committing capital.

Translating Opportunity Scores into Innovation Strategy

An opportunity score calculated through the Jobs-to-be-Done framework converts raw customer ratings into three clear strategic mandates: allocate capital to scores above 15, strip cost from scores below 10, and segment the market before spending on anything in the middle.

An opportunity score is a numeric value between 0 and 20 calculated from customer ratings of importance and satisfaction to measure the degree of unmet market need for a specific job step. In the formula created by Strategyn founder Tony Ulwick, an outcome score above 15 indicates extreme underservice. These metrics represent points in the workflow where customers struggle severely and existing solutions fail to deliver.

Funneling High Scores (>15) into Core Roadmaps and R&D Budgets

Outcomes scoring 15 or higher demand immediate engineering prioritization and budget allocation. When a desired outcome reaches this threshold, customers actively search for alternatives and willingly reallocate budget to resolve the bottleneck. You should route these items directly into your near-term roadmap or allocate targeted capital using a Manage Innovation Budgets: 70-20-10 (Excel Template) model to de-risk technical execution.

A classic demonstration occurred when medical device manufacturer Cordis Corporation applied this scoring system to interventional cardiology procedures. As documented by Tony Ulwick in the Harvard Business Review, focusing engineering teams exclusively on outcomes scoring above 15 enabled Cordis to develop a new angioplasty balloon design that drove their market share from 1% to over 20% in 18 months. When teams try to fund dozens of low-conviction projects simultaneously, using a structured tool to Prioritize R&D Projects: 3 Matrices (Excel Template) against these hard scores stops pet initiatives from stealing development capacity.

Pruning Features on Overserved Outcomes (<10)

Outcomes with opportunity scores below 10 indicate an overserved market condition where customer satisfaction comfortably exceeds the stated importance. Scores below this line show that your users already achieve their goal efficiently with current tools. Continued feature delivery in this zone creates bloat rather than revenue growth.

When scores sit between 0 and 9.9, your operational strategy shifts from feature addition to aggressive cost reduction. You can safely freeze engineering tickets tied to these outcomes and evaluate active system components for removal. Running a structured exercise with a TRIZ Trimming: Component Pruning Matrix (Excel Template) allows product teams to excise expensive underlying infrastructure without harming customer retention. Harvard Business School professor Clayton Christensen noted in his research on market disruption that incumbents routinely over-engineer products along low-opportunity vectors, opening the door for low-cost entrants to peel away mainstream accounts.

Segmenting Variance to Spot Hidden Sub-Markets

A broad market dataset often shows mediocre opportunity scores simply because two opposing user populations cancel each other out. Enterprise administrators might rate an administrative outcome at maximum importance with low satisfaction, while end-users rate it as completely irrelevant. When you merge their data into one column, the score lands at a deceptive 11.2, masking a prime opening to Spot Disruptive Innovation: Find Your Next Big Opportunity.

To uncover these hidden pockets, run a standard deviation check across the importance and satisfaction ratings for every line item in your spreadsheet. Any outcome where customer responses diverge by more than 2.0 scale points warrants immediate demographic or behavioral segmentation. Splitting the raw data along persona, operational scale, or industry vertical frequently reveals discrete sub-markets where the opportunity score spikes well above 16.0.

Once your team separates the overall survey noise from these high-leverage clusters, you can run the math directly inside the pre-built Google Sheets template detailed below.

Copy-Paste Google Sheets Formula Template and Layout

Tony Ulwick’s Opportunity Algorithm calculates a quantitative priority score for customer needs using the exact mathematical formula of Importance plus the maximum of Importance minus Satisfaction or zero: Importance + MAX(Importance - Satisfaction, 0).

In Ulwick’s Jobs-to-be-Done framework documented by Strategyn, this formula reveals whether a customer outcome is underserved, appropriately served, or overserved on a 1-to-10 scale. The opportunity score is a numerical index between 2.0 and 20.0 that prioritizes product investments by ranking how severely users lack satisfaction relative to the importance they assign to a task. An outcome scoring above 15.0 represents an extreme unmet need, while scores below 10.0 highlight areas where features risk over-engineering.

+-------------------------------------------+
| ULWICK OPPORTUNITY FORMULA PIPELINE       |
+-------------------------------------------+
                      |
                      v
             [ Customer Survey ]
         Importance & Satisfaction
             (Scale: 1.0 to 10.0)
                      |
                      v
             [ Five-Column Sheet ]
           Col A: Outcome Statement
           Col B: Importance (1-10)
           Col C: Satisfaction (1-10)
                      |
                      v
            [ Opportunity Calc ]
       Importance + MAX(Imp - Sat, 0)
                      |
                      v
           [ Opportunity Score ]
           > 15.0 : Extreme Need
          12.0-14.9 : High Priority
          10.0-11.9 : Table Stakes
           < 10.0 : Overserved

Complete Five-Column Spreadsheet Layout

To track these metrics, configure a dedicated worksheet with five columns running from row 1 to row 100. Enter the following headers across row 1:

Populate Column A with clean statements harvested from your VOC Translation Matrix (With 5-Step Template). Columns B and C store survey-derived mean values across your customer base. Columns D and E contain automated formula calculations.

If you are sourcing core outcome statements across your team, Tony Ulwick outlines the structural rules for statement syntax in his book What Customers Want.

Google Sheets Formulas for Single-Row and Array Execution

For row-by-row calculation, click cell D2 and paste this single-row formula:

=IF(OR(ISBLANK(B2), ISBLANK(C2)), "", B2 + MAX(B2 - C2, 0))

In cell E2, paste this nested logic to automatically bucket the outcome into Ulwick’s strategic categories:

=IF(D2="", "", IF(D2>=15, "Underserved (High Priority)", IF(D2>=12, "Underserved (Medium Priority)", IF(D2>=10, "Appropriately Served", "Overserved (Disruption Risk)"))))

When managing large datasets with dozens of customer outcomes, dragging formulas down rows creates sync errors whenever someone inserts a new row. Instead, clear cells D2:E and paste these two array formulas into the header cells D1 and E1 to execute dynamic array parsing across the entire sheet:

Paste into cell D1:

={"Opportunity Score"; ARRAYFORMULA(IF((B2:B="") + (C2:C=""), "", B2:B + IF((B2:B - C2:C) > 0, B2:B - C2:C, 0)))}

Paste into cell E1:

={"Opportunity Landscape"; ARRAYFORMULA(IF(D2:D="", "", IF(D2:D>=15, "Underserved (High Priority)", IF(D2:D>=12, "Underserved (Medium Priority)", IF(D2:D>=10, "Appropriately Served", "Overserved (Disruption Risk)")))))}

In a benchmark study published by the Harvard Business Review on outcome-driven innovation, product teams using this mathematical filter increased their commercial launch success rate from an industry average of 17% up to 86%. This score directly establishes which features warrant engineering sprints and which projects to route into your Prioritize R&D Projects: 3 Matrices (Excel Template).

Data Validation Rules to Lock Input Boundaries

Ulwick's algorithm distorts if a team member enters a satisfaction score of 0 or an importance score above 10. A single 12.0 typed by mistake skews your opportunity rank by 4.0 full points. Apply Google Sheets native data validation across columns B and C to stop bad inputs before they alter your roadmap.

Follow these configuration steps:

1. Select range B2:C100.
2. Open the menu and navigate to Data > Data validation > Add rule.
3. Under Criteria, pick is between from the dropdown.
4. Set the lower boundary field to 1.0 and the upper boundary field to 10.0.
5. Click Advanced options.
6. Select Reject the input under If the data is invalid.
7. Check Show help text for a selected cell and enter: "Input must be a customer survey mean between 1.0 and 10.0."
8. Click Done.

Pair this validation with conditional formatting. Highlight D2:D100, select Format > Conditional formatting, choose Greater than or equal to, set the value to 12.0, and select a soft green fill. This immediately flags breakthrough opportunities for your Seed-Stage Innovation Scorecard (Spreadsheet Template) without requiring manual sorting. Outlier scores below 10.0 indicate features where cost reductions or alternative pricing models can spot disruptive innovation.

Open your team's Google Drive right now, create a new sheet with these five columns, paste the array formulas into cells D1 and E1, and input your top 5 customer outcome statements to identify your product's highest-leverage engineering target before your next backlog grooming session.

Sources & Further Reading

Tony Ulwick's Opportunity Algorithm rests on quantitative research codified in Outcome-Driven Innovation, which evaluates customer metrics using mathematical scoring rather than open-ended brainstorming. The Opportunity Algorithm is a mathematical formula that calculates the commercial promise of an unmet customer need by adding its importance score to the positive difference between importance and current customer satisfaction. In Tony Ulwick's 2005 book What Customers Want, published by McGraw-Hill, analysis across 73 corporate product development projects showed that using this mathematical prioritization yielded an 86% commercial success rate, contrasted against the historical 17% baseline product success rate tracked by the Product Development and Management Association.

The formula applies a direct arithmetic logic: \(\text{Opportunity Score} = \text{Importance} + \max(\text{Importance} - \text{Satisfaction}, 0)\). An opportunity score of 12.0 points or greater identifies an underserved market segment ripe for new development, while scores below 10.0 points signal areas that customers already consider sufficiently addressed. Late Harvard Business School professor Clayton Christensen corroborated this mechanics-focused framing, demonstrating that customer behavior shifts around functional jobs rather than demographic profiles.

To build robust survey instruments that uncover precise customer outcome metrics for your calculation model, consult the original research literature on Jobs-to-be-Done theory.

Featured image by FFD Restorations on Pexels