Complex weighted distribution - multiple input sheets

[Patrick020]

Hi everyone <o></o>
<o></o>
Would appreciate some help with a formula which tracks the percentage of time each employee utilises particular ‘Requirements’ – this information is determined by the Job each employee is assigned to.

I have completed an initial version of this (with an ugly combination of SUMPRODUCT, COUNTIF, INDEXMATCH) but would like to improve it to take into account the varying duration of each job. This is beyond my ability in Excel.

Effectively there are three sheets – two input sheets and one output sheet: <o></o>
<o></o>
INPUT SHEET ONE - Contains the name of each employee where you assign jobs based on a drop down/data validation.<o></o>

[TABLE="class: MsoNormalTable"]
<tbody>[TR]
[TD]Employee 1 <o></o>
[/TD]
[TD]Employee 2<o></o>
[/TD]
[TD]Employee 3<o></o>
[/TD]
[TD]Employee 4<o></o>
[/TD]
[/TR]
[TR]
[TD]Job 1 <o></o>
[/TD]
[TD]Job 4<o></o>
[/TD]
[TD]Job 1<o></o>
[/TD]
[TD]Job 1<o></o>
[/TD]
[/TR]
[TR]
[TD]Job 2<o></o>
[/TD]
[TD]Job 5<o></o>
[/TD]
[TD]Job 2<o></o>
[/TD]
[TD]Job 2<o></o>
[/TD]
[/TR]
[TR]
[TD]Job 3<o></o>
[/TD]
[TD]Job 6<o></o>
[/TD]
[TD]N/A<o></o>
[/TD]
[TD]Job 3<o></o>
[/TD]
[/TR]
[TR]
[TD]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Job 4<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Job 5<o></o>
[/TD]
[/TR]
</tbody>[/TABLE]

INPUT SHEET TWO - Contains each job plotted against Requirements. Requirements are either ‘High’ or ‘Low’. The Duration figure states how many days that job will take (this is the data I am struggling to integrate).<o></o>

[TABLE="class: MsoNormalTable"]
<tbody>[TR]
[TD][/TD]
[TD]Job 1 <o></o>
[/TD]
[TD]Job 2<o></o>
[/TD]
[TD]Job 3<o></o>
[/TD]
[TD]Job 4<o></o>
[/TD]
[TD]Job 5<o></o>
[/TD]
[TD]Job 6<o></o>
[/TD]
[/TR]
[TR]
[TD]Duration (Days)<o></o>
[/TD]
[TD]0.5<o></o>
[/TD]
[TD]2<o></o>
[/TD]
[TD]3<o></o>
[/TD]
[TD]5<o></o>
[/TD]
[TD]1<o></o>
[/TD]
[TD]2<o></o>
[/TD]
[/TR]
[TR]
[TD]<o></o>
[/TD]
[TD]<o></o>
[/TD]
[TD]<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 95, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Requirement 1<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Low <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Low <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 95, bgcolor: transparent"]N/A<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Requirement 2<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Low <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Low <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 95, bgcolor: transparent"]Low <o></o>
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Requirement 3<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Low <o></o>
[/TD]
[TD="width: 95, bgcolor: transparent"]Low <o></o>
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Requirement 4<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Low <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]High <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Low <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 95, bgcolor: transparent"]N/A<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Requirement 5<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]High <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]High <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]High <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 95, bgcolor: transparent"]High<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Requirement 6<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]High <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]High <o></o>
[/TD]
[TD="width: 95, bgcolor: transparent"]N/A<o></o>
[/TD]
[/TR]
</tbody>[/TABLE]

OUTPUT SHEET - ASSUMPTION

[TABLE="class: MsoNormalTable"]
<tbody>[TR]
[TD]High<o></o>
[/TD]
[TD]80%<o></o>
[/TD]
[/TR]
[TR]
[TD]Low<o></o>
[/TD]
[TD]20% <o></o>
[/TD]
[/TR]
</tbody>[/TABLE]

The Output Sheet looks up the Employee/Job allocation in Sheet 1 and subsequently looks up the Job/Requirement mapping in Sheet 2 - these Requirements are apportioned across the Employees time. This allocation is based on an assumption that 80% of employee time is spent on High Requirements and 20% is spent on Low Requirements.

This is regardless of how many Requirements there are i.e. if there are two‘High’ requirements these each account for 40% of time. If there are four ‘Low’Requirements these account for 5%.

Using the data above I have provided my current answer and the correct answer (bolded) below.

OUTPUT SHEET - (My answer vs correct answer)
[TABLE="class: MsoNormalTable"]
<tbody>[TR]
[TD][/TD]
[TD]Employee 1 <o></o>
[/TD]
[TD]Answer<o></o>
[/TD]
[TD]Employee 2<o></o>
[/TD]
[TD]Answer<o></o>
[/TD]
[/TR]
[TR]
[TD]Requirement 1<o></o>
[/TD]
[TD]0.08<o></o>
[/TD]
[TD]0.045
[/TD]
[TD]N/A<o></o>
[/TD]
[TD]N/A<o></o>
[/TD]
[/TR]
[TR]
[TD]Requirement 2<o></o>
[/TD]
[TD]0.08<o></o>
[/TD]
[TD]0.118
[/TD]
[TD]0.05<o></o>
[/TD]
[TD]0.025<o></o>
[/TD]
[/TR]
[TR]
[TD]Requirement 3<o></o>
[/TD]
[TD]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.1<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.05<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Requirement 4<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.24<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.255
[/TD]
[TD="width: 104, bgcolor: transparent"]0.05<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.125<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Requirement 5<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.6<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.582
[/TD]
[TD="width: 104, bgcolor: transparent"]0.27<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.2<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Requirement 6<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.53<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.6<o></o>
[/TD]
[/TR]
</tbody>[/TABLE]
<o></o>
Explanation of my answer for Employee 1: <o></o>
<o></o>
Employee 1 is assigned to Job 1, Job 2 and Job 3 – there are four High Requirements and five Low Requirements. As there is no weighting for the Duration of each job, each High Requirement is worth 0.2 (80% / 4) and Low Requirement is worth 0.04 (20% / 5). Applying that to Requirement 1 we can see that both Job 1 and Job 2 have a Low need for this requirement, which means theanswer is 0.08 (0.04 x 2).
<o></o>
Explanation for correct answer for Employee 1: <o></o>
<o></o>
The table below converts the High/Low allocation in Input Sheet 2 (listed above) into numerical values (the High Requirements are bolded): <o></o>
<o></o>
[TABLE="class: MsoNormalTable"]
<tbody>[TR]
[TD][/TD]
[TD]Job 1 <o></o>
[/TD]
[TD]Job 2<o></o>
[/TD]
[TD]Job 3<o></o>
[/TD]
[/TR]
[TR]
[TD]Duration (Days)<o></o>
[/TD]
[TD]0.5<o></o>
[/TD]
[TD]2<o></o>
[/TD]
[TD]3<o></o>
[/TD]
[/TR]
[TR]
[TD]<o></o>
[/TD]
[TD]<o></o>
[/TD]
[TD]<o></o>
[/TD]
[TD]<o></o>
[/TD]
[/TR]
[TR]
[TD]Requirement 1<o></o>
[/TD]
[TD]0.05<o></o>
[/TD]
[TD]0.2<o></o>
[/TD]
[TD]N/A<o></o>
[/TD]
[/TR]
[TR]
[TD]Requirement 2<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.05<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.6 <o></o>
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Requirement 3<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Requirement 4<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.2 <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]1.2 <o></o>
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Requirement 5<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.4 <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]1.6 <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]1.2 <o></o>
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"]Requirement 6<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[/TR]
</tbody>[/TABLE]
<o></o>
High Requirements total 4.4 days and Low Requirements total 1.1 days. <o></o>
<o></o>
Answer:
[TABLE="class: MsoNormalTable"]
<tbody>[TR]
[TD][/TD]
[TD]Employee 1 <o></o>
[/TD]
[/TR]
[TR]
[TD]Requirement 1<o></o>
[/TD]
[TD](20%*(0.05/1.1)) + (20%*(0.2/1.1)) = 0.045<o></o>
[/TD]
[/TR]
[TR]
[TD]Requirement 2<o></o>
[/TD]
[TD](20%*(0.05/1.1)) + (20%*(0.6/1.1)) = 0.118<o></o>
[/TD]
[/TR]
[TR]
[TD]Requirement 3<o></o>
[/TD]
[TD]N/A<o></o>
[/TD]
[/TR]
[TR]
[TD]Requirement 4<o></o>
[/TD]
[TD](80%*(1.2/4.4)) + (20%*(0.2/1.1)) = 0.255<o></o>
[/TD]
[/TR]
[TR]
[TD]Requirement 5<o></o>
[/TD]
[TD](80%*(0.4/4.4)) + (80%*(1.4/4.4)) + (80%*(1.6/4.4)) = 0.582<o></o>
[/TD]
[/TR]
[TR]
[TD]Requirement 6<o></o>
[/TD]
[TD]N/A<o></o>
[/TD]
[/TR]
</tbody>[/TABLE]

I hope that is clear - I can provide the answers for Employees 3 - 5 if needed.

I can also provide my formula (very inefficient and not very good) which does the calculation except for the Duration allowance (undoubtedly the hardest part).

Thanks
Patrick
<o></o>

 

[Patrick020]

<o></o>
Hi everyone <o></o>
<o></o>
Conscious that description may have been too word-heavy.
<o></o>
Effectively I would like to split an employee’s time across Requirements – these Requirements are determined by Jobs they are assigned. <o></o>
<o></o>
When splitting an employee’s time there is an assumption that 80% goes to Requirements marked High and 20% goes to Requirements marked Low. <o></o>
<o></o>
I have a formula currently working with no allowance for the Duration each job takes.

What I would like to do is build a weighting for each job so that if Job 7 takes twice as long as Job 8, then the High Requirements in Job 7 will require twice as much time as those in Job 8.

Example data: <o></o>
<o></o>
The bolded is the correct answer I would like to calculate. An explanation is provided at the bottom of the post above.

[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD][/TD]
[TD]A<o></o>
[/TD]
[TD]B<o></o>
[/TD]
[TD]C<o></o>
[/TD]
[TD]D<o></o>
[/TD]
[TD]E<o></o>
[/TD]
[TD]F<o></o>
[/TD]
[TD]G<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]1<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]Employee 1 <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Employee 2<o></o>
[/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: 95, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]2<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]Job 1 <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Job 4<o></o>
[/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: 95, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]3<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]Job 2<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Job 5<o></o>
[/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: 95, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]4<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]Job 3<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Job 6<o></o>
[/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: 95, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]5<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/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: 95, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]6<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/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: 95, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]7<o></o>
[/TD]
[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: 95, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]8<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"]Job 1 <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Job 2<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Job 3<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Job 4<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Job 5<o></o>
[/TD]
[TD="width: 95, bgcolor: transparent"]Job 6<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]9<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]Duration <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.5<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]2<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]3<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]5<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]1<o></o>
[/TD]
[TD="width: 95, bgcolor: transparent"]2<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]10<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]Requirement 1<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Low <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Low<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 95, bgcolor: transparent"]N/A<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]11<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]Requirement 2<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Low <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Low <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 95, bgcolor: transparent"]Low<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]12<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]Requirement 3<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Low <o></o>
[/TD]
[TD="width: 95, bgcolor: transparent"]Low <o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]13<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]Requirement 4<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Low <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]High <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Low <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 95, bgcolor: transparent"]N/A<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]14<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]Requirement 5<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]High <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]High <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]High<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 95, bgcolor: transparent"]High<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]15<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]Requirement 6<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]High<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]High<o></o>
[/TD]
[TD="width: 95, bgcolor: transparent"]N/A<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]16<o></o>
[/TD]
[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: 95, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]17<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"]Employee 1 <o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]Employee 2<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 95, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]18<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]Requirement 1<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.045<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 95, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]19<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]Requirement 2<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.118<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.025<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"]High<o></o>
[/TD]
[TD="width: 95, bgcolor: transparent"]Low<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]20<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]Requirement 3<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.05<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"]80%<o></o>
[/TD]
[TD="width: 95, bgcolor: transparent"]20%<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]21<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]Requirement 4<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.255<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.125<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 95, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]22<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]Requirement 5<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.582<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.2<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 95, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]23<o></o>
[/TD]
[TD="width: 117, bgcolor: transparent"]Requirement 6<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"]0.6<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 95, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
<o></o>

 

[Eric W]

Yeah, the calculations here are a bit of a mess! I gave up trying to follow the logic of the math, and just tried to duplicate your calculations. I managed to duplicate your original answer (column R) in column T.

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
Employee 1	Employee 2	Employee 3	Employee 4	Job 1	Job 2	Job 3	Job 4	Job 5	Job 6	High	Employee 1	Answer	Employee 2	Answer
Job 1	Job 4	Job 1	Job 1	Duration (Days)	Low	Requirement 1	N/A	N/A
Job 2	Job 5	Job 2	Job 2	Requirement 2
Job 3	Job 6	N/A	Job 3	Requirement 1	Low	Low	N/A	N/A	N/A	N/A	Requirement 3	N/A	N/A
N/A	N/A	N/A	Job 4	Requirement 2	Low	N/A	Low	N/A	N/A	Low	Requirement 4
N/A	N/A	N/A	Job 5	Requirement 3	N/A	N/A	N/A	N/A	Low	Low	Requirement 5
Requirement 4	N/A	Low	High	Low	N/A	N/A	Requirement 6	N/A	N/A
Requirement 5	High	High	High	N/A	N/A	High
Requirement 6	N/A	N/A	N/A	High	High	N/A

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]80%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"]0.5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]20%[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.08[/TD]
[TD="align: right"]0.045[/TD]
[TD="align: right"]0.08[/TD]
[TD="align: right"]0.0625[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.08[/TD]
[TD="align: right"]0.118[/TD]
[TD="align: right"]0.08[/TD]
[TD="align: right"]0.0875[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.025[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0.05[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.24[/TD]
[TD="align: right"]0.255[/TD]
[TD="align: right"]0.24[/TD]
[TD="align: right"]0.332353[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.125[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.6[/TD]
[TD="align: right"]0.582[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]0.517647[/TD]
[TD="align: right"]0.27[/TD]
[TD="align: right"]0.2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.53[/TD]
[TD="align: right"]0.6[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]T2[/TH]
[TD="align: left"]=$O$2/SUMPRODUCT(($G$4:$L$9="Low")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0)))*SUMPRODUCT(($G$4:$L$9="Low")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0))*($F$4:$F$9=$Q2))+$O$1/SUMPRODUCT(($G$4:$L$9="High")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0)))*SUMPRODUCT(($G$4:$L$9="High")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0))*($F$4:$F$9=$Q2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]U2[/TH]
[TD="align: left"]=$O$2/SUMPRODUCT(($G$4:$L$9="Low")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0))*($G$2:$L$2))*SUMPRODUCT(($G$4:$L$9="Low")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0))*($F$4:$F$9=$Q2)*($G$2:$L$2))+$O$1/SUMPRODUCT(($G$4:$L$9="High")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0))*($G$2:$L$2))*SUMPRODUCT(($G$4:$L$9="High")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0))*($F$4:$F$9=$Q2)*($G$2:$L$2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



However, I ran into some difficulties when I tried to create your "correct" answer. Part of the problem is that I believe that you made some errors in your calculation. For example, on your High/Low converted table, you have Requirement 5, Job 3 = 1.2 Shouldn't it be 2.4? The formula in U is what I came up with for the "correct" answer, but I can't really validate it until you verify your calculations. Let me know.

 

[Patrick020]

Hi Eric <o></o>
<o></o>
Thanks for looking at this. The first formula is already a big upgrade due to its efficiency so thank you. <o></o>
<o></o>
I believe the math is correct. <o></o>
<o></o>
Firstly I have added an explanation to the High / Low converted table to explain Requirement 5 for Job 3 (I have bolded the Low Requirements to assist in the below explanation): <o></o>
<o></o>
[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD][/TD]
[TD="width: 193, bgcolor: transparent"]Job 1 <o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]Job 2<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]Job 3<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 152, bgcolor: transparent"]Duration <o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]0.5<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]2<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]3<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 152, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 152, bgcolor: transparent"]Requirement 1<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]0.05 = (20%*0.5/2) <o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]0.2 = (20%*2/2)<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]N/A<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 152, bgcolor: transparent"]Requirement 2<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]0.05 = (20%*0.5/2)<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]0.6 = (20%*3)<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 152, bgcolor: transparent"]Requirement 3<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]N/A<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 152, bgcolor: transparent"]Requirement 4<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]0.2 = (20%*2/2)<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]1.2 = (80%*3/2)<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 152, bgcolor: transparent"]Requirement 5<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]0.40 = (80%*0.5)<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]1.6 = (80%*2)<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]1.2 = (80%*3/2)<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 152, bgcolor: transparent"]Requirement 6<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]N/A<o></o>
[/TD]
[TD="width: 193, bgcolor: transparent"]N/A<o></o>
[/TD]
[/TR]
</tbody>[/TABLE]
<o></o>
In terms of the actual calculation for ease we can look at the allocation of Low Requirements only. The column on the left is my manual calculation and the right is based on the formula provided in U2: <o></o>
<o></o>
[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD][/TD]
[TD="width: 198, bgcolor: transparent"]Employee 1 (My calculation)<o></o>
[/TD]
[TD="width: 227, bgcolor: transparent"]Employee 1 (Formula)<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"]Requirement 1<o></o>
[/TD]
[TD="width: 198, bgcolor: transparent"]0.045<o></o>
[/TD]
[TD="width: 227, bgcolor: transparent"]0.0625<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"]Requirement 2<o></o>
[/TD]
[TD="width: 198, bgcolor: transparent"]0.118<o></o>
[/TD]
[TD="width: 227, bgcolor: transparent"]0.0875<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"]Requirement 3<o></o>
[/TD]
[TD="width: 198, bgcolor: transparent"][/TD]
[TD="width: 227, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"]Requirement 4<o></o>
[/TD]
[TD="width: 198, bgcolor: transparent"]0.036<o></o>
[/TD]
[TD="width: 227, bgcolor: transparent"]0.05<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"]Requirement 5<o></o>
[/TD]
[TD="width: 198, bgcolor: transparent"][/TD]
[TD="width: 227, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"]Requirement 6<o></o>
[/TD]
[TD="width: 198, bgcolor: transparent"][/TD]
[TD="width: 227, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
</tbody>[/TABLE]
<o></o>
Explanation of my Calculation: <o></o>
<o></o>
Employee 1 is spending 1.1 days on Low Requirements (add the bolded in the conversion table). We need to convert that 1.1 days into 0.2 days(i.e. the 20%). <o></o>
<o></o>
When we convert the 1.1 days into 0.2 days we need to ensure the proportions do not change. <o></o>
<o></o>
What we therefore need to do is identify the percentage of time spent on each requirement in comparison to the total – then use this same ratio and apply it to the 20%. Explanation of my answer: <o></o>
<o></o>
[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD][/TD]
[TD="width: 444, bgcolor: transparent"]Employee 1 (My calculation)<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"]Requirement 1<o></o>
[/TD]
[TD="width: 444, bgcolor: transparent"](0.05/1.1 = 4.5%) + (0.2/1.1=18.2%) = 22.7% <o></o>
22.7% * 0.2 = 0.045<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"]Requirement 2<o></o>
[/TD]
[TD="width: 444, bgcolor: transparent"](0.05/1.1 = 4.5%) + (0.6/1.1=54.5%) = 59%<o></o>
59% * 0.2 = 0.118<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"]Requirement 3<o></o>
[/TD]
[TD="width: 444, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"]Requirement 4<o></o>
[/TD]
[TD="width: 444, bgcolor: transparent"](0.2/1.1 = 18.2%) <o></o>
18.2% * 0.2 = 0.0364 <o></o>
[/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"]Requirement 5<o></o>
[/TD]
[TD="width: 444, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"]Requirement 6<o></o>
[/TD]
[TD="width: 444, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
<o></o>
Hopefully that makes the logic clearer – if you think thereis a mistake in the math please let me know (definitely would not be the first time!). <o></o>
<o></o>
Thanks <o></o>
Patrick <o></o>

 

[Eric W]

Something weird has happened to my HTML Maker, so I can't display my spreadsheet right now. But given the layout I had in Post # 3, put this formula in U2 with Control+Shift+Enter:

=$O$2/SUMPRODUCT(($G$4:$L$9="Low")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0))*($G$2:$L$2)/MMULT(COLUMN($G$1:$L$1)^0,($G$4:$L$9="Low")+0))*SUMPRODUCT(($G$4:$L$9="Low")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0))*($F$4:$F$9=$Q2)*($G$2:$L$2)/MMULT(COLUMN($G$1:$L$1)^0,($G$4:$L$9="Low")+0))+$O$1/SUMPRODUCT(($G$4:$L$9="High")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0))*($G$2:$L$2)/MMULT(COLUMN($G$1:$L$1)^0,($G$4:$L$9="High")+0))*SUMPRODUCT(($G$4:$L$9="High")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0))*($F$4:$F$9=$Q2)*($G$2:$L$2)/MMULT(COLUMN($G$1:$L$1)^0,($G$4:$L$9="High")+0))

Simple, right? Drag down as needed. This matches your desired totals.

We can simplify it a little if you are willing to use some helper rows. In G11 put: =COUNTIF(G$4:G$9,"Low") and in G12 put: =COUNTIF(G$4:G$9,"High") and drag those right to column L. Then this formula works in U2:

=$O$2/SUMPRODUCT(($G$4:$L$9="Low")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0))*$G$2:$L$2/$G$11:$L$11)*SUMPRODUCT(($G$4:$L$9="Low")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0))*($F$4:$F$9=$Q2)*$G$2:$L$2/$G$11:$L$11)+$O$1/SUMPRODUCT(($G$4:$L$9="High")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0))*$G$2:$L$2/$G$12:$L$12)*SUMPRODUCT(($G$4:$L$9="High")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0))*($F$4:$F$9=$Q2)*$G$2:$L$2/$G$12:$L$12)

With additional helper cells/rows/columns it may be possible to simplify it even more, but I'll have to think about it some more.

 

[Patrick020]

With additional helper cells/rows/columns it may be possible to simplify it even more, but I'll have to think about it some more.

Hi Eric

Not too sure what has gone wrong here as the formula worked absolutely perfectly on a smaller data set - when I transferred it to our main file (90 employees, 300 jobs, 110 requirements!) it would only give a #DIV/0! Error. Given nothing has changed with the structure of the data I'm partially thinking it's due to how many calculations are involved in the larger arrays.

The structure of the excel can be changed so more than happy to add as many helper rows as required - I think even if I can get rid of the error the file will struggle to handle the complexity of this calculation.

 

[Eric W]

I knew there was one situation that would cause a #DIV/0! error, but I didn't think it would happen, so I didn't bring it up. If one or more of your jobs has ONLY Low requirements or ONLY High requirements, we'd get the #DIV/0!. To fix that with the formula with the helper rows, change the G11 helper formulas to

G11: =IFERROR(G$2/COUNTIF(G$4:G$9,"Low"),1)
G12: =IFERROR(G$2/COUNTIF(G$4:G$9,"High"),1)

and the main formula to:

U2:

=$O$2/SUMPRODUCT(($G$4:$L$9="Low")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0))*[COLOR=#ff0000]$G$11:$L$11[/COLOR])*SUMPRODUCT(($G$4:$L$9="Low")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0))*($F$4:$F$9=$Q2)*[COLOR=#ff0000]$G$11:$L$11[/COLOR])+$O$1/SUMPRODUCT(($G$4:$L$9="High")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0))*[COLOR=#ff0000]$G$12:$L$12[/COLOR])*SUMPRODUCT(($G$4:$L$9="High")*ISNUMBER(MATCH($G$1:$L$1,INDEX($A$2:$D$6,0,MATCH(R$1,$A$1:$D$1,0)),0))*($F$4:$F$9=$Q2)*[COLOR=#ff0000]$G$12:$L$12[/COLOR])

Or you can leave the main formula alone and just change the helper formulas to:

G11: =IFERROR(1/(1/COUNTIF(G$4:G$9,"Low")),G$2)
G12: =IFERROR(1/(1/COUNTIF(G$4:G$9,"High")),G$2)



That should solve the #DIV/0! error, but the performance will still probably be bad, there's just too much calculating going on. I'll think about whether some additional helpers would improve performance. Would you consider an on-demand macro to perform the calculations?

 

[Patrick020]

I knew there was one situation that would cause a #DIV/0! error, but I didn't think it would happen, so I didn't bring it up. If one or more of your jobs has ONLY Low requirements or ONLY High requirements, we'd get the #DIV/0!.

That should solve the #DIV/0! error, but the performance will still probably be bad, there's just too much calculating going on. I'll think about whether some additional helpers would improve performance. Would you consider an on-demand macro to perform the calculations?

Thank you for this - I have done a few manual calculations and the math is working perfectly. Bravo!

Funnily enough when I used a prior iteration of this formula (before you integrated the difficult duration aspect) I ran into the same #DIV/0! problem and therefore implemented a rule that each Job must have both high and low requirements. The reason the #DIV/0! came up this time is that across the array in your formula I have a few summary columns which basically sum the requirements for various functions (to which Jobs are aligned). Those columns therefore have units as opposed to "High" or "Low".

Performance is actually not too bad. When you assign employees to different jobs it does take around 5-10 seconds to recalculate (which isn't ideal) but it's in no way a significant problem. The insight this is giving is really valuable so thank you very much for looking at it.

 

[Eric W]

Whew! That took a fair amount of analysis, but I'm glad it works for you!