Need a formula to determine priority based on rank

[Nlambert]

Hello all,

New to the forum (lurking for a while). I have a spreadsheet that I could really use some help with. I'll try to explain in plain English and then attach a sample file that I am working with.

I have a data file from which data must be extracted to help prioritize and manage employee time to ensure they are completing their tasks based upon the earliest task end date. Each employee supports multiple companies at the same time, and as such their level of effort is defined based upon how their time must be split up. Once each task is complete and the hours have been spent the tasks are removed from the sheet, then the employee moves to the next task on the list.

My spreadsheet uses a rank formula to rank each employee's tasks based upon the end date.... On a separate tab there is an LOE reference list that defines what percent of time each employee is expected to spend on each customer. I am trying to determine a formula that will look up the LOE from the LOE reference tab and only populate a value in the LOE column on my worksheet if the employee should be working on that task right now. If the employee's LOE for the first task is not 100%, I need it to find the next task in line and populate the LOE for that tab so that they are 100% tasked at all times.

Here is a quick example of what I need:

David supports Company A,B,C, and D. Company D has a task ending on 6/15/17 which is the soonest end date therefore the rank is 1. David's expected level of effort for the company is 50%. Since David is not 100% allocated, I need a formula that will see that 50% of his time is still unallocated, look at the next lowest ranking number, and also assign the LOE percent to that column as well. If the formula sees that David is at 100% allocation, it should not tell him to start another project.

I haven't been able to figure out a formula that will take care of all of this so I am hoping that some of the excel experts can help me solve the problem. I have attached my example sheet in hopes that this will make sense. I appreciate all of the help in advance!

[CAntosh]

What should happen with tied ranks? You can build a hidden tiebreaker into your ranking formula by using the following (array-entered) in E2, filled down:

=RANK.EQ(D2,INDEX($D$2:$D$11,SMALL(IF($A$2:$A$11=A2,ROW($D$2:$D$11)-ROW($D$2)+1),1)):INDEX($D$2:$D$11,LARGE(IF($A$2:$A$11=A2,ROW($D$2:$D$11)-ROW($D$2)+1),1)),1)+0.0000001*ROW()

Then the following (array-entered) in F2, filled down, should do the trick:

=MAX(0,MIN(INDEX(LOE!$C$2:$C$8,MATCH(1,(LOE!$A$2:$A$8=$A2)*(LOE!$B$2:$B$8=$B2),0)),1-SUMIFS($F$2:$F$11,$A$2:$A$11,$A2,$E$2:$E$11,"<"&$E2)))

Will that work, or do you want to try to split the effort if there's a tie?

[Nlambert]

I'm ok with a tie, because that means all of those tasks share priority. It would indicate that the person should be working those first.

My thinking is that if Company A & B both share a rank of 1 because their due dates are the same, the employee should be splitting their efforts between them. Then the LOE column should return a value showing what percent of time they should be spending on each one based upon the ranking. (Hope that makes sense)

I tried these formulas in my example sheet, and they seem to work very well. However, the Rank formula works in the real sheet, but the LOE formula is giving me some errors.

For example, employee John has 7 total tasks. 3 tasks for Company A, 3 tasks for Company B, and 1 task for Company C.

John's level of effort for each task is:

Company A - 40%
Company B - 45%
Company C - 10%

Company A dates and ranking:

6/30/17 - 1
8/30/17 - 3
11/6/17 - 4

Company B:
8/17/17 - 2
11/9/17 - 5
11/9/17 - 5

Company C:
11/9/17 - 5

What it seems like the LOE results should return are:

Employee ---- Company ---- End Date ---- Rank ---- LOE
John-------------A---------6/30/17---------1------- 40%
John-------------B---------8/17/17---------2------- 45%
John-------------A---------8/30/17---------3------- 40% (yes, he is over allocated, which is what I am trying to see to resolve)
John-------------A---------11/6/17---------4-------
John-------------B---------11/9/17---------5-------
John-------------B---------11/9/17---------6-------
John-------------C---------11/9/17---------7-------

What it actually shows is:

Employee ---- Company ---- End Date ---- Rank ---- LOE
John-------------A---------6/30/17---------1------- #N/A
John-------------B---------8/17/17---------2------- #N/A
John-------------A---------8/30/17---------3------- 50%
John-------------A---------11/6/17---------4------- 50%
John-------------B---------11/9/17---------5------- #N/A
John-------------B---------11/9/17---------6------- 100%
John-------------C---------11/9/17---------7------- #N/A


Thanks for the help thus far! This is definitely some progress!

[CAntosh]

We can alter the tie handling to split between the tied ranks by using a COUNTIFS denominator. The attached sample has been extended to include John's information. I changed the RANK formula back to your original formula, removing my tie-breaker, so in E2, array-entered, is:

=RANK.EQ(D2,INDEX($D$2:$D$18,SMALL(IF($A$2:$A$18=A2,ROW($D$2:$D$18)-ROW($D$2)+1),1)):INDEX($D$2:$D$18,LARGE(IF($A$2:$A$18=A2,ROW($D$2:$D$18)-ROW($D$2)+1),1)),1)

Then for the effort, in F2, array-entered, is:

=MAX(0,MIN(INDEX(LOE!$C$2:$C$11,MATCH(1,(LOE!$A$2:$A$11=$A2)*(LOE!$B$2:$B$11=$B2),0)),1-SUMIFS($F$2:$F$18,$A$2:$A$18,$A2,$E$2:$E$18,"<"&$E2)))/COUNTIFS($A$2:$A$18,$A2,$E$2:$E$18,$E2)

I believe this produces the results you're after. If it works in the sample but not your actual workbook, then start by double checking all of the ranges to make sure they've been translated correctly. If you're still getting incorrect results, then try posting a sample that's a better representation of how your real data is arranged so we can try to diagnose where the translation is failing.

[Nlambert]

We can alter the tie handling to split between the tied ranks by using a COUNTIFS denominator. The attached sample has been extended to include John's information. I changed the RANK formula back to your original formula, removing my tie-breaker, so in E2, array-entered, is:

=RANK.EQ(D2,INDEX($D$2:$D$18,SMALL(IF($A$2:$A$18=A2,ROW($D$2:$D$18)-ROW($D$2)+1),1)):INDEX($D$2:$D$18,LARGE(IF($A$2:$A$18=A2,ROW($D$2:$D$18)-ROW($D$2)+1),1)),1)

Then for the effort, in F2, array-entered, is:

=MAX(0,MIN(INDEX(LOE!$C$2:$C$11,MATCH(1,(LOE!$A$2:$A$11=$A2)*(LOE!$B$2:$B$11=$B2),0)),1-SUMIFS($F$2:$F$18,$A$2:$A$18,$A2,$E$2:$E$18,"<"&$E2)))/COUNTIFS($A$2:$A$18,$A2,$E$2:$E$18,$E2)

I believe this produces the results you're after. If it works in the sample but not your actual workbook, then start by double checking all of the ranges to make sure they've been translated correctly. If you're still getting incorrect results, then try posting a sample that's a better representation of how your real data is arranged so we can try to diagnose where the translation is failing.

CAntosh, this worked perfectly! Thanks to all that helped me solve this issue!

[CAntosh]

Glad to help, good luck!

[MANSIJAIN]

Hello All,

I have not exact but a similar problem. Let me try to explain taking an example - There are 5 vacant positions at location A for some X job, 4 vacant position at location B and 3 vacant position at location C. There are 12 candidates for these positions with Rank 1 to 12. Each of 12 candidates has filled preference of working on these locations. However, individual with Rank 1 (lower Rank) would be given preference in order to assign the locations (i.e., if there's only 1 position left at location A and individual of both Rank 1 and Rank 2 filled it as their first preference, then individual with Rank 1 will be priortized and get the position at location A.)

I have attached an excel with sample input and expected result.

I couldn't figure out the excel formula that will do this. It will be great to get your thoughts and help on excel formula with this.

Many thanks in advance!

[shukla.ankur281190]

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[MANSIJAIN]

Thank you Ankur for the suggestion. I will create a new thread.

Thanks again!