Forum Discussion
Using MS Excel to Calculate Rolling Headcount and Turnover
1) In H2, enter the following array formula confirmed with Ctrl+Shift+Enter:
=SUMPRODUCT(($B$2:$B$11<=G2)*(IF($C$2:$C$11="",G2,$C$2:$C$11)>=G2))
Fill down.
Am I missing the point? Surely a headcount simply requires COUNTIFS to quantify the total number of hires up to and including the census date minus the total number of departures (adjusted by the headcount at the dawn of time if non-zero). Turnover is simply the change between to census dates, though the differences between two COUNTIFS can be combined into one by adding a 'started after' criterion.
These days, I might dress the solution up a bit and introduce a Lambda function 'HEADCOUNTλ'
= LAMBDA(Date,
COUNTIFS(Table1[Date Started],"<="&Date)
- COUNTIFS(Table1[Date Left],"<="&Date) )so that the worksheet formula reads
= HEADCOUNTλ(CensusDate)I am so glad to see the back of CSE though. I had schooled myself to use absolute references only and commit all formulae with CSE but it is such a relief to get rid of the practice.
Depends on goals. If for predefined date - yes. If slice and dice with periods, departments, etc - data model is more preferable.