How to Use VSTACK and LAMBDA to Author Dynamic Hannah-Kay Concentration Index Models in Microsoft Excel

In anti-trust law, industrial economics, and regulatory compliance, assessing market power requires metrics that evaluate market share distributions beyond simple four-firm concentration ratios (CR4) and the Herfindahl-Hirschman Index (HHI). The Hannah-Kay (HK) Concentration Index introduces a customisable elasticity parameter α (α > 0, α ≠ 1) that weights larger versus smaller market participants dynamically. By combining modern dynamic array functions—specifically VSTACK, LAMBDA, LET, and SCAN—Excel users can build fully automated, parametric concentration models without legacy VBA macros.

Mathematical Formulation of the Hannah-Kay Index

For an industry consisting of N competitors with market shares s_i (where ∑ s_i = 1), the Hannah-Kay Index with elasticity parameter α is expressed as:

HK(α) = (∑ (s_i)^α)^(1 / (1 - α))

When α approaches 2, the index provides an equivalent numbers metric closely related to the inverse of the Herfindahl-Hirschman Index. Lower values of α emphasise the role of smaller fringe competitors, whereas higher values of α heavily penalise dominant market leaders. This elasticity enables economists to stress-test horizontal mergers under varying regulatory assumptions.

Authoring the Core LAMBDA Formula

Open Microsoft Excel (Office 365 or Excel 2024) and open Formulas > Name Manager. Create a new defined name titled HANNAH_KAY with the following dynamic LAMBDA definition:

=LAMBDA(shares, alpha,
    LET(
        clean_shares, FILTER(shares, (shares > 0) * ISNUMBER(shares)),
        total_share, SUM(clean_shares),
        norm_shares, clean_shares / total_share,
        power_sum, SUM(norm_shares ^ alpha),
        exponent, 1 / (1 - alpha),
        IF(alpha = 1,
           EXP(-SUM(norm_shares * LN(norm_shares))),
           power_sum ^ exponent
        )
    )
)

Notice that for α = 1, the formula seamlessly computes the exponential of the Shannon entropy index, ensuring mathematical continuity across all parameter ranges.

Constructing the Dynamic Sensitivity Table with VSTACK

To evaluate market concentration across multiple elasticity values (α = 0.5, 1.5, 2.0, 2.5, 3.0), configure your spreadsheet with firm revenues in column range A2:A11. In cell C2, input the following self-contained reporting formula:

=LET(
    rev_range, A2:A11,
    alpha_list, {0.5; 1.5; 2; 2.5; 3},
    header, HSTACK("Alpha Parameter (α)", "Hannah-Kay Index (Equivalent Firms)", "Market Structure Classification"),
    calc_row, LAMBDA(a,
        LET(
            hk_val, HANNAH_KAY(rev_range, a),
            class_desc, IF(hk_val < 2.5, "Highly Concentrated / Oligopoly",
                        IF(hk_val < 5.0, "Moderately Concentrated", "Competitive / Fragmented")),
            HSTACK(a, ROUND(hk_val, 3), class_desc)
        )
    ),
    results, DROP(REDUCE("", alpha_list, LAMBDA(acc, a, VSTACK(acc, calc_row(a)))), 1),
    VSTACK(header, results)
)

Interpreting Output and Economic Implications

1. Equivalent Firms Interpretation: An HK score of 3.20 indicates that the industry exhibits the same degree of concentration as a hypothetical market divided equally among 3.2 identical firms.
2. Parameter Sensitivity: If increasing α from 1.5 to 3.0 causes a sharp decline in the equivalent firm count, market power is heavily concentrated within the top one or two firms rather than distributed evenly across the mid-tier.
3. Zero-Macro Portability: Because the calculations rely entirely on dynamic array formulas, the model runs smoothly in Excel for Web and mobile environments without security warnings or macro permission blocks.