Hi everyone,
Just had a query with trying to calculate some formula.
As you can see in the table below, I want to calculate the % of the employee that will be away to the total of headcount of each manager. So in J2 for Alice on Mar 1, there is 1 person that will be absent for 2 days. So if Today is Mar 1, J2 would be 2% of the headcount. the Max threshold for any Manger should not exceed 10%(indicated in J1). If today is Mar 3, that person will be back and Alice would be at 0%.
I hope I've explained it clearly, if not, let me know.
Just had a query with trying to calculate some formula.
As you can see in the table below, I want to calculate the % of the employee that will be away to the total of headcount of each manager. So in J2 for Alice on Mar 1, there is 1 person that will be absent for 2 days. So if Today is Mar 1, J2 would be 2% of the headcount. the Max threshold for any Manger should not exceed 10%(indicated in J1). If today is Mar 3, that person will be back and Alice would be at 0%.
Book1 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | Name | Manager | Time Off | Date | # of Days | Manager | Total Headcount | 10% | ||||
2 | Alan | Alice | Vacation | 15-Mar | 10 | Alice | 50 | |||||
3 | Bob | Alice | Sick | 01-Mar | 4 | Kate | 40 | |||||
4 | charlie | Alice | Personal Day | 10-Mar | 2 | |||||||
5 | Debbie | Kate | Vacation | 15-Mar | 5 | |||||||
6 | Erin | Kate | Sick | 01-Mar | 1 | |||||||
7 | Frank | Kate | Personal Day | 10-Mar | 1 | |||||||
Sheet1 |
I hope I've explained it clearly, if not, let me know.