Charts & Visual Data
Combination (Combo) Chart & Secondary Axis Scaling Formula: Zero-Baseline Alignment
Combine disparate units like Dollar Revenue Columns and Gross Margin % Lines on a Combo Chart without baseline distortion by computing exact Secondary Y-Axis bounds that lock both primary and secondary zero lines.
Why unaligned secondary axes create misleading visual crossovers — and how proportionality locks the zero line!
When you plot Monthly Revenue ($500,000) and Operating Margin (14.5% = 0.145) on a single Y-axis, the 0.145 margin line collapses flat onto zero! Plotting Columns on Primary Axis Y_1 and a Line on Secondary Axis Y_2 (a Combination / Combo Chart) restores visibility, EXCEPT when negative numbers appear (e.g., Q1 net loss -$100,000 vs -5% margin): if Primary Y_1 spans [-$200k, +$800k] (zero sits 20% from the bottom) while Secondary Y_2 auto-scales to [-20%, +40%] (zero sits 33.3% from the bottom), a positive +$10k bar can visually sit BELOW a negative -2% line!
To force both axes to share a single horizontal zero line, lock the negative-to-positive span ratio across both axes: given Primary bounds [Y1_min, Y1_max] (with Y1_min < 0 < Y1_max) and desired Secondary ceiling Y2_max > 0, set Secondary Floor Y2_min = Y2_max * (Y1_min / Y1_max)! And in single-axis chart engines, you can project any secondary series y_2 directly onto primary coordinates via Rescaled_Y = y_2 * (Y1_max / Y2_max) while displaying original % data labels!
| Axis Alignment Parameter | Primary Dollar Axis (Y_1) | Zero-Locked Secondary % Axis (Y_2) |
|---|---|---|
| Axis Maximum Ceiling | +$800,000 | +40.0% (0.40) |
| Axis Minimum Floor (Ratio = -0.25) | -$200,000 | 40.0% * (-200k / 800k) = -10.0% (-0.10) |
| Zero-Line Fractional Height (0 - Min)/(Max - Min) | 200k / 1,000k = 20.0% from bottom | 10% / 50% = 20.0% from bottom (Exact Lock!) |
Zero-Aligned Secondary Axis Minimum Bound Y2_min
Computes Y2_max * (Y1_min / Y1_max) across Primary Min B2 (<= 0), Primary Max B3 (> 0), and Secondary Max B4 (> 0) so both Y-axes place zero at the exact same pixel height.
=IF(OR(B2 > 0, B3 <= 0, B4 <= 0), "Requires Y1_min <= 0, Y1_max > 0, and Y2_max > 0", ROUND(B4 * (B2 / B3), 4))Primary-Coordinate Rescaled Value for Single-Axis Combo Overlay
Projects a secondary percentage observation B5 (e.g., 0.185 = 18.5% margin) onto primary dollar height B5 * (B3 / B4).
=IF(OR(B3 <= 0, B4 <= 0), "Requires Y1_max > 0 and Y2_max > 0", ROUND(B5 * (B3 / B4), 2))Try the example
Use the original values or change them to test your understanding.