Dec 31 2020 07:13 AM
Dec 31 2020 07:13 AM
I have an Excel spreadsheet with columns including:
How can I use this information to calculate the number of personnel on any given day? i.e. to create something similar to this?
I'm also interested in calculating the monthly average number of personnel, as well as the number of new starts and the number that left in a given month, to identify trends.
Thank you for your help! I enjoy trying to figure this stuff out, but I'm a bit stuck with this one and I'm excited for your ideas!
Dec 31 2020 08:21 AMSolution
1) In H2, enter the following array formula confirmed with Ctrl+Shift+Enter:
Dec 31 2020 08:42 AM
Dec 31 2020 08:52 AM
3) In N2 as an array formula confirmed with Ctrl+Shift+Enter:
Sep 27 2021 07:01 PM
Sep 28 2021 03:12 AM
I'd create a pivot table based on the raw data, and filter that the way you want.
Sep 28 2021 05:02 AM
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!!
Sep 28 2021 05:23 AM
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.
Sep 28 2021 05:36 AM
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.
Sep 28 2021 07:05 AM
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
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.
Sep 28 2021 07:11 AM
Depends on goals. If for predefined date - yes. If slice and dice with periods, departments, etc - data model is more preferable.
Sep 28 2021 07:32 AM
You are right of course. The 'slicing and dicing' of large data sets is what BI was built for but, for smaller datasets that do not need to be loaded from external sources, dynamic array solutions can give database methods a run for their money. Maybe by defining a function,
= TURNOVERλ(StartDate, Period, "ProductEngineering")
If you want to give the end user the option of selecting data through the use of slicers, then the data model is the natural solution.
Sep 28 2021 08:17 AM
Yes, often the question we asked is the small part of entire picture and we may only guess what is behind. Moreover, not always OP knows what exactly (s)he'd like to have.
By the way, that's why I'm not fun of "best response" logic here. Best answer for one is not best for another one. "My computer doesn't work, what shall I do?" has million of answers, who knows what's the best in concrete case?
Sep 28 2021 08:42 AM
@Sergei Baklan I appreciate everyone's help! I'm trying to bring two tables together to allow me to slice by department, age, gender, ethnicity, etc... for head count, hires, separations, and turnover. I'm using a month end date as the the predefined date to go off of when creating the pivot table. I attached the example workbook that has both tables involved. I have the formula setup in data2 table to do this, but I would like it to be more flexible. I hope this helps. Thank you again.
Apr 07 2022 05:59 PM
@Sergei Baklan 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!