Math & Statistical
100% Stacked Column & Bar Mix Matrix Formula: Mixed-Reference Period Shares (B$2:B$4)
Normalize multi-quarter revenue or headcount tables into a 100% composition matrix using column-row-locked mixed references ROUND(B2 / SUM(B$2:B$4) * 100, 2) and measure mix shift percentage-point changes.
How mixed reference B$2:B$4 normalizes every quarter column to 100.00% in a single drag-across formula!
When comparing SaaS tier mix (Enterprise, Mid-Market, SMB) across Q1 and Q2 where total bookings grew from $400k to $500k, raw stacked columns blend volume growth with mix shift! Building an explicit 100%-normalized composition block using mixed row-locked denominator =ROUND(B2 / SUM(B$2:B$4) * 100, 2) lets one formula copy cleanly both DOWN rows and ACROSS quarter columns!
Revealing exact percentage shares shows immediately that even though Enterprise grew in dollars ($180k -> $210k), its revenue mix share actually slipped -3.00 percentage points from 45.00% in Q1 to 42.00% in Q2!
| Customer Segment | Q1 Bookings ($400k Total) | Q1 Mix Share % | Q2 Bookings ($500k Total) | Q2 Mix Share & Shift |
|---|---|---|---|---|
| Enterprise | $180k | 45.00% | $210k | 42.00% (-3.00 pp shift) |
| Mid-Market | $120k | 30.00% | $150k | 30.00% (0.00 pp flat) |
| SMB Self-Serve | $100k | 25.00% | $140k | 28.00% (+3.00 pp gain) |
Q1 Segment Percentage Share with Mixed Row-Locked Denominator = ROUND(B2 / SUM(B$2:B$4) * 100, 2)
Divides segment cell B2 by Q1 column sum SUM(B$2:B$4) with zero-column protection.
=IF(SUM(B$2:B$4) <= 0, "Zero Period Total", ROUND(B2 / SUM(B$2:B$4) * 100, 2))Period-Over-Period Mix Shift in Percentage Points (Q2 Share % minus Q1 Share %)
Computes exact percentage-point composition shift between Q2 (C2:C4) and Q1 (B2:B4).
=IF(OR(SUM(B$2:B$4) <= 0, SUM(C$2:C$4) <= 0), "Zero Period Total", ROUND((C2 / SUM(C$2:C$4) - B2 / SUM(B$2:B$4)) * 100, 2))Try the example
Use the original values or change them to test your understanding.