22.13 HR
HR interviews ask attrition, offer acceptance, and time to fill. "We got 200 CVs" is the vanity metric of this function. Do not invent salaries in this table. Headcount does not need pay (Module 21.4).
| KPI | What the number means | Who uses it | Action it drives |
|---|---|---|---|
| Attrition | People who left in the month divided by headcount at the start. | HR business partner, city lead | A spike is exit interviews, not a hiring poster. |
| Offer acceptance | Offers accepted divided by offers made. | Recruiter | A low rate means pay, role, or delay. More CVs will not fix a refused offer. |
| Time to fill | Sum of days to fill divided by roles filled. | HR lead | A long fill on one role blocks that team's work. Report the role, not only the average. |
Fictional sample rows. One month. Type these rows into a new sheet from cell A1, press Ctrl + T, and name the table tblHR (Table Design › Table Name).
| City | Start headcount | Left | Offers | Accepted | Days-to-fill sum | Roles filled | Applications |
|---|---|---|---|---|---|---|---|
| Pune | 40 | 2 | 8 | 6 | 48 | 4 | 200 |
| Nashik | 30 | 3 | 4 | 3 | 60 | 3 | 40 |
| Kolhapur | 20 | 1 | 2 | 2 | 20 | 2 | 30 |
| Nagpur | 25 | 0 | 3 | 1 | 45 | 1 | 150 |
| Solapur | 15 | 1 | 2 | 2 | 16 | 2 | 20 |
| Sambhaji Nagar | 18 | 2 | 4 | 2 | 36 | 2 | 80 |
Excel formulas for the whole file (type them in empty cells below or beside the table):
| KPI | Excel formula | Result | Number format |
|---|---|---|---|
| Attrition | =SUM(tblHR[Left])/SUM(tblHR[Start headcount]) |
6.1% | 0.0% |
| Offer acceptance | =SUM(tblHR[Accepted])/SUM(tblHR[Offers]) |
69.6% | 0.0% |
| Time to fill | =SUM(tblHR[Days-to-fill sum])/SUM(tblHR[Roles filled]) |
16.1 days | 0.0 |
Calculated columns (one result per row). Click the first empty column of the table, type the header, then the formula. The Table fills every row by itself.
| New column | Formula in the first row |
|---|---|
| Attrition | =[@Left]/[@[Start headcount]] |
| Offer acceptance | =[@Accepted]/[@Offers] |
| Time to fill | =[@[Days-to-fill sum]]/[@[Roles filled]] |
Expected result per row:
| City | Attrition | Offer acceptance | Time to fill |
|---|---|---|---|
| Pune | 5.0% | 75.0% | 12.0 days |
| Nashik | 10.0% | 75.0% | 20.0 days |
| Kolhapur | 5.0% | 100.0% | 10.0 days |
| Nagpur | 0.0% | 33.3% | 45.0 days |
| Solapur | 6.7% | 100.0% | 8.0 days |
| Sambhaji Nagar | 11.1% | 50.0% | 18.0 days |
One city with SUMIFS. Attrition for Pune only:
=SUMIFS(tblHR[Left],tblHR[City],"Pune")/SUMIFS(tblHR[Start headcount],tblHR[City],"Pune")
Result for Pune: 5.0%. Change "Pune" to a cell reference (for example $K$1) and add a drop-down, and the same formula answers for any city.
| Good KPI | Vanity metric | |
|---|---|---|
| From the same rows | Nagpur acceptance 33.3% and 45 days to fill one role | Nagpur 150 applications, and Pune 200 applications |
| Why | Two offers were refused and the one hire took a month and a half. | The CV card says recruiting "worked". Nashik hired 3 people from 40 applications. |
What the manager does. The Nashik HR partner holds exit conversations for the 3 leavers (10% of a 30-person start). That is the week's work, not a job fair. The Pune partner does not celebrate 200 CVs. Pune acceptance is 6 ÷ 8 = 75.0% and time to fill is 48 ÷ 4 = 12 days, which is fine in this file. Ask Nagpur why two offers died before posting the role again.
Ravindra Bagale's Tip
Write the denominator in the header: "Attrition (left ÷ start headcount)". People who joined this month do not go into Left.
Ravindra Bagale's Tip – मराठी
Header मध्ये denominator लिहा: "Attrition (left ÷ start headcount)". या महिन्यात join झालेले लोक Left मध्ये जात नाहीत.
Ravindra Bagale's Tip – हिंदी
Header में denominator लिखें: "Attrition (left ÷ start headcount)". इस महीने join हुए लोग Left में नहीं जाते.