Home 
Search 
Today's Posts 
#1




How to choose a qualifier from different set of criteria or combinations
I have a set of conditions denoted by A, B, C, D and depending upon specific combinations of the conditions being true, I need to take a corresponding decision from a group of decisions denoted as 1,2,3,4,5,6 So if True or "T" for the following conditions, the corresponding decisions would be as mentioned under Conditions True Decision Number A,B,C,D  1 A, C, D  2 B, C  3 A, B, D  4 A, C  5 C, D  6 I hope I have been able to explain my problem. Thanks for the help San 
#2




How to choose a qualifier from different set of criteria or combinations
Not knowing what the conditions are, I temporarily used cells A1,B1,C1,D1 to hold TRUEorFALSE values to test my approach. These four correspond to the conditions A,B,C,D.
This formula seems to meet the need: =IFERROR(MATCH(A12*B14*C18*D1, {15,13,6,11,5,12},0), "") In real life, donâ€™t use A1,B1,C1,D1, of course. Instead, replace them with the required conditions. Replacing A1,B1,C1,D1 with four reallife conditions might look something like: =IFERROR(MATCH((K7<5) 2*(J7+8P7) 4*OR(Q7100,Q7<0) 8*(TODAY()+60R7), {15,13,6,11,5,12}, 0), "") Explanation: There are sixteen possible combinations of TRUE/FALSE values for the four conditions. The expression A12*B14*C18*D1 assigns a different number to each combination. For example, the combination number 15 is what the expression returns from the "conditions true" for decision number 1. The array {15,13,6,11,5,12} lists the combination numbers for the six "conditions true" lines in the original post. Since 15 is the first entry in {15,13,6,11,5,12}, the MATCH returns 1 (as required) when A12*B14*C18*D1 is 15; that is, when conditions A, B, C, D are true. Likewise, the conditions for decision number 2 produce combination number 13, causing the MATCH to return 2, etc. For combinations that do not appear in the original post, IFERROR makes the result an empty string, since no decision number applies. Hope this helps getting started. 
#3




How to choose a qualifier from different set of criteria or combinations
On Wednesday, January 16, 2019 at 1:29:53 AM UTC+5:30, MyVeryOwnSelf wrote:
Not knowing what the conditions are, I temporarily used cells A1,B1,C1,D1 to hold TRUEorFALSE values to test my approach. These four correspond to the conditions A,B,C,D. This formula seems to meet the need: =IFERROR(MATCH(A12*B14*C18*D1, {15,13,6,11,5,12},0), "") In real life, donâ€™t use A1,B1,C1,D1, of course. Instead, replace them with the required conditions. Replacing A1,B1,C1,D1 with four reallife conditions might look something like: =IFERROR(MATCH((K7<5) 2*(J7+8P7) 4*OR(Q7100,Q7<0) 8*(TODAY()+60R7), {15,13,6,11,5,12}, 0), "") Explanation: There are sixteen possible combinations of TRUE/FALSE values for the four conditions. The expression A12*B14*C18*D1 assigns a different number to each combination. For example, the combination number 15 is what the expression returns from the "conditions true" for decision number 1. The array {15,13,6,11,5,12} lists the combination numbers for the six "conditions true" lines in the original post. Since 15 is the first entry in {15,13,6,11,5,12}, the MATCH returns 1 (as required) when A12*B14*C18*D1 is 15; that is, when conditions A, B, C, D are true. Likewise, the conditions for decision number 2 produce combination number 13, causing the MATCH to return 2, etc. For combinations that do not appear in the original post, IFERROR makes the result an empty string, since no decision number applies. Hope this helps getting started. Thanks a ton for the help. In fact I was trying to use the "Choose" function but it was not working. Just want to know regarding the assigning of 1 for A, 2 for B, 4 for C and 8 for D. Is this assigning of 1,2,4,8, in anyway related to the maximum no. of combinations of 4 conditions or is it just to arrive at a unique value for each set of True/False? 
#4




How to choose a qualifier from different set of criteria or combinations
Is this assigning of 1,2,4,8, in anyway related to the
maximum no. of combinations of 4 conditions? Yes. (A12*B14*C18*D1) is using binary arithmetic to get a 4bit number with each bit representing one condition, TRUE or FALSE. The values are 0, 1, 2, 3, 4, ..., 15. If there were 5 conditions, we'd use 1,2,4,8,16 and the values would be 0, 1, 2, 3, 4, ..., 33. In this situation, we could actually use regular old decimal arithmetic (A110*B1100*C11000*D1) to get values (0, 1, 10, 11, 100, ..., 1111) and the list would be {1111, 1101, 0110, 1011, 0101, 1100}. I was trying to use the "Choose" function ... MATCH works well (compared with CHOOSE) only because the decision numbers are small integers. 
#5




How to choose a qualifier from different set of criteria or combinations
On Wednesday, January 16, 2019 at 11:47:23 PM UTC+5:30, MyVeryOwnSelf wrote:
Is this assigning of 1,2,4,8, in anyway related to the maximum no. of combinations of 4 conditions? Yes. (A12*B14*C18*D1) is using binary arithmetic to get a 4bit number with each bit representing one condition, TRUE or FALSE. The values are 0, 1, 2, 3, 4, ..., 15. If there were 5 conditions, we'd use 1,2,4,8,16 and the values would be 0, 1, 2, 3, 4, ..., 33. In this situation, we could actually use regular old decimal arithmetic (A110*B1100*C11000*D1) to get values (0, 1, 10, 11, 100, ..., 1111) and the list would be {1111, 1101, 0110, 1011, 0101, 1100}. I was trying to use the "Choose" function ... MATCH works well (compared with CHOOSE) only because the decision numbers are small integers. Thanks for the clarification 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
choose range based on criteria  Excel Programming  
get a value from multiple criteria combinations  Excel Worksheet Functions  
Creating combinations based on a criteria  Excel Programming  
Invalid Qualifier  Excel Programming  
Invalid Qualifier???  Excel Programming 