The task and original case
Create a rate report that distinguishes zero observed successes from an undefined rate. A group with no attempts supplies no denominator. Preserving that fact is a stronger analyst work sample than forcing every cell to display a percentage.
| Case element | Value or requirement |
|---|---|
| Inputs | A 10 successes/100attempts; B 0/0; C 1/10. |
| Build | A rate table that preserves undefined values and states the denominator. |
This is an original invented exercise. It is independent practice, with no employer assessment or open-vacancy claim. Download the inputs, attempt your own result, then open the separate answer key.
Data and worked inputs
Build a result another person can follow
Use one row per group, with separate integer columns for successes and attempts. Check that both counts are nonnegative and successes do not exceed attempts. Calculate a group rate only when attempts are greater than zero; retain an undefined marker and explanatory status otherwise.
For the combined observed rate, sum successes and attempts across the declared groups before dividing. pandas provides missing-value handling; a missing rate should remain distinguishable from a measured zero. If your presentation exports a blank cell, include a status column so that a reader cannot confuse undefined, unavailable and forgotten calculations.
Sources[1]
Check the worked result
A has 10 successes from 100 attempts, giving 10%. B has 0 from 0, so its rate is undefined. C has 1 from 10, also giving 10%. Combined counts are 11 successes from 110 attempts, and the observed rate is 10%.
A simple average that fills B with zero produces about 6.67%, an answer driven by the fill policy rather than the observed attempts. Even when two valid group rates match, retain the denominators so the calculation remains reusable when they diverge.
Make the failure visible
Try B with one success and zero attempts. That is inconsistent, not an unusually high rate. Try a missing attempts field too; it is unknown rather than zero. A spreadsheet formula that replaces every division error with zero can hide both conditions. Route invalid counts to an exception list and require a reason before using a replacement.
Revise it and ask someone to reproduce it
For the second revision, change C to five successes from ten attempts. Combined performance becomes 15 ÷ 110, about 13.64%; averaging A and C's percentages gives 30%, which answers an equal-group question instead. Ask a peer to name the combined denominator and explain B's undefined status without reading your formula.
- Success and attempt counts accompany every defined rate.
- Undefined, missing and invalid states are distinguishable.
- The combined numerator and denominator are summed before division.
Describe the work accurately
After completing your own version, you could adapt this project bullet: “Built an independent rate report preserving zero denominators and reconciling combined rates from underlying counts.”
Use Projects or Independent practice as the heading. Keep the input files, result and revision together. Describe only your own completed work; the invented numbers are model outputs, not employer results or measured business impact.