How to extract ending headcount for the month based on a per week basis

[Rhon Alvarez]

Hi Team,

I have a data consisting of HC being monitored on a weekly basis;

seeking your assistance so that I could get the current month's running week's ending HC.

example for September: Starting HC is the ending HC from August and ending HC is the 4th week of September WB0924
for October: Starting HC is the ending HC from September (194) and ending HC is the 5th week of October WB1029
while for November: Starting HC is the ending HC from October (78) and running week's HC WB1112 (13)



Please see attached file

 

[pecoflyer]

1. You posted an xls file. What is your XL version?
2. What does "HC" mean?

 

[Rhon Alvarez]

hi @pecoflyer,

i am using a 97-2003 version of excel.

HC means Head Count

 

[pecoflyer]

I suggest you upgrade to a newer XL version, as not many still use these old ones therefore limiting possibilities for help

 

[Rhon Alvarez]

hi @pecoflyer ,

thanks! but this is what the company is using for now and I have no rights to request for an upgrade. what i need is just a simple formula to obtain what is needed.

 

[p45cal]

what i need is just a simple formula

With Excel 97-2003 that's not going to happen. By stating that, someone may take it as a challenge and produce a simple formula!
It wouldn't be straightforward to do it with formulae-only with more recent versions either.

I can get you the figures you need, but not in the arrangement that you want.
In the attached, changed and added (simple) formulae in columns A, B & C.
At cell I10 a pivot table giving you data for your first table.
At cell P10 a pivot giving you your end of month/running week's HC. The Max of Week column in that pivot is only so that I can filter the results for the final week in each month (see filter for the WB field), you can ignore/hide that column.