Employee forecasting - Multi-criteria calculation

[Patrick020]

Hello everybody.
<o></o>
I’m a bit out of my depth with this one!
<o></o>
Effectively what I am trying to do is calculate the time commitment of each employee based on a number of criteria. <o></o>
<o></o>
Each employee is allocated to a number of jobs – the time commitment for each of these jobs is provided in FTE (i.e. if the time required for a job is 2 FTE, this could indicate one employee for two days, or two employees for one day each). <o></o>
<o></o>
The Criteria are provided in brackets. <o></o>
<o></o>
Please see the below data: <o></o>
<o></o>

	Job 1<o></o>	Job 2<o></o>	Job 3<o></o>	Job 4<o></o>	Job 5<o></o>	Job 6<o></o>	Answer <o></o>
Criteria 1 (High)<o></o>	High<o></o>	High<o></o>	Low<o></o>	High<o></o>	High<o></o>	High<o></o>
Criteria 2 (Yes) <o></o>	Yes<o></o>	No <o></o>	Yes<o></o>	Yes<o></o>	Yes<o></o>	Yes<o></o>
Days FTE<o></o>	2.5<o></o>	3<o></o>	2<o></o>	4<o></o>	1.5<o></o>	2<o></o>
Employee 1<o></o>	N/A<o></o>	N/A<o></o>	Primary<o></o>	N/A<o></o>	Primary<o></o>	N/A<o></o>	0.5<o></o>
Employee 2<o></o>	Primary <o></o>	N/A<o></o>	N/A<o></o>	N/A<o></o>	Primary<o></o>	N/A<o></o>	3<o></o>
Employee 3<o></o>	N/A<o></o>	Primary<o></o>	N/A<o></o>	N/A<o></o>	Primary<o></o>	Primary<o></o>	1.5<o></o>
Employee 4<o></o>	N/A<o></o>	N/A<o></o>	N/A<o></o>	Primary<o></o>	N/A<o></o>	N/A<o></o>	2<o></o>
Employee 5<o></o>	N/A<o></o>	Primary<o></o>	Primary<o></o>	Primary<o></o>	N/A<o></o>	Primary<o></o>	3<o></o>

<tbody>
</tbody>

<o></o>
The answer I am trying to calculate has been provided in the final column. <o></o>
<o></o>
Taking Employee 1 as an example to explain the logic: Employee 1 is not assigned to Job 1, Job 2, Job 4 or Job 6. Employee 1 is assigned to Job 3 but Criteria 1 is not met so we do not include Job 3 in the calculation. <o></o>
<o></o>
Employee 1 is assigned to Job 5 and both criteria are met. This Job takes 1.5 FTE’s and we can see that three employees are assigned to this Job – therefore 1.5 /3 = 0.5 which is the answer.

Thanks for your help
Patrick

 

[markmzz]

Hello everybody.
<o></o>
I’m a bit out of my depth with this one!
<o></o>
Effectively what I am trying to do is calculate the time commitment of each employee based on a number of criteria. <o></o>
<o></o>
Each employee is allocated to a number of jobs – the time commitment for each of these jobs is provided in FTE (i.e. if the time required for a job is 2 FTE, this could indicate one employee for two days, or two employees for one day each). <o></o>
<o></o>
The Criteria are provided in brackets. <o></o>
<o></o>
Please see the below data: <o></o>
<o></o>

	Job 1<o></o>	Job 2<o></o>	Job 3<o></o>	Job 4<o></o>	Job 5<o></o>	Job 6<o></o>	Answer <o></o>
Criteria 1 (High)<o></o>	High<o></o>	High<o></o>	Low<o></o>	High<o></o>	High<o></o>	High<o></o>
Criteria 2 (Yes) <o></o>	Yes<o></o>	No <o></o>	Yes<o></o>	Yes<o></o>	Yes<o></o>	Yes<o></o>
Days FTE<o></o>	2.5<o></o>	3<o></o>	2<o></o>	4<o></o>	1.5<o></o>	2<o></o>
Employee 1<o></o>	N/A<o></o>	N/A<o></o>	Primary<o></o>	N/A<o></o>	Primary<o></o>	N/A<o></o>	0.5<o></o>
Employee 2<o></o>	Primary <o></o>	N/A<o></o>	N/A<o></o>	N/A<o></o>	Primary<o></o>	N/A<o></o>	3<o></o>
Employee 3<o></o>	N/A<o></o>	Primary<o></o>	N/A<o></o>	N/A<o></o>	Primary<o></o>	Primary<o></o>	1.5<o></o>
Employee 4<o></o>	N/A<o></o>	N/A<o></o>	N/A<o></o>	Primary<o></o>	N/A<o></o>	N/A<o></o>	2<o></o>
Employee 5<o></o>	N/A<o></o>	Primary<o></o>	Primary<o></o>	Primary<o></o>	N/A<o></o>	Primary<o></o>	3<o></o>

<tbody>
</tbody>

<o></o>
The answer I am trying to calculate has been provided in the final column. <o></o>
<o></o>
Taking Employee 1 as an example to explain the logic: Employee 1 is not assigned to Job 1, Job 2, Job 4 or Job 6. Employee 1 is assigned to Job 3 but Criteria 1 is not met so we do not include Job 3 in the calculation. <o></o>
<o></o>
Employee 1 is assigned to Job 5 and both criteria are met. This Job takes 1.5 FTE’s and we can see that three employees are assigned to this Job – therefore 1.5 /3 = 0.5 which is the answer.

Thanks for your help
Patrick

Hi!

Try the Array Formula (use Ctrl+Shift+Enter to enter the formula) below in H6 and copy down:

=SUM(IF($B6:$G6="Primary",IF($B$2:$G$2="High",IF($B$3:$G$3="Yes",$B$4:$G$4/
MMULT({1,1,1,1,1},--($B$6:$G$10="Primary"))))))

Or

=SUM(IF($B6:$G6="Primary",IF($B$2:$G$2="High",IF($B$3:$G$3="Yes",$B$4:$G$4/
MMULT(TRANSPOSE(ROW(A$6:A$10)/ROW(A$6:A$10)),--($B$6:$G$10="Primary"))))))

Markmzz

 

[Patrick020]

Hi!

Try the Array Formula (use Ctrl+Shift+Enter to enter the formula) below in H6 and copy down:

=SUM(IF($B6:$G6="Primary",IF($B$2:$G$2="High",IF($B$3:$G$3="Yes",$B$4:$G$4/
MMULT({1,1,1,1,1},--($B$6:$G$10="Primary"))))))

Or

=SUM(IF($B6:$G6="Primary",IF($B$2:$G$2="High",IF($B$3:$G$3="Yes",$B$4:$G$4/
MMULT(TRANSPOSE(ROW(A$6:A$10)/ROW(A$6:A$10)),--($B$6:$G$10="Primary"))))))

Markmzz

Wow, thank you Markmzz!

Both of these formula worked perfectly (I will use the second given the size of the data) - you've literally saved me at least a weeks worth of work here!

If you don't mind, please can you explain the second half of the formula (MMULT onwards) as I'm struggling to follow the logic you have applied.

Patrick

 

[markmzz]

​

Wow, thank you Markmzz!
Both of these formula worked perfectly (I will use the second given the size of the data) - you've literally saved me at least a weeks worth of work here!
If you don't mind, please can you explain the second half of the formula (MMULT onwards) as I'm struggling to follow the logic you have applied.
Patrick

Hi!

You're welcome and thanks for the feedback.

Now, I'll try to explain the second part of the formula. In my opinion, the best way to do that is look at the formula step by step.

Lets go:

	Job 1	Job 2	Job 3	Job 4	Job 5	Job 6	Answer		Step by Step	=MMULT(TRANSPOSE(ROW(A$6:A$10)/ ROW(A$6:A$10)),--($B$6:$G$10="Primary"))	The Start Formula
Criteria 1 (High)	High	High	Low	High	High	High			1	=MMULT(TRANSPOSE({6;7;8;9;10}/ {6;7;8;9;10}),--($B$6:$G$10="Primary"))	Using the ROW function, get the numbers of all the rows in the range B6:B10 ({6;7;8;9;10}) in the fraction ROW(A$6:A$10)/ROW(A$6:A$10).
Criteria 2 (Yes)	Yes	No	Yes	Yes	Yes	Yes			2	=MMULT(TRANSPOSE({1;1;1;1;1}), --($B$6:$G$10="Primary"))	Performs the division numbers of lines of range B6:B10 for themselves and get the matrix 5x1 ({1;1;1;1;1}).
Days FTE	2,5	3	2	4	1,5	2			3	=MMULT({1\1\1\1\1}, --($B$6:$G$10="Primary"))	Using the TRANSPOSE function, get the matrix transpose of the matrix 5x1 ({1;1;1;1;1}) whose result is the matrix 1x5 ({1\1\1\1\1}).
							Result		4	=MMULT({1\1\1\1\1}, --({FALSE\FALSE\TRUE\FALSE\TRUE\FALSE; TRUE\FALSE\FALSE\FALSE\TRUE\FALSE; FALSE\TRUE\FALSE\FALSE\TRUE\TRUE; FALSE\FALSE\FALSE\TRUE\FALSE\FALSE; FALSE\TRUE\TRUE\TRUE\FALSE\TRUE}))	Compares the contents of the cells in the range C6:G10 (distribution of employees by job) with the Primary word and get the matrix 5x6 below {FALSE\FALSE\TRUE\FALSE\TRUE\FALSE; TRUE\FALSE\FALSE\FALSE\TRUE\FALSE; FALSE\TRUE\FALSE\FALSE\TRUE\TRUE; FALSE\FALSE\FALSE\TRUE\FALSE\FALSE; FALSE\TRUE\TRUE\TRUE\FALSE\TRUE}
Employee 1	N/A	N/A	Primary	N/A	Primary	N/A	0,5		5	=MMULT({1\1\1\1\1}, {0\0\1\0\1\0; 1\0\0\0\1\0; 0\1\0\0\1\1; 0\0\0\1\0\0; 0\1\1\1\0\1})	Using the multiply by -1 two times (-- or -1*-1) to transform TRUE in 1 and FALSE in 0 get the matrix 5x6 below {1\1\1\1\1}, {0\0\1\0\1\0; 1\0\0\0\1\0; 0\1\0\0\1\1; 0\0\0\1\0\0; 0\1\1\1\0\1}
Employee 2	Primary	N/A	N/A	N/A	Primary	N/A	3		6	={1\2\2\2\3\2}	Finally, using the MMULT function multiplies the matrix 1x5 by matrix 5x6 and get the matrix 1x6 in the end ({1\2\2\2\3\2} - with the distribution of employees for each one of the six jobs). Remember that the first part of the complete formula is enough to make your selection of jobs which meet the criteria. In the case of Employee 2, we have only the first and fifth jobs ({1\0\0\0\3\0}).
Employee 3	N/A	Primary	N/A	N/A	Primary	Primary	1,5
Employee 4	N/A	N/A	N/A	Primary	N/A	N/A	2
Employee 5	N/A	Primary	Primary	Primary	N/A	Primary	3
***********	*******	*******	*******	*******	*******	*******	******	**	*******	*****************************************	******************************************************

<tbody>
</tbody>

I hope that this helps.

Markmzz

 

[markmzz]

Hi!

Just one more detail:

In the case of Employee 2, we have only the first and fifth Jobs and

the penultimate step of the whole formula in this case is SUM({2,5\FALSE\FALSE\FALSE\0,5\FALSE}).

So when we use the function SUM, we get the value 3.

Markmzz