PhantomJoe
New Member
- Joined
- Sep 7, 2017
- Messages
- 22
I'm not sure why this is stumping me but I've been struggling to find the right solution to solve for the following.
I have a table of data that is used to determine cancellation percentages based on a number of factors.
The first factor is the "Position Assignment" (found in column U) and values in this column can be one of 8 values like "Direct Hire Business" and "Direct Hire Technical".
The next check is against the number of "Days Open" (column R) for the requisition. Depending on the specific Position Assignment the ranges will vary. Here's an example:
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Position Assignment[/TD]
[TD]Days Open[/TD]
[TD]% of Fee[/TD]
[TD]Days Open[/TD]
[TD]% of Fee[/TD]
[TD]Days Open[/TD]
[TD]% of Fee[/TD]
[/TR]
[TR]
[TD]Direct Hire Technical[/TD]
[TD]<4 Days[/TD]
[TD]0%[/TD]
[TD]5-14 Days[/TD]
[TD]50%[/TD]
[TD]15+ Days[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]FCG/LP[/TD]
[TD]<30 Days[/TD]
[TD]0%[/TD]
[TD]31-44 Days[/TD]
[TD]25%[/TD]
[TD]45+ Days[/TD]
[TD]100%[/TD]
[/TR]
</tbody>[/TABLE]
<table border="0" cellpadding="0" cellspacing="0" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;"="" width=""><colgroup><col></colgroup><tbody></tbody></table>
So the above table contains the logic Cancellation Fees based on Position Assignments and if it's closed within the X days then the % of Fee will be assigned based on how long the Req was open. So if it was a Direct Hire Technical and open for 9 days then the % of Fee would be 50%.
The formula I think I'm looking for would first do a lookup based on the Position Assignment and then check the Days Open column to see what range it falls in then assign the % of Fee based on those 2 factors.
Is this doable with an if/vlookup formula?
I have a table of data that is used to determine cancellation percentages based on a number of factors.
The first factor is the "Position Assignment" (found in column U) and values in this column can be one of 8 values like "Direct Hire Business" and "Direct Hire Technical".
The next check is against the number of "Days Open" (column R) for the requisition. Depending on the specific Position Assignment the ranges will vary. Here's an example:
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Position Assignment[/TD]
[TD]Days Open[/TD]
[TD]% of Fee[/TD]
[TD]Days Open[/TD]
[TD]% of Fee[/TD]
[TD]Days Open[/TD]
[TD]% of Fee[/TD]
[/TR]
[TR]
[TD]Direct Hire Technical[/TD]
[TD]<4 Days[/TD]
[TD]0%[/TD]
[TD]5-14 Days[/TD]
[TD]50%[/TD]
[TD]15+ Days[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]FCG/LP[/TD]
[TD]<30 Days[/TD]
[TD]0%[/TD]
[TD]31-44 Days[/TD]
[TD]25%[/TD]
[TD]45+ Days[/TD]
[TD]100%[/TD]
[/TR]
</tbody>[/TABLE]
<table border="0" cellpadding="0" cellspacing="0" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;"="" width=""><colgroup><col></colgroup><tbody></tbody></table>
So the above table contains the logic Cancellation Fees based on Position Assignments and if it's closed within the X days then the % of Fee will be assigned based on how long the Req was open. So if it was a Direct Hire Technical and open for 9 days then the % of Fee would be 50%.
The formula I think I'm looking for would first do a lookup based on the Position Assignment and then check the Days Open column to see what range it falls in then assign the % of Fee based on those 2 factors.
Is this doable with an if/vlookup formula?
