Regional Shift-Share Growth Decomposition Formula

Separate macro tailwinds and industry composition from genuine local competitiveness!

When a metropolitan area adds jobs faster than the national average, did local economic-development policy outperform — or did the city merely inherit a boom in software while underperforming national software peers? Edgar Dunn’s (1960) Classic Shift-Share Analysis partitions Base-Year Sectoral Employment E_i0’s observed change Delta_E_i = E_i1 - E_i0 into three exact additive components using National Aggregate Growth g_N, National Sector-i Growth g_iN, and Regional Sector-i Growth g_iR = (E_i1 - E_i0) / E_i0: (1) National Growth Share NS_i = E_i0 * g_N, (2) Industry Mix (Proportional Shift) IM_i = E_i0 * (g_iN - g_N), and (3) Regional Competitive Shift (Differential Effect) RS_i = E_i0 * (g_iR - g_iN) — summing identically to NS_i + IM_i + RS_i = E_i0 * g_iR = E_i1 - E_i0!

Verify on exact integers across a 2-sector metro economy (National Overall Growth g_N = 0.05 = 5.0% in H2): Sector_1_Tech (Base E_10 = 10,000 in B2, End E_11 = 11,800 in C2 -> g_1R = +18.0%; National Tech Growth g_1N = 0.15 = +15.0% in D2) and Sector_2_Manufacturing (Base E_20 = 20,000 in B3, End E_21 = 20,600 in C3 -> g_2R = +3.0%; National Mfg Growth g_2N = -0.02 = -2.0% in D3): Total Metro Job Gain is +2,400 (+8.0%). National Growth explains (10,000 + 20,000) * 0.05 = +1,500.00 jobs; Industry Mix explains 10,000*(0.15 - 0.05) + 20,000*(-0.02 - 0.05) = +1,000 - 1,400 = -400.00 jobs (heavy legacy manufacturing mix dragged the city down!); and Regional Competitive Shift is 10,000*(0.18 - 0.15) + 20,000*(0.03 - (-0.02)) = +300 + 1,000 = +1,300.00 jobs (proving BOTH local sectors beat their national benchmarks: 1,500 - 400 + 1,300 = 2,400 exact!)!

Aggregate Industry Mix Effect (IM) Without Array Broadcasting Formula

Rewrite SUM(E_i0 * (g_iN - g_N)) cleanly as SUMPRODUCT(B2:B3, D2:D3) - SUM(B2:B3) * H2 so every spreadsheet engine evaluates it natively!

=IF(OR(MIN(B2:B3) <= 0, MIN(C2:C3) < 0), "Invalid Employment Input", ROUND(SUMPRODUCT(B2:B3, D2:D3) - SUM(B2:B3) * H2, 2))

Aggregate Regional Competitive Shift (RS) Formula

Since E_i0 * g_iR = E_i1 - E_i0 identically, Total Competitive Shift equals Observed Regional Change minus Expected National-Sector Change: (SUM(C2:C3) - SUM(B2:B3)) - SUMPRODUCT(B2:B3, D2:D3)!

=IF(OR(MIN(B2:B3) <= 0, MIN(C2:C3) < 0), "Invalid Employment Input", ROUND((SUM(C2:C3) - SUM(B2:B3)) - SUMPRODUCT(B2:B3, D2:D3), 2))

Further reading

• Arte Sheets Lesson — Oaxaca-Blinder Twofold Wage-Gap Decomposition Formula
• Arte Sheets Lesson — Verdoorn-Kaldor Manufacturing Productivity Growth Formula