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.
I'd create a pivot table based on the raw data, and filter that the way you want.
Thanks! I have my two tables...the first table is all the info related to the employee, start date, department, etc... The second table is where I get my calculations. The date I'm pointing at and the equations for hires, terms, and headcount. I tried to create a relationship between the two tables, but I am not having any luck. Let me know if you need more detail. I can send you the two tables I'm using. Thanks for your help!!
In general yes, that could be done with data model. It will be great to know on which version of Excel you are and on which platform (Windows, Mac, etc). Plus desirably to have sample file to play with it - that's not 5-minutes job to generate solution.
SergeiBaklan I’ve decided to work on this some more. I have a date table, my employee table (department, gender, age, ethnicity) and the table I use to figure monthly head counts, hires, terms and turnover percentage based on month end dates. I have all three tables in data model of my pivot table/chart but I can’t bring it all together to allow me to slice these stats down by the categories I want to show. Thanks for any help you can give me!
Perhaps you can use PowerQuery and PowerPivot for this, but that's not my forte - I hope that someone else can help. If you don't get new replies here, I'd start a new discussion. That will attract more attention.
