I am working on a sheet that returns a value if a certain number of criteria have been met. I can't upload a spreadsheet at work but hopefully I can explain the situation accordingly in plain english.
All are cell 5 through cell 99.
Column B = Employee Name - there are names repeated if the employee is supporting tasks for multiple companies
Column C = Company
Column E = Task end date
Column F = Remaining hours to charge
Column G = Level of effort - LOE (some employees may spend 20% of their time for Company A, 30% for company B, etc...)
Column H - Rank (This column ranks the lines based upon when the end date occurs on each task by employee. The closest end date is ranked 1, second closest 2, etc...)
The purpose of the formula is to look at each employee name & company name, and return the LOE % for the earliest finish date for each employee by company. So if an employee has two finish dates for two companies that occur before the other companies I want it to look up the employee LOE% from the LOE tab and return the value that corresponds to each employee and company. Basically I need to be able to determine which companies each employee needs to support first and ignore the ones with a finish date down the road.
As an example:
---------A--------------B--------------E-----------G------------H
----Employee-----Company-----End date-----LOE %------Rank
5-----Dan-----------Micro Corp----4/25/17------50%---------1
6-----Dan-----------Mid Corp------6/15/17------50%---------3
7-----Dan-----------Macro Corp---4/25/17------50%---------2
8-----Dan-----------Over Corp----7/1/17--------50%---------4
Since Micro Corp and Macro Corp end first, Dan should be devoting his time 50/50 between the two companies and ignoring Mid Corp and Over Corp until the first two tasks are complete. So I want column G to return a blank value until he finishes his first two assignments.
My formula right now is:
{=IF($A$5:$A$99=A5,IF($B$5:$B$99=B5,IF(H5=1,INDEX(LOE!D:D,MATCH(A5&B5,LOE!$B$2:$B$200&LOE!$C$2:$C$200,0)),"")))))}
I am returning a result of 100% for column G5, and I assume it is because one of my criteria is that H5=1.
I am not sure if this is the correct formula to use as I really need the rank formula to not only rank by finish date, but also by company as well. So even if Macro Corp is ranked #2 by date, that is the earliest job due to Macro Corp, so it should also be ranked #1 since the employee has to work both.
I hope this isn't too confusing to understand but I would really appreciate the help. I think I may be overcomplicating this.
Here is my rank formula (thanks to another forum member!):
{=RANK.EQ(E5,INDEX($E$5:$E$200,SMALL(IF($B$5:$B$200=B5,ROW($E$5:$E$200)-ROW($E$5)+1),1)):INDEX($E$5:$E$200,LARGE(IF($B$5:$B$200=B5,ROW($E$5:$E$200)-ROW($E$5)+1),1)),1)}
All are cell 5 through cell 99.
Column B = Employee Name - there are names repeated if the employee is supporting tasks for multiple companies
Column C = Company
Column E = Task end date
Column F = Remaining hours to charge
Column G = Level of effort - LOE (some employees may spend 20% of their time for Company A, 30% for company B, etc...)
Column H - Rank (This column ranks the lines based upon when the end date occurs on each task by employee. The closest end date is ranked 1, second closest 2, etc...)
The purpose of the formula is to look at each employee name & company name, and return the LOE % for the earliest finish date for each employee by company. So if an employee has two finish dates for two companies that occur before the other companies I want it to look up the employee LOE% from the LOE tab and return the value that corresponds to each employee and company. Basically I need to be able to determine which companies each employee needs to support first and ignore the ones with a finish date down the road.
As an example:
---------A--------------B--------------E-----------G------------H
----Employee-----Company-----End date-----LOE %------Rank
5-----Dan-----------Micro Corp----4/25/17------50%---------1
6-----Dan-----------Mid Corp------6/15/17------50%---------3
7-----Dan-----------Macro Corp---4/25/17------50%---------2
8-----Dan-----------Over Corp----7/1/17--------50%---------4
Since Micro Corp and Macro Corp end first, Dan should be devoting his time 50/50 between the two companies and ignoring Mid Corp and Over Corp until the first two tasks are complete. So I want column G to return a blank value until he finishes his first two assignments.
My formula right now is:
{=IF($A$5:$A$99=A5,IF($B$5:$B$99=B5,IF(H5=1,INDEX(LOE!D:D,MATCH(A5&B5,LOE!$B$2:$B$200&LOE!$C$2:$C$200,0)),"")))))}
I am returning a result of 100% for column G5, and I assume it is because one of my criteria is that H5=1.
I am not sure if this is the correct formula to use as I really need the rank formula to not only rank by finish date, but also by company as well. So even if Macro Corp is ranked #2 by date, that is the earliest job due to Macro Corp, so it should also be ranked #1 since the employee has to work both.
I hope this isn't too confusing to understand but I would really appreciate the help. I think I may be overcomplicating this.
Here is my rank formula (thanks to another forum member!):
{=RANK.EQ(E5,INDEX($E$5:$E$200,SMALL(IF($B$5:$B$200=B5,ROW($E$5:$E$200)-ROW($E$5)+1),1)):INDEX($E$5:$E$200,LARGE(IF($B$5:$B$200=B5,ROW($E$5:$E$200)-ROW($E$5)+1),1)),1)}
