12-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!
12-31-2020 08:21 AM
Solution1) 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.
12-31-2020 08:42 AM
In O2:
=COUNTIFS($B$2:$B$11,">="&M2,$B$2:$B$11,"<"&EDATE(M2,1))
In P2:
=COUNTIFS($C$2:$C$11,">="&M2,$C$2:$C$11,"<"&EDATE(M2,1))
Fill down.
12-31-2020 08:44 AM
12-31-2020 08:45 AM
12-31-2020 08:52 AM
3) In N2 as an array formula confirmed with Ctrl+Shift+Enter:
=SUM(IF(IF(IF($C$2:$C$11="",EDATE(M2,1),$C$2:$C$11)<EDATE(M2,1),IF($C$2:$C$11="",EDATE(M2,1),$C$2:$C$11),EDATE(M2,1))>IF($B$2:$B$11>M2,$B$2:$B$11,M2),IF(IF($C$2:$C$11="",EDATE(M2,1),$C$2:$C$11)<EDATE(M2,1),IF($C$2:$C$11="",EDATE(M2,1),$C$2:$C$11),EDATE(M2,1))-IF($B$2:$B$11>M2,$B$2:$B$11,M2),0))/(EDATE(M2,1)-M2)
Fill down.