Variance analysis for headcount

Dears, I've been struggle to find a way to do a variance analysis on the employees of my company. The aim is that I can dynamically select two periods (month 3>March vs month 1>January) and identify ...

excel

Formulas and Functions

PeterBartholomew1

Nov 16, 2024

This requires Excel 365 insider at present. The formula

= LET(
    employee, CHOOSECOLS(Table2[#All], 1, 5, 2),
    month,    Table2[[#All],[Month]],
    FTE,      Table2[[#All],[FTE]],
    PT,       PIVOTBY(employee, month, FTE, SUM,,0,,0),
    PT
  )

will create a pivoted array

Pivoted

To calculate the difference between month 1 and month 3, adds to the length of the formula

= LET(
    employee, CHOOSECOLS(Table2[#All], 1, 5, 2),
    month,    Table2[[#All],[Month]],
    FTE,      Table2[[#All],[FTE]],
    PT,       PIVOTBY(employee, month, FTE, SUM,,0,,0),
    onroll,   DROP(PT, 1,3),
    counts,   IF(onroll<>"", VALUE(onroll), 0),
    change,   TAKE(counts,,-1) - TAKE(counts,,1),
    HSTACK(TAKE(DROP(PT,1),,3), change)
  )

giving

Change

That could be filtered to give non-zero changes only

Filtered

    FILTER(HSTACK(TAKE(DROP(PT,1),,3), change), change)