Charts & Visual Data
Arrow & Dumbbell Chart Formula: Floating Base Anchor, Gain/Loss Span & Midpoint
Stage directional Arrow or Dumbbell change charts by pairing an invisible floating base MIN(Prior, Current) with separate Positive-Gain and Negative-Drop span columns and a centered delta-label midpoint.
Replace cluttered paired bars with clean directional change vectors!
Clustered Prior-vs-Current bar charts force viewers to mentally subtract 10 pairs of bars! An Arrow Chart (or Cleveland Dumbbell Dot Plot) highlights the exact magnitude and direction of change between Prior (B2) and Current (B3): an invisible stacked base sits at Anchor = MIN(B2, B3), a green Gain_Span = MAX(0, B3 - B2) extends rightward for improvements, and a coral Loss_Span = MAX(0, B2 - B3) marks declines!
To position a "+23 pts" callout label cleanly over the connecting connector shaft, plot a scatter/line series at Midpoint = (B2 + B3) / 2.
| Account NPS Trajectory | Prior (B2) | Current (B3) | Anchor Base / Active Span / Midpoint |
|---|---|---|---|
| Enterprise Cohort (Gain) | 62 | 85 | Base = 62 | Gain = +23 | Loss = 0 | Mid = 73.5 |
| Legacy Retail (Decline) | 90 | 74 | Base = 74 | Gain = 0 | Loss = 16 | Mid = 82.0 |
Invisible Floating Anchor Base MIN(Prior, Current)
Positions the left edge of the floating dumbbell/arrow connector at the smaller of Prior (B2) and Current (B3).
=IF(OR(B2 < 0, B3 < 0), "Requires Non-Negative Endpoints", ROUND(MIN(B2, B3), 2))Positive Improvement Span & Connector Label Midpoint
Computes positive gain span MAX(0, B3 - B2) (or midpoint (B2 + B3) / 2 for centered delta labels).
=IF(OR(B2 < 0, B3 < 0), 0, ROUND(MAX(0, B3 - B2), 2))Try the example
Use the original values or change them to test your understanding.