How to Use VSTACK and LAMBDA to Author Dynamic Coefficient of Variation Inequality Models in Microsoft Excel

While the Gini coefficient and percentile ratios provide intuitive indicators of economic disparity, econometricians and financial analysts rely heavily on the Coefficient of Variation (CV) and the Squared Coefficient of Variation (SCV) for statistical inequality analysis. The SCV is an exact linear transformation of the Generalized Entropy index at parameter (alpha = 2) ((GE(2) = rac{1}{2} CV^2)). Unlike percentile ratios that disregard the middle distribution, the CV satisfies key axiomatic properties including scale independence, population replication, and the Pigou-Dalton principle of transfers. In Microsoft Excel, dynamic array formulas utilizing LAMBDA, LET, STDEV.P, and VSTACK enable automated calculation of these inequality metrics.

Mathematical Foundations of the Coefficient of Variation

The Coefficient of Variation (CV) measures the relative dispersion of an income or compensation distribution by dividing the standard deviation by the mean:

[ CV = rac{sigma}{mu} = rac{sqrt{rac{1}{N} sum_{i=1}^N (y_i – mu)^2}}{mu} ]

The Squared Coefficient of Variation (SCV) and its relationship to Generalized Entropy (GE(2)) are defined as:

[ SCV = CV^2 = rac{sigma^2}{mu^2} ] [ GE(2) = rac{1}{2} left[ rac{1}{N} sum_{i=1}^N left( rac{y_i}{mu} ight)^2 – 1 ight] = rac{1}{2} CV^2 ]

Key properties of the Coefficient of Variation in economic analysis:

• Scale Independence: Multiplying all incomes by a constant factor leaves the (CV) completely unchanged, satisfying relative inequality invariance.
• Equal Sensitivity Across Distribution: Unlike the Gini coefficient (which is heavily sensitive to transfers around the median) or Theil’s T index (sensitive to top-income transfers), the (CV^2) metric attaches equal weight to income transfers at all levels of the distribution.

Authoring the Dynamic CV Model in Microsoft Excel

Place your raw compensation or household income sample in column range B2:B50. In cell D2, enter this self-contained dynamic array formula:

=LET(
    incomes, B2:B50,
    clean_inc, FILTER(incomes, ISNUMBER(incomes) * (incomes > 0)),
    n_count, ROWS(clean_inc),
    
    /* Core Moments */
    mean_val, AVERAGE(clean_inc),
    std_pop, STDEV.P(clean_inc),
    var_pop, VAR.P(clean_inc),
    
    /* Inequality Indices */
    cv_score, std_pop / mean_val,
    scv_score, cv_score^2,
    ge2_score, 0.5 * scv_score,
    
    /* Gini Benchmark Comparison */
    sorted_inc, SORT(clean_inc, 1, 1),
    ranks, SEQUENCE(n_count),
    gini_val, (2 / (n_count * mean_val)) * SUM(ranks * sorted_inc) - (n_count + 1) / n_count,
    
    /* Assemble Spilled Dashboard */
    headers, HSTACK("Statistical Metric", "Calculated Value", "Econometric Interpretation"),
    r1, HSTACK("Sample Size (N)", TEXT(n_count, "#,##0"), "Total active household/employee observations"),
    r2, HSTACK("Arithmetic Mean (mu)", TEXT(mean_val, "£#,##0.00"), "Baseline distribution central tendency"),
    r3, HSTACK("Population Standard Deviation (sigma)", TEXT(std_pop, "£#,##0.00"), "Absolute dispersion around the mean"),
    r4, HSTACK("Coefficient of Variation (CV)", TEXT(cv_score, "0.0000"), "Normalized relative dispersion (sigma / mu)"),
    r5, HSTACK("Squared Coefficient of Variation (SCV)", TEXT(scv_score, "0.0000"), "CV squared relative variance metric"),
    r6, HSTACK("Generalized Entropy GE(2)", TEXT(ge2_score, "0.0000"), "Symmetric transfer-sensitive inequality index"),
    r7, HSTACK("Gini Coefficient (Benchmark)", TEXT(gini_val, "0.0000"), "Median-sensitive benchmark inequality index"),
    
    VSTACK(headers, r1, r2, r3, r4, r5, r6, r7)
)

Building a Comparative Multi-Group CV Matrix with VSTACK

When assessing inequality across multiple corporate departments (e.g., Engineering, Sales, and Operations), combine VSTACK and BYCOL to compare relative dispersion:

=LET(
    /* Data columns for Department 1, 2, and 3 */
    dept1, FILTER(B2:B50, ISNUMBER(B2:B50)),
    dept2, FILTER(C2:C50, ISNUMBER(C2:C50)),
    dept3, FILTER(D2:D50, ISNUMBER(D2:D50)),
    
    calc_metrics, LAMBDA(name, vec,
        LET(
            m, AVERAGE(vec),
            s, STDEV.P(vec),
            cv, s / m,
            scv, cv^2,
            HSTACK(name, TEXT(ROWS(vec), "#,##0"), TEXT(m, "£#,##0"), TEXT(s, "£#,##0"), TEXT(cv, "0.0000"), TEXT(scv, "0.0000"))
        )
    ),
    
    head, HSTACK("Department Cohort", "Count (N)", "Mean Salary", "Std Deviation", "CV Score", "SCV Score"),
    row_eng, calc_metrics("Engineering", dept1),
    row_sales, calc_metrics("Sales", dept2),
    row_ops, calc_metrics("Operations", dept3),
    
    VSTACK(head, row_eng, row_sales, row_ops)
)

Core Advantages of the Formulaic Implementation

1. Instant Spilled Updates: Adding, modifying, or removing salary figures instantly recalibrates standard deviations, CV values, and entropy metrics across all reporting tiers.
2. Transfer Sensitivity Equivalence: Provides an objective comparison against the Gini coefficient to identify whether extreme compensation outliers are driving organizational inequality.
3. No VBA Macros: Native dynamic array design guarantees seamless compatibility across Excel for Windows, macOS, and Excel Online without security warnings.