Hello and thank you in advance for any help or guidance you can provide.
I have a situation where I need to determine whether a business qualifies for a sales program, and if they do qualify, what Tier and Sub-Tier do they fall in. There are several criteria to take into consideration. Rather than write out long nested If/Then/AND/OR statements, I was hoping to set up the qualification requirements in a table for easier management and updating. I have tried creating a table and using Index/Match with multiple criteria, but I was having trouble with the AND/ OR logic within it. I also tried breaking down the logic into chunks based on each qualifier, and then building a reference id that could be used in a look up table, but I had the same issues with the AND/OR Logic.
In my attached sample, the top portion tries to lay out the current qualification logic in plain English. The Output section takes the Input parameters and uses them in a standard nested if statement, to compare to the qualifiers in the table. It works and can return the Tier and Sub-Tier, but I was hoping for a more dynamic solution. I’d like to basically have the logic outlined in a table, and then use the inputs to lookup which Tier and Sub-Tier the Business would fall in to. The last section illustrates what I foresee could be updated qualifications.
Cross Posted@
ExcelForum
Chandoo
I have a situation where I need to determine whether a business qualifies for a sales program, and if they do qualify, what Tier and Sub-Tier do they fall in. There are several criteria to take into consideration. Rather than write out long nested If/Then/AND/OR statements, I was hoping to set up the qualification requirements in a table for easier management and updating. I have tried creating a table and using Index/Match with multiple criteria, but I was having trouble with the AND/ OR logic within it. I also tried breaking down the logic into chunks based on each qualifier, and then building a reference id that could be used in a look up table, but I had the same issues with the AND/OR Logic.
In my attached sample, the top portion tries to lay out the current qualification logic in plain English. The Output section takes the Input parameters and uses them in a standard nested if statement, to compare to the qualifiers in the table. It works and can return the Tier and Sub-Tier, but I was hoping for a more dynamic solution. I’d like to basically have the logic outlined in a table, and then use the inputs to lookup which Tier and Sub-Tier the Business would fall in to. The last section illustrates what I foresee could be updated qualifications.
Cross Posted@
ExcelForum
Chandoo
| Qualification Test.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | ||||||||||||
| 2 | Tier 1 - Must have at least $1m in current business | |||||||||||
| 3 | -AND | |||||||||||
| 4 | --1. - At least 10 new client sales worth at least $250,000 in new sales | |||||||||||
| 5 | --OR | |||||||||||
| 6 | --2. - At least $500,000 in new sales | |||||||||||
| 7 | ||||||||||||
| 8 | Tier 2 - Must have less than $1m in current business | |||||||||||
| 9 | -AND | |||||||||||
| 10 | --1. - At least 10 new client sales worth at least $100,000 in new sales | |||||||||||
| 11 | --OR | |||||||||||
| 12 | --2. - At least $250,000 in new sales | |||||||||||
| 13 | ||||||||||||
| 14 | ||||||||||||
| 15 | Current Amounts (Input) | Output | ||||||||||
| 16 | Current Business | New Clients | New Sales $ | Tier | Sub-Tier | |||||||
| 17 | $1,000,000 | 10 | $250,000 | Tier 1 | 1 | |||||||
| 18 | ||||||||||||
| 19 | Table of Rules | |||||||||||
| 20 | ||||||||||||
| 21 | Symb.1 | Current Business $ | Operator1 | Symb.2 | New Clients | Operator2 | Symb.3 | New Sales $ | Tier | Sub-Tier | ||
| 22 | >= | $1,000,000 | AND | >= | 10 | AND | > | $250,000 | Tier 1 | 1 | ||
| 23 | >= | $1,000,000 | AND | >= | N/A | AND | > | $500,000 | Tier 1 | 2 | ||
| 24 | < | $1,000,000 | AND | >= | 10 | AND | > | $100,000 | Tier 2 | 1 | ||
| 25 | < | $1,000,000 | AND | >= | N/A | AND | > | $250,000 | Tier 2 | 2 | ||
| 26 | ||||||||||||
| 27 | ||||||||||||
| 28 | ||||||||||||
| 29 | ||||||||||||
| 30 | Potential Future Scenerio | |||||||||||
| 31 | Table of Rules | |||||||||||
| 32 | ||||||||||||
| 33 | Symb.1 | Current Business $ | Operator1 | Symb.2 | New Clients | Operator2 | Symb.3 | New Sales $ | Tier | Sub-Tier | ||
| 34 | >= | $5,000,000 | AND | >= | 20 | OR | > | $400,000 | Tier 1 | 1 | ||
| 35 | >= | $5,000,000 | AND | >= | N/A | AND | > | $600,000 | Tier 1 | 2 | ||
| 36 | >= | $5,000,000 | AND | >= | 15 | AND | > | $300,000 | Tier 1 | 3 | ||
| 37 | >= | $3,000,000 | AND | >= | 15 | OR | > | $250,000 | Tier 2 | 1 | ||
| 38 | >= | $3,000,000 | AND | >= | N/A | AND | > | $500,000 | Tier 2 | 2 | ||
| 39 | < | $1,000,000 | AND | >= | 10 | AND | > | $100,000 | Tier 3 | 1 | ||
| 40 | < | $1,000,000 | AND | >= | N/A | AND | > | $250,000 | Tier 3 | 2 | ||
| 41 | < | $1,000,000 | OR | >= | 30 | OR | $500,000 | Tier 4 | 1 | |||
Sheet1 | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I17 | I17 | =IF(AND(A17>=B22,OR(AND(B17>=E22,C17>=H22),C17>=H23)),"Tier 1",IF(AND(A17<B24,A17>0,OR(AND(B17>=E24,C17>=H24),C17>=H25)),"Tier 2","Not Eligible")) |
| J17 | J17 | =IF(I17="Not Eligible","Not Eligible",IF(AND(I17="Tier 1",OR(AND(B17>=E22,C17>=H22),C17>=H23)),"1",IF(AND(I17="Tier 2",OR(AND(B17>=E24,C17>=H24),C17>=H25)),"2"))) |
