Employee forecasting - Multi-criteria calculation

P

Patrick020

New Member
Joined
Mar 20, 2018
Messages
15
Hello everybody.
<o:p></o:p>
I’m a bit out of my depth with this one!
<o:p></o:p>
Effectively what I am trying to do is calculate the time commitment of each employee based on a number of criteria. <o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
The Criteria are provided in brackets. <o:p></o:p>
<o:p></o:p>
Please see the below data: <o:p></o:p>
<o:p></o:p>
[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD][/TD]
[TD="width: 104, bgcolor: transparent"]Job 1<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Job 2<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Job 3<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Job 4<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Job 5<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]Job 6<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]Answer <o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Criteria 1 (High)<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]High<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]High<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Low<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]High<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]High<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]High<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Criteria 2 (Yes) <o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Yes<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]No <o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Yes<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Yes<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Yes<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]Yes<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Days FTE<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]2.5<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]3<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]2<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]4<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]1.5<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]2<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Employee 1<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Primary<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Primary<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]0.5<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Employee 2<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Primary <o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Primary<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]3<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Employee 3<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Primary<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Primary<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]Primary<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]1.5<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Employee 4<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Primary<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]2<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Employee 5<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Primary<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Primary<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]Primary<o:p></o:p>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]Primary<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]3<o:p></o:p>
[/TD]
[/TR]
</tbody>[/TABLE]
<o:p></o:p>
The answer I am trying to calculate has been provided in the final column. <o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Patrick020 said:
Hello everybody.
<o:p></o:p>
I’m a bit out of my depth with this one!
<o:p></o:p>
Effectively what I am trying to do is calculate the time commitment of each employee based on a number of criteria. <o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
The Criteria are provided in brackets. <o:p></o:p>
<o:p></o:p>
Please see the below data: <o:p></o:p>
<o:p></o:p>
[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD][/TD]
[TD]Job 1<o:p></o:p>[/TD]
[TD]Job 2<o:p></o:p>[/TD]
[TD]Job 3<o:p></o:p>[/TD]
[TD]Job 4<o:p></o:p>[/TD]
[TD]Job 5<o:p></o:p>[/TD]
[TD="width: 64, bgcolor: transparent"]Job 6<o:p></o:p>[/TD]
[TD="width: 64, bgcolor: transparent"]Answer <o:p></o:p>[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Criteria 1 (High)<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]High<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]High<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]Low<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]High<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]High<o:p></o:p>[/TD]
[TD="width: 64, bgcolor: transparent"]High<o:p></o:p>[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Criteria 2 (Yes) <o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]Yes<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]No <o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]Yes<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]Yes<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]Yes<o:p></o:p>[/TD]
[TD="width: 64, bgcolor: transparent"]Yes<o:p></o:p>[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Days FTE<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]2.5<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]3<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]2<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]4<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]1.5<o:p></o:p>[/TD]
[TD="width: 64, bgcolor: transparent"]2<o:p></o:p>[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Employee 1<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]Primary<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]Primary<o:p></o:p>[/TD]
[TD="width: 64, bgcolor: transparent"]N/A<o:p></o:p>[/TD]
[TD="width: 64, bgcolor: transparent"]0.5<o:p></o:p>[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Employee 2<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]Primary <o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]Primary<o:p></o:p>[/TD]
[TD="width: 64, bgcolor: transparent"]N/A<o:p></o:p>[/TD]
[TD="width: 64, bgcolor: transparent"]3<o:p></o:p>[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Employee 3<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]Primary<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]Primary<o:p></o:p>[/TD]
[TD="width: 64, bgcolor: transparent"]Primary<o:p></o:p>[/TD]
[TD="width: 64, bgcolor: transparent"]1.5<o:p></o:p>[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Employee 4<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]Primary<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>[/TD]
[TD="width: 64, bgcolor: transparent"]N/A<o:p></o:p>[/TD]
[TD="width: 64, bgcolor: transparent"]2<o:p></o:p>[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Employee 5<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]Primary<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]Primary<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]Primary<o:p></o:p>[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o:p></o:p>[/TD]
[TD="width: 64, bgcolor: transparent"]Primary<o:p></o:p>[/TD]
[TD="width: 64, bgcolor: transparent"]3<o:p></o:p>[/TD]
[/TR]
</tbody>[/TABLE]
<o:p></o:p>
The answer I am trying to calculate has been provided in the final column. <o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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
Click to expand...

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
 
Last edited:
Upvote 0
markmzz said:
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
Click to expand...

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
 
Upvote 0
​
Patrick020 said:
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
Click to expand...
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:

[TABLE="class: grid, width: 1212"]
<tbody>[TR]
[TD]
[/TD]
[TD]Job 1[/TD]
[TD]Job 2[/TD]
[TD]Job 3[/TD]
[TD]Job 4[/TD]
[TD]Job 5[/TD]
[TD]Job 6[/TD]
[TD]Answer[/TD]
[TD]
[/TD]
[TD]Step by Step[/TD]
[TD]=MMULT(TRANSPOSE(ROW(A$6:A$10)/ ROW(A$6:A$10)),--($B$6:$G$10="Primary"))[/TD]
[TD]The Start Formula[/TD]
[/TR]
[TR]
[TD]Criteria 1 (High)[/TD]
[TD]High[/TD]
[TD]High[/TD]
[TD]Low[/TD]
[TD]High[/TD]
[TD]High[/TD]
[TD]High[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]1[/TD]
[TD]=MMULT(TRANSPOSE({6;7;8;9;10}/ {6;7;8;9;10}),--($B$6:$G$10="Primary"))[/TD]
[TD]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).[/TD]
[/TR]
[TR]
[TD]Criteria 2 (Yes)[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]2[/TD]
[TD]=MMULT(TRANSPOSE({1;1;1;1;1}),
--($B$6:$G$10="Primary"))[/TD]
[TD]Performs the division numbers of lines of range B6:B10 for themselves and get the matrix 5x1 ({1;1;1;1;1}).[/TD]
[/TR]
[TR]
[TD]Days FTE[/TD]
[TD="align: right"]2,5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1,5[/TD]
[TD="align: right"]2[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]3[/TD]
[TD]=MMULT({1\1\1\1\1},
--($B$6:$G$10="Primary"))[/TD]
[TD]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}).[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Result[/TD]
[TD]
[/TD]
[TD="align: right"]4[/TD]
[TD]=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}))[/TD]
[TD]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}[/TD]
[/TR]
[TR]
[TD]Employee 1[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]Primary[/TD]
[TD]N/A[/TD]
[TD]Primary[/TD]
[TD]N/A[/TD]
[TD="align: right"]0,5[/TD]
[TD]
[/TD]
[TD="align: right"]5[/TD]
[TD]=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})[/TD]
[TD]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}[/TD]
[/TR]
[TR]
[TD]Employee 2[/TD]
[TD]Primary[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]Primary[/TD]
[TD]N/A[/TD]
[TD="align: right"]3[/TD]
[TD]
[/TD]
[TD="align: right"]6[/TD]
[TD]={1\2\2\2\3\2}[/TD]
[TD]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}).[/TD]
[/TR]
[TR]
[TD]Employee 3[/TD]
[TD]N/A[/TD]
[TD]Primary[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]Primary[/TD]
[TD]Primary[/TD]
[TD="align: right"]1,5[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Employee 4[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]Primary[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD="align: right"]2[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Employee 5[/TD]
[TD]N/A[/TD]
[TD]Primary[/TD]
[TD]Primary[/TD]
[TD]Primary[/TD]
[TD]N/A[/TD]
[TD]Primary[/TD]
[TD="align: right"]3[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]***********[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]******[/TD]
[TD]**[/TD]
[TD]*******[/TD]
[TD]*****************************************[/TD]
[TD]******************************************************[/TD]
[/TR]
</tbody>[/TABLE]


I hope that this helps.

Markmzz
 
Upvote 0
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Calculate sums based on values
Replies
3
Views
387
NateSC
NateSC
O
  • Question Question
Count only one occurrence of all criteria being met
Replies
8
Views
2K
KRice
K
I
  • Question Question
Find the second highest attribute based on criteria. I know its a combo of index(match(large, but I just can't get it to work correctly.
Replies
1
Views
658
StephenCrump
S
E
  • Question Question
Adding a custom message.
Replies
1
Views
705
Micron
M
P
  • Question Question
Lambda or VBA to solve the pairs webinar problem!!!
Replies
0
Views
183
Phantom1
P

Forum statistics

Threads
1,226,693
Messages
6,192,471
Members
453,726
Latest member
JoeH57

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top