Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 12th 19, 06:22 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2013
Posts: 22
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  
Old Yesterday, 08:59 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2012
Posts: 46
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, don’t 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  
Old Today, 04:31 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2013
Posts: 22
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, don’t 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?


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 02:48 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017