Charts & Data Visualization
Gauge (Speedometer) Chart Formula: 180-Degree Doughnut Dial & Pointer Needle XY
Build a semicircular KPI Gauge / Speedometer Chart by pairing a 360-degree Doughnut dial (Red + Amber + Green = 180 degrees plus a transparent 180-degree bottom half) with a clamped pointer angle theta = 180 * (1 - p) and needle tip (COS, SIN).
Combine a 270°-rotated Doughnut semicircle with a trigonometric XY Scatter needle!
Because spreadsheets do not include a native speedometer dial primitive, dashboard designers construct a 180° semicircular Gauge Chart from two synchronized layers: first, a Doughnut series rotated to 270° whose top visible half sums to 180 units (for example, Red Critical = 60, Amber Warning = 60, Green Target = 60) and whose bottom hidden slice equals the sum of the top bands (180 units, formatted with no fill)!
Second, clamping KPI completion ratio p = MIN(1, MAX(0, Actual / Max_Scale)) maps 0% attainment to left-horizontal 180° and 100% attainment to right-horizontal 0° via Pointer Angle theta_deg = 180 * (1 - p), placing the Scatter needle tip at exact unit-circle coordinates X_tip = COS(RADIANS(theta_deg)) and Y_tip = SIN(RADIANS(theta_deg)) anchored at hub (0, 0)!
| KPI Attainment Ratio p = Actual / Max | Dial Sweep Angle from Left (180 * p) | Math Polar Angle theta_deg = 180 * (1 - p) | Needle Tip Coordinates (X_tip, Y_tip) |
|---|---|---|---|
| 0% Attainment (p = 0.00) | 0° sweep | 180.0° | (-1.0000, 0.0000) — Far Left |
| 50% Mid-Dial (p = 0.50) | 90° sweep | 90.0° | (0.0000, 1.0000) — Vertical Top |
| 75% Upper Green (p = 0.75) | 135° sweep | 45.0° | (0.7071, 0.7071) — Upper Right |
| 100%+ Capped (p >= 1.00) | 180° full sweep | 0.0° | (1.0000, 0.0000) — Far Right |
Clamped Speedometer Pointer Polar Angle theta_deg (180° Left to 0° Right)
Clamps Actual B2 within [0, Max_Scale B3] so over-target or negative values never spin the needle into the hidden bottom half of the doughnut.
=IF(B3 <= 0, "Requires Positive Gauge Maximum", ROUND(180 * (1 - MIN(1, MAX(0, B2 / B3))), 2))Needle Tip Horizontal Coordinate X_tip = COS(RADIANS(theta_deg))
Evaluates unit-radius needle tip X coordinate in [-1, +1] from clamped pointer angle.
=IF(B3 <= 0, "Requires Positive Gauge Maximum", ROUND(COS(RADIANS(180 * (1 - MIN(1, MAX(0, B2 / B3))))), 4))Try the example
Use the original values or change them to test your understanding.