Math & Statistical
Thermometer Goal Chart Formulas: Capped Fill Percentage, Remaining Gap & Stretch Surplus
Prepare clean 0–100% fundraising and sales thermometer column charts using MIN(1, Raised / Goal) * 100, remaining gap MAX(0, Goal - Raised), and overachievement surplus MAX(0, Raised - Goal).
Why raw Raised / Goal breaks fixed-scale 100% thermometer charts when campaigns exceed target!
A fundraising or quarterly quota thermometer column chart relies on a locked 0%–100% vertical axis—so if actual bookings B2 = $275,000 surpass goal B3 = $250,000, an uncapped ratio B2 / B3 = 110% shoots past the top of the thermometer bulb or rescales the axis awkwardly! Capping the fill series with =ROUND(MIN(1, B2 / B3) * 100, 2) pins the mercury bar cleanly at 100.00%!
Pair the capped mercury percentage with two zero-floored companion cells—Remaining_to_Goal = MAX(0, B3 - B2) ($66,000 at $184,000 raised; $0 once reached) and Stretch_Surplus = MAX(0, B2 - B3) ($25,000 at $275,000 raised)—and plot the summary range as a column or bar chart (including via Arte Sheets’ AI assistant create_chart command on an explicit cell range)!
| Campaign Progress vs. $250,000 Goal | Capped Thermometer Fill % | Remaining Gap MAX(0, Goal-Actual) | Stretch Surplus MAX(0, Actual-Goal) |
|---|---|---|---|
| Actual = $184,000 (In Progress) | 73.60% | $66,000 Remaining | $0 |
| Actual = $275,000 (Goal Exceeded!) | 100.00% (Capped cleanly) | $0 | $25,000 Overachievement |
| Invalid Goal <= 0 | Division Guard | "Goal Must Exceed Zero" | Positive Target Check |
Capped 0–100% Thermometer Mercury Fill Formula = ROUND(MIN(1, MAX(0, Actual / Goal)) * 100, 2)
Clamps thermometer fill percentage strictly inside [0%, 100%] when Goal B3 > 0.
=IF(OR(B2 < 0, B3 <= 0), "Goal Must Exceed Zero", ROUND(MIN(1, B2 / B3) * 100, 2))Remaining Dollars Needed to Reach Goal = MAX(0, Goal - Actual)
Returns the exact remaining gap before reaching target and floors cleanly at 0 once achieved.
=IF(OR(B2 < 0, B3 <= 0), "Goal Must Exceed Zero", MAX(0, B3 - B2))Try the example
Use the original values or change them to test your understanding.