How to compute the total hours the person was available
- Thread starter Shayeebur
- Start date
NARAYANK991
Excel Ninja
Hi Shayeebur ,
Check your file now.
Narayan
Check your file now.
Narayan
Aurelie
Member
@NARAYANK991
I would as well be interested to know how this formula works, just for the sake of it
BR
I would as well be interested to know how this formula works, just for the sake of it
BR
Somendra Misra
Excel Ninja
@oldchippy , @NARAYANK991
Pardon me for answering this. I also came with that formula but NarayanK991 was fast, I am still far behind in the time race.
See the ROUND() is clear it will round the result of SUMPRODUCT() by 2 Significat Digit.
Now OP want to add hours per person for 7 days only if any days has XXXXXX in it. So, Person 1 has XXXXXX on Day 1 and Day 7. So the sum of those hours should come in result. Now in Day 1 total of all present person is say 50 hours and there are 3 person present so per person hours = 50/3.
Now on day 7 there are 2 person present and hours are 30, so per person =30/2. So for person 1 total of 7 days =50/3+30/2.
So the formula
SUMPRODUCT((C$11:C$17="XXXXXX")*($J$11:$J$17/MMULT(--($C$11:$I$17="XXXXXX"),{1;1;1;1;1;1;1})))
In this formula the red portion will check for Cornell (name as in example in file) where for Day 1 to Day 7 is XXXXXX so some TRUE & FALSE. This will be multiplied by per person hour of that day. So blue portion is total hours per day by each person / Orange portion is matrix multiplication of Per row where there is XXXXXX * row vector of 1.
SUMPRODUCT(({TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE})*({50;30;44;42;15;16;40}/{3;2;3;3;1;1;3}))
now you can see red portion returns where there is XXXXXX for cornell, this is multiplied by his each day contribution and than sum product will sum it. ({50;30;44;42;15;16;40}/{3;2;3;3;1;1;3}) -- first array is total hours per day / total manpower present. when this is multipled by red portion you get goal hours.
Hope this will clear your doubt. I might not be so good in explaining, pardon me for that. I am still learning.
@NARAYANK991 ... Excuse me for answering the question asked to you.
Regards!
Pardon me for answering this. I also came with that formula but NarayanK991 was fast, I am still far behind in the time race
.See the ROUND() is clear it will round the result of SUMPRODUCT() by 2 Significat Digit.
Now OP want to add hours per person for 7 days only if any days has XXXXXX in it. So, Person 1 has XXXXXX on Day 1 and Day 7. So the sum of those hours should come in result. Now in Day 1 total of all present person is say 50 hours and there are 3 person present so per person hours = 50/3.
Now on day 7 there are 2 person present and hours are 30, so per person =30/2. So for person 1 total of 7 days =50/3+30/2.
So the formula
SUMPRODUCT((C$11:C$17="XXXXXX")*($J$11:$J$17/MMULT(--($C$11:$I$17="XXXXXX"),{1;1;1;1;1;1;1})))
In this formula the red portion will check for Cornell (name as in example in file) where for Day 1 to Day 7 is XXXXXX so some TRUE & FALSE. This will be multiplied by per person hour of that day. So blue portion is total hours per day by each person / Orange portion is matrix multiplication of Per row where there is XXXXXX * row vector of 1.
SUMPRODUCT(({TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE})*({50;30;44;42;15;16;40}/{3;2;3;3;1;1;3}))
now you can see red portion returns where there is XXXXXX for cornell, this is multiplied by his each day contribution and than sum product will sum it. ({50;30;44;42;15;16;40}/{3;2;3;3;1;1;3}) -- first array is total hours per day / total manpower present. when this is multipled by red portion you get goal hours.
Hope this will clear your doubt. I might not be so good in explaining, pardon me for that. I am still learning.
@NARAYANK991 ... Excuse me for answering the question asked to you.
Regards!