ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   How to choose a qualifier from different set of criteria or combinations (https://www.excelbanter.com/excel-worksheet-functions/454209-how-choose-qualifier-different-set-criteria-combinations.html)

San[_4_]

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

MyVeryOwnSelf[_3_]

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 TRUE-or-FALSE values to test my approach. These four correspond to the conditions A,B,C,D.

This formula seems to meet the need:
=IFERROR(MATCH(--A1--2*B1--4*C1--8*D1,
{15,13,6,11,5,12},0), "")

In real life, dont use A1,B1,C1,D1, of course. Instead, replace them with the required conditions. Replacing A1,B1,C1,D1 with four real-life 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
--A1--2*B1--4*C1--8*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
--A1--2*B1--4*C1--8*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.

San[_4_]

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 TRUE-or-FALSE values to test my approach. These four correspond to the conditions A,B,C,D.

This formula seems to meet the need:
=IFERROR(MATCH(--A1--2*B1--4*C1--8*D1,
{15,13,6,11,5,12},0), "")

In real life, dont use A1,B1,C1,D1, of course. Instead, replace them with the required conditions. Replacing A1,B1,C1,D1 with four real-life 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
--A1--2*B1--4*C1--8*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
--A1--2*B1--4*C1--8*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?

MyVeryOwnSelf[_3_]

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. (--A1--2*B1--4*C1--8*D1) is using binary arithmetic to get a 4-bit 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 (--A1--10*B1--100*C1--1000*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.


San[_4_]

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. (--A1--2*B1--4*C1--8*D1) is using binary arithmetic to get a 4-bit 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 (--A1--10*B1--100*C1--1000*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


All times are GMT +1. The time now is 12:53 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com