Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 56
Default 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.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default 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?
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 56
Default 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.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
choose range based on criteria Ivanl Excel Programming 1 May 13th 09 06:01 PM
get a value from multiple criteria combinations ladygr Excel Worksheet Functions 5 February 7th 07 11:19 PM
Creating combinations based on a criteria Maxi[_2_] Excel Programming 6 February 26th 06 02:56 PM
Invalid Qualifier Stuart[_21_] Excel Programming 4 April 21st 05 03:07 PM
Invalid Qualifier??? Michael Vaughan Excel Programming 1 November 29th 04 12:46 PM


All times are GMT +1. The time now is 04:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"