Charts & Visual Data
Doughnut Progress Ring Formula: Clamped Fill, Remainder Arc & Over-Target Guard
Prevent broken Doughnut Progress Charts when attainment exceeds 100% by clamping the unfilled remainder slice with MAX(0, 1 - Actual/Goal) instead of raw 1 - Actual/Goal.
Why naive =1 - Completion% breaks every Doughnut Gauge above 100%!
A two-slice Doughnut Progress Ring pairs Completed_Share and Unfilled_Remainder so the circle totals 100%. Beginners write Remainder = 1 - (B2 / B3), which works at 78% (0.78 fill + 0.22 grey track = 1.00) — until a rep hits 125% of quota! At 1.25, 1 - 1.25 = -0.25: Excel/spreadsheet chart engines take ABS(-0.25) = +0.25 and render a fake 20% grey "unfinished" gap on top of a 125% overachiever!
Always clamp both slices to [0, 1]: Primary_Ring_Fill = MIN(1, MAX(0, B2 / B3)) and Unfilled_Remainder = MAX(0, 1 - B2 / B3), while tracking Stretch_Surplus = MAX(0, B2 / B3 - 1) for the center KPI callout!
| Attainment Scenario (Goal = 1,000) | Actual (B2) | Clamped Ring Fill | Clamped Remainder Slice |
|---|---|---|---|
| In Progress (78.0%) | 780 | 0.7800 (78% arc) | 0.2200 (22% grey arc) |
| Goal Met (100.0%) | 1,000 | 1.0000 (Full ring) | 0.0000 (Zero gap) |
| Over-Target (125.0%) | 1,250 | 1.0000 (Full ring, +25% badge) | 0.0000 (Prevents -0.25 ABS bug!) |
Clamped Unfilled Remainder Arc Slice [0, 1]
Returns the remaining fraction to 100% and floors cleanly at 0 once B2 >= B3.
=IF(OR(B3 <= 0, B2 < 0), "Target Must Be Positive", ROUND(MAX(0, 1 - B2 / B3), 4))Clamped Primary Completion Arc Slice [0, 1]
Caps the visual ring slice at 1.0000 while raw attainment B2/B3 drives the percentage label.
=IF(OR(B3 <= 0, B2 < 0), 0, ROUND(MIN(1, MAX(0, B2 / B3)), 4))Try the example
Use the original values or change them to test your understanding.