DYSARTISANIDEAS, FORGED INTO FORM.Search the episode library ↗

THE INNOVATION FORGE / From the Foundry

Excel Scoring and Validation: make the rubric easier to inspect

Separate raw behavior counts, capped points and validation in Excel, then check missing values, statistical relationships and performance on new records.

A lantern-lit drafting table with a brass compass, diagrams and a closed leather notebook.
Keep the calculation open to inspection.

THE IDEA, IN A MINUTE

Keep the evidence behind every score visible.

David reads Chapter 3 of The Innovation Forge, moving from a clean CRM export to a scoring matrix and validation tab. This From the Foundry chapter explains how Excel can make a rubric easier to revise while keeping errors, incomplete observations and overfitting visible.

01 / THE RECORDING

Watch the episode.
Or find your moment.

Preserve the observations behind the score

Start the workbook with behavior counts: attended events, selected email interactions and portal visits. Keep those observations separate from the points assigned to them. Changing a rubric should change the score without requiring you to reconstruct what happened.

The export needs a consistent row definition, usable numeric columns and a deliberate approach to duplicates and missing values. A repeated record can distort the analysis; a blank can mean something different from zero. Document what each column measures before experimenting with its weight. The chapter begins with the export already available and leaves the Configurable Joins construction to another chapter.

In the recording · 03:49 ↗

Give the rubric its own control tab

A separate reference tab holds the choices you expect to revise: whether to include a behavior, its points per occurrence and its maximum contribution. Keep subscore categories visible too. A calculation tab can then apply those choices to historical records, while the raw-data tab remains available for inspection.

Capping a contribution prevents one frequently repeated behavior from dominating the total. If a fictional rubric gives four points per visit with a ten-point cap, three visits contribute ten points. That arithmetic expresses a design choice, not evidence that ten is the right weight. Compare alternatives deliberately and retain the underlying counts.

In the recording · 05:43 ↗

Make errors noticeable while building

The chapter's error-handling example returns zero when a calculation fails, but David recommends replacing that fallback while building so errors can be found. That distinction matters: a formula failure should not quietly become a measured absence of engagement.

Keep an error indicator alongside the calculated value and resolve it before interpreting the score. When totaling the included behaviors and bonuses, check which columns qualify and whether rounding belongs at the end. A workbook that always displays a number can still be hiding broken references, unexpected text or an incomplete export.

In the recording · 07:47 ↗

Choose a comparison that answers the question

The validation tab compares the new score with an existing measure, such as a staff evaluation or an observed outcome. Inspect both individual behaviors and the total. Grouped averages can reveal where a relationship changes, but small groups can look more convincing than their evidence supports.

Define the observation window before comparing outcomes. A score meant to describe earlier engagement should not contain activity recorded after the outcome it is being checked against. Keep the comparison measure out of its own predictors. Staff ratings also need scrutiny: they reflect a particular evaluation process rather than an unquestionable ground truth.

In the recording · 09:41 ↗

Keep strength, direction and uncertainty together

Pearson correlation summarizes a linear relationship. Its sign gives the direction, while its magnitude describes strength. A coefficient of negative 0.65 and one of positive 0.65 have equal magnitude but opposite directions. Retain the sign when explaining what the score means.

Use the same eligible numeric pairs for the correlation and its sample count. A legitimate zero belongs in the analysis; an unknown value needs explicit handling. The chapter uses a nonnegative-value count as a proxy, which deserves inspection before reuse, especially if meaningful negative values or blanks are possible.

A p-value does not establish predictive accuracy or causation. Read it alongside the relationship's size, sample and purpose. Categorical variables require an appropriate analysis rather than automatic reuse of the same formula. Conditional formatting can aid scanning, but keep text labels and values visible so interpretation does not depend on distinguishing red from green.

In the recording · 12:02 ↗

Test what survives beyond the workbook

Trying hundreds of weights and behaviors creates opportunities to find accidental patterns. Record the comparisons you tried and reserve data for a check that did not guide those choices. A beautifully fitted historical score can still perform poorly on new records.

The chapter favors a useful pilot over endless pre-production refinement. Check new cohorts, review changes in events and forms, and investigate unexpected results before revising the rubric. Historical comparisons become harder to interpret when the institution changes how it invites, records or supports engagement.

Finally, remember how partial the record is. Unrecorded activity and unequal opportunities to participate limit what a low count can tell you. Use the workbook to examine measurement and improve outreach processes, while preserving human context around the people those rows represent.

In the recording · 20:50 ↗

03 / A SMALL EXPERIMENT

Check the rubric before trusting the total

Use these three fictional workbook cases to test a cap, paired observations and a time boundary. Write the expected result before inspecting the calculation.

A frequent behavior reaches its cap

A fictional record has three verified visits worth four points each, capped at ten points. It also has five portal visits worth one point each, capped at three points. Both behaviors are included and there are no bonuses. What total should the workbook produce?

Compare your reasoning

The visits contribute min(3 × 4, 10) = 10. Portal visits contribute min(5 × 1, 3) = 3. The total is 13. Keep the raw counts of three and five visible so changing the weights does not change the underlying observations.

Revisit this moment · 07:55 ↗
Zero is a value; missing is not

Four fictional rows contain score and comparison pairs: A = (8, 1), B = (missing, 0), C = (0, 0), D = (4, missing). Using complete numeric pairs, which rows belong in the comparison and what is the pair count?

Compare your reasoning

A and C belong, giving two complete pairs. C's zeros are observed values. B and D are incomplete and must not become zeros merely to make the calculation work. Two pairs are far too few for a useful validation conclusion; the exercise checks eligibility and counting, not model quality.

Revisit this moment · 13:43 ↗
A later event changes the retrospective total

A fictional outreach rubric counts events observed by April 1 at two points each, capped at twelve. A record has four events by April 1 and six more after an outcome on June 1. What score belongs in the April 1 validation snapshot?

Compare your reasoning

The snapshot score is min(4 × 2, 12) = 8. Counting all ten events would produce a capped score of 12, but those six later events were unavailable on April 1. Preserve the observation cutoff rather than letting future activity leak into the historical comparison.

Revisit this moment · 20:50 ↗
Build a test card to take with you

Notes stay in this tab. Reloading clears them.

04 / INSIDE THE STUDIO

The seams are part
of the story.

Behind the episode

The cold open's superhero fishing trip returns during the warning about testing hundreds of relationships. The repeated joke gives the statistical caution a memorable cue: a spreadsheet that makes comparison easy also makes it easy to chase an accidental pattern. David's early narrator's note asks listeners to keep the reference images nearby, a practical choice for a chapter with long Excel expressions spoken aloud.

Revisit the studio moment · 00:00 ↗

Art from the workshop

A lantern-lit table keeps plans, instruments and a notebook close at hand.

Open the artwork ↗

KEEP THE IDEA MOVING

Where would you take it next?

CONTINUE EXPLORING