How to Do TURF Analysis in Excel (and Why You Shouldn't)

Can You Run TURF in Excel?

Yes, for small item sets. Excel can calculate TURF reach for any combination of items if you set up the data correctly. For learning how TURF works or running a quick analysis on 8-12 items, Excel does the job. For real studies with 15+ items, Excel becomes impractical and error-prone.

This guide shows you both: how to build TURF in Excel so you understand the mechanics, and why you should use a proper tool for production research.

Excel TURF: Step by Step

Step 1: Set Up the Data Matrix

Create a table where each row is a respondent and each column is an item. Enter 1 if the respondent accepts that item, 0 if not.

Step 2: Calculate Individual Item Reach

For each item, reach = SUM of the column / total respondents.

=SUM(B2:B201)/COUNTA(A2:A201)

This gives you each item's individual acceptance rate. In a real study with 200 respondents, you'd see numbers like Chocolate: 62%, Vanilla: 58%, Strawberry: 51%, etc.

Step 3: Calculate Combination Reach

For a specific combination (say, Chocolate + Mint + Mango), create a helper column that checks whether each respondent accepts at least one of the three:

=IF(OR(B2=1, E2=1, F2=1), 1, 0)

Then sum the helper column and divide by total respondents. That's the unduplicated reach for that combination.

Step 4: Test All Combinations

Here's where Excel gets painful. For 6 items choosing 3, there are 20 combinations. You'd create 20 helper columns (or use a lookup table), calculate reach for each, and pick the highest.

For 15 items choosing 5, there are 3,003 combinations. For 20 items choosing 8, there are 125,970. You can't build that many helper columns.

Step 5: Find the Optimal Combination

For small sets (under 15 items), you can enumerate all combinations using a systematic approach: list all possible combinations in rows, calculate reach for each (using SUMPRODUCT and OR logic), and sort by reach.

For the chocolate example with 6 items choosing 3:

Sort by reach, and the top row is your TURF-optimal combination.

Why Excel Breaks Down

Scale

The number of combinations grows exponentially. At 20 items choosing 6, you're evaluating 38,760 combinations. At 30 items choosing 8, it's over 5 million. Excel can't handle this without VBA macros, and even then, computation time becomes impractical.

Error Risk

Manually building 3,003 combination formulas is error-prone. One misreferenced cell skews an entire combination's reach calculation, and you'd never notice among thousands of rows. Dedicated tools eliminate this by computing combinations programmatically.

No Incremental Analysis

TURF's most useful output is the incremental reach curve (what does each additional item contribute?). Building this in Excel requires running the analysis at portfolio size 1, then 2, then 3, and so on, each time re-evaluating all combinations. It's tedious to build and slow to modify.

No Segment Analysis

If you want TURF results for different customer segments, you'd need to duplicate the entire analysis for each segment. With a proper tool, segment-level TURF is a single configuration change.

No Constraints

Real studies often need "must include" and "must exclude" constraints. In Excel, you'd need to manually filter which combinations to evaluate. Dedicated tools handle constraints natively.

When Excel Is Fine

Learning

Building TURF in Excel once is an excellent way to understand the mechanics. Create a small dataset (6 items, 20 respondents), calculate reach for a few combinations manually, and verify the results make intuitive sense. This builds the understanding that makes you a better user of automated tools.

Tiny Studies

If you have 8 items choosing 3-4 and don't need segment analysis, Excel works. There are 56-70 combinations, which is manageable. The setup takes 30 minutes and produces valid results.

Quick Validation

If you've run TURF in a dedicated tool and want to verify a specific combination's reach, Excel is a fast way to cross-check. Pull the data matrix, calculate reach for that combination, and confirm it matches.

Better Alternatives

Survey Platforms with Built-In TURF

Quali-Fi, Displayr, and several other platforms include TURF as a native analysis module. You collect the data through the platform, click to run TURF, and get reach curves, optimal combinations, incremental tables, and segment breakdowns in seconds. No formula building required.

R

The tuRf package and custom scripts handle TURF on datasets of any size. The computation runs in seconds even for 40+ items. R also handles constraints, segment analysis, and sensitivity testing natively.

Python

Using itertools.combinations and numpy, TURF is about 15 lines of Python code for the basic version. Libraries like pandas handle data preparation, and the analysis scales to large item sets.

Sawtooth Software

Sawtooth's Discover platform includes TURF optimization as a built-in module, designed for MaxDiff data. It handles the MaxDiff-to-TURF pipeline automatically: collect MaxDiff data, set an acceptance threshold, and run TURF on the derived acceptance matrix.

A Template for Small-Scale Excel TURF

If you want to build a working Excel TURF for learning purposes, here's the structure:

Sheet 1: Data Matrix

• Column A: Respondent IDs
• Columns B-N: Items (1/0 acceptance)

Sheet 2: Combination Generator

• Each row lists a combination (e.g., Items B, D, F)
• A formula calculates reach for that combination using SUMPRODUCT

Sheet 3: Results

• Sort combinations by reach
• Top row = optimal combination
• Chart: portfolio size vs. reach

Key formula for reach of a 3-item combination (items in columns B, E, G):

=SUMPRODUCT(SIGN(Data!B2:B201 + Data!E2:E201 + Data!G2:G201)) / COUNTA(Data!A2:A201)

The SIGN function converts any sum >= 1 to 1, effectively creating an OR condition across the selected items.

Frequently Asked Questions

Can I use Excel VBA to automate the combination evaluation?

Yes. A VBA macro can enumerate all combinations, calculate reach for each, and output the optimal set. This extends Excel's practical limit from ~15 items to ~25 items. Beyond that, even VBA becomes slow. For a quick VBA approach, use nested loops to generate combinations and SUMPRODUCT in each iteration to calculate reach.

Are there free TURF calculators online?

A few exist, but most are limited. Sawtooth offers a TURF calculator for specific use with their software. Some online tools handle basic TURF on small datasets. For serious research, a platform subscription or R/Python is more reliable.

How accurate is Excel TURF compared to dedicated tools?

If your formulas are correct, the results are identical. TURF is a deterministic algorithm; there's no approximation or model fitting. The risk with Excel isn't accuracy but completeness (missing combinations due to formula errors) and scalability.

Related Guides

• TURF Analysis: Complete Guide -- Full methodology overview
• How to Run TURF Analysis -- Step-by-step in proper tools
• How to Interpret TURF Results -- Reading output from any tool
• TURF Analysis Examples -- Real-world case studies
• MaxDiff Analysis -- Generating better input data for TURF
• Sample Size Calculator -- Plan your data collection

Skip the spreadsheet -- try Quali-Fi free for 14 days.