CF in a cell with 6 conditions

[hsandeep]

I want to formulate 6 CFs in a cell B1 to generate either of the value 1,2,3,4,5,6.

6 CFs are to be formulated to generate either of:
CF1=1: A1 is less than B1; B1 is less than C1 and A1 is less than C1
CF2=2: A1 is less than B1; B1 is greater than or equal to C1 and A1 is less than C1
CF3=3: A1 is less than B1; B1 is greater than or equal to C1 and A1 is greater than or equal to C1
CF4=4: A1 is greater than or equal to B1; B1 is greater than or equal to C1 and A1 is greater than or equal to C1
CF5=5: A1 is greater than or equal to B1; B1 is less than C1 and A1 is greater than or equal to C1
CF6=6: A1 is greater than or equal to B1; B1 is greater than or equal to C1 and A1 is less than C1



What should the formula be in the CF? Thanx in adv.

 

[hsandeep]

That one, at least, is impossible.

You are CORRECT. So this 6th condition is not required i.e CF6 NOT REQUIRED in thread #1.

 

[hsandeep]

Aside from the row with the question marks, does the result column look correct?

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

A1​

[/TD]
[TD]

B1​

[/TD]
[TD]

C1​

[/TD]
[TD]

result​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

??​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[/TR]
</tbody>[/TABLE]


The formula in E2 and down is

=CHOOSE(4*(A2>=B2) + 2*(B2>=C2) + (A2>=C2) + 1, 1, "impossible", 2, 3, "??", 5, "impossible", 4)

What should be the result in row 4?

First of all, the formula is needed in B1 cell in the 'CF Rules Manager' to generate either of 1,2,3,4,5,6.
Your comment:"result in row 4": Answer: Here 'AMENDED' CF6 comes into play. Result=6
'AMENDED' CF6=6: A1 is greater than or equal to B1; B1 is LESS than C1 and A1 is less than C1
I'll post a sample data. Have a look & then confirm the formula
Sheet1

	A	B	C	D

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]500[/TD]
[TD="bgcolor: #ffff00, align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]500[/TD]
[TD="bgcolor: #ffff00, align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]200[/TD]
[TD="bgcolor: #ffff00, align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: #ffff00, align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]250[/TD]
[TD="bgcolor: #ffff00, align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #ffff00, align: right"]300[/TD]
[TD="bgcolor: #ffff00, align: right"]300[/TD]
[TD="bgcolor: #ffff00, align: right"]500[/TD]
[TD="bgcolor: #ffff00, align: right"]6[/TD]

</tbody>

Although the table shows result to be 'appearing' in column D but I need it to be generated in B1 cell in the 'CF Rules Manager'.
There are 3 cells only A1, B1 & C1.
A1 & C1=Values.
Output in B1 cell in the 'CF Rules Manager' to generate either of 1,2,3,4,5,6.

 

[hsandeep]

I'll reconstruct the CFs in B1 and make it 7 as:

6 CFs are to be formulated to generate either of:
CF1=1: A1 is less than B1; B1 is less than C1 and A1 is less than C1
CF2=2: A1 is less than B1; B1 is greater than or equal to C1 and A1 is less than C1
CF3=3: A1 is less than B1; B1 is greater than or equal to C1 and A1 is greater than or equal to C1
CF4=4: A1 is greater than or equal to B1; B1 is greater than or equal to C1 and A1 is greater than or equal to C1
CF5=5: A1 is greater than or equal to B1; B1 is less than C1 and A1 is greater than C1
CF6=6: A1 is greater than or equal to B1; B1 is less than C1 and A1 is less than C1
CF7=7: A1 is greater than or equal to B1; B1 is less than C1 and A1 is equal to C1

 

[hsandeep]

Finally: total 10 CFs in B1:

6 CFs are to be formulated to generate either of:
CF1=1: A1 is less than B1; B1 is less than C1 and A1 is less than C1
CF2=2: A1 is less than B1; B1 is greater than or equal to C1 and A1 is less than C1
CF3=3: A1 is less than B1; B1 is greater than or equal to C1 and A1 is greater than or equal to C1
CF4=4: A1 is greater than or equal to B1; B1 is greater than or equal to C1 and A1 is greater than or equal to C1
CF5=5: A1 is greater than or equal to B1; B1 is less than C1 and A1 is greater than C1
CF6=6: A1 is greater than or equal to B1; B1 is less than C1 and A1 is less than C1
CF7=7: A1 is greater than or equal to B1; B1 is less than C1 and A1 is equal to C1

ADD 3 more CFs (now totalling 10)
CF8=8: A1 is equal to B1; B1 is equal to C1 and A1 is equal to C1
CF9=9: A1 is greater than B1; B1 is greater than C1 and A1 is less than C1
CF10=10: A1 is equal to B1; B1 is greater than C1 and A1 is greater than C1

 

[shg]

Instead of me figuring that out, how about you complete this table:

[Table="width:, class:grid"][tr][td] [/td][td]

L​

[/td][td]

M​

[/td][td]

N​

[/td][td]

P​

[/td][/tr]
[tr][td]

2​

[/td][td]

A1:B1​

[/td][td]

B1:C1​

[/td][td]

A1:C1​

[/td][td]

CF​

[/td][/tr]

[tr][td]

3​

[/td][td]

<​

[/td][td]

<​

[/td][td]

<​

[/td][td]

??​

[/td][/tr]

[tr][td]

4​

[/td][td]

<​

[/td][td]

<​

[/td][td]

=​

[/td][td]

??​

[/td][/tr]

[tr][td]

5​

[/td][td]

<​

[/td][td]

<​

[/td][td]

>​

[/td][td]

??​

[/td][/tr]

[tr][td]

6​

[/td][td]

<​

[/td][td]

=​

[/td][td]

<​

[/td][td]

??​

[/td][/tr]

[tr][td]

7​

[/td][td]

<​

[/td][td]

=​

[/td][td]

=​

[/td][td]

??​

[/td][/tr]

[tr][td]

8​

[/td][td]

<​

[/td][td]

=​

[/td][td]

>​

[/td][td]

??​

[/td][/tr]

[tr][td]

9​

[/td][td]

<​

[/td][td]

>​

[/td][td]

<​

[/td][td]

??​

[/td][/tr]

[tr][td]

10​

[/td][td]

<​

[/td][td]

>​

[/td][td]

=​

[/td][td]

??​

[/td][/tr]

[tr][td]

11​

[/td][td]

<​

[/td][td]

>​

[/td][td]

>​

[/td][td]

??​

[/td][/tr]

[tr][td]

12​

[/td][td]

=​

[/td][td]

<​

[/td][td]

<​

[/td][td]

??​

[/td][/tr]

[tr][td]

13​

[/td][td]

=​

[/td][td]

<​

[/td][td]

=​

[/td][td]

??​

[/td][/tr]

[tr][td]

14​

[/td][td]

=​

[/td][td]

<​

[/td][td]

>​

[/td][td]

??​

[/td][/tr]

[tr][td]

15​

[/td][td]

=​

[/td][td]

=​

[/td][td]

<​

[/td][td]

??​

[/td][/tr]
[/table]

 

[hsandeep]

Instead of me figuring that out, how about you complete this table:

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

L​

[/TD]
[TD]

M​

[/TD]
[TD]

N​

[/TD]
[TD]

P​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

A1:B1​

[/TD]
[TD]

B1:C1​

[/TD]
[TD]

A1:C1​

[/TD]
[TD]

CF​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

<​

[/TD]
[TD]

<​

[/TD]
[TD]

<​

[/TD]
[TD]

??​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

<​

[/TD]
[TD]

<​

[/TD]
[TD]

=​

[/TD]
[TD]

??​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

<​

[/TD]
[TD]

<​

[/TD]
[TD]

>​

[/TD]
[TD]

??​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

<​

[/TD]
[TD]

=​

[/TD]
[TD]

<​

[/TD]
[TD]

??​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

<​

[/TD]
[TD]

=​

[/TD]
[TD]

=​

[/TD]
[TD]

??​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

<​

[/TD]
[TD]

=​

[/TD]
[TD]

>​

[/TD]
[TD]

??​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

<​

[/TD]
[TD]

>​

[/TD]
[TD]

<​

[/TD]
[TD]

??​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

<​

[/TD]
[TD]

>​

[/TD]
[TD]

=​

[/TD]
[TD]

??​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

<​

[/TD]
[TD]

>​

[/TD]
[TD]

>​

[/TD]
[TD]

??​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

=​

[/TD]
[TD]

<​

[/TD]
[TD]

<​

[/TD]
[TD]

??​

[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

=​

[/TD]
[TD]

<​

[/TD]
[TD]

=​

[/TD]
[TD]

??​

[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

=​

[/TD]
[TD]

<​

[/TD]
[TD]

>​

[/TD]
[TD]

??​

[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

=​

[/TD]
[TD]

=​

[/TD]
[TD]

<​

[/TD]
[TD]

??​

[/TD]
[/TR]
</tbody>[/TABLE]

Answers:

Sheet1 (2)

	P

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 48px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]16[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]18[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]19[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]20[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]21[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]22[/TD]

</tbody>

 

[shg]

My apologies, I posted the wrong list. It should be

[Table="width:, class:grid"][tr][td] [/td][td]

R​

[/td][td]

S​

[/td][td]

T​

[/td][td]

U​

[/td][td]

V​

[/td][/tr]
[tr][td]

2​

[/td][td]

A1:B1​

[/td][td]

B1:C1​

[/td][td]

A1:C1​

[/td][td]

Case​

[/td][td]

CF​

[/td][/tr]

[tr][td]

3​

[/td][td]

<​

[/td][td]

<​

[/td][td]

<​

[/td][td]

1​

[/td][td]

??​

[/td][/tr]

[tr][td]

4​

[/td][td]

<​

[/td][td]

=​

[/td][td]

<​

[/td][td]

3​

[/td][td]

??​

[/td][/tr]

[tr][td]

5​

[/td][td]

<​

[/td][td]

>​

[/td][td]

<​

[/td][td]

6​

[/td][td]

??​

[/td][/tr]

[tr][td]

6​

[/td][td]

<​

[/td][td]

>​

[/td][td]

=​

[/td][td]

7​

[/td][td]

??​

[/td][/tr]

[tr][td]

7​

[/td][td]

<​

[/td][td]

>​

[/td][td]

>​

[/td][td]

8​

[/td][td]

??​

[/td][/tr]

[tr][td]

8​

[/td][td]

=​

[/td][td]

<​

[/td][td]

<​

[/td][td]

9​

[/td][td]

??​

[/td][/tr]

[tr][td]

9​

[/td][td]

=​

[/td][td]

=​

[/td][td]

=​

[/td][td]

13​

[/td][td]

??​

[/td][/tr]

[tr][td]

10​

[/td][td]

=​

[/td][td]

>​

[/td][td]

>​

[/td][td]

17​

[/td][td]

??​

[/td][/tr]

[tr][td]

11​

[/td][td]

>​

[/td][td]

<​

[/td][td]

<​

[/td][td]

18​

[/td][td]

??​

[/td][/tr]

[tr][td]

12​

[/td][td]

>​

[/td][td]

<​

[/td][td]

=​

[/td][td]

19​

[/td][td]

??​

[/td][/tr]

[tr][td]

13​

[/td][td]

>​

[/td][td]

<​

[/td][td]

>​

[/td][td]

20​

[/td][td]

??​

[/td][/tr]

[tr][td]

14​

[/td][td]

>​

[/td][td]

=​

[/td][td]

>​

[/td][td]

23​

[/td][td]

??​

[/td][/tr]

[tr][td]

15​

[/td][td]

>​

[/td][td]

>​

[/td][td]

>​

[/td][td]

26​

[/td][td]

??​

[/td][/tr]
[/table]

The formula in U3 and down is

=9*SIGN(O3-P3) + 3*SIGN(P3-Q3) + SIGN(O3-Q3) + 14

The formula you need will be like

=LOOKUP(9*SIGN(A1-B1) + 3*SIGN(B1-C1) + SIGN(A1-C1) + 14, {1,3,6,7,8,9,13,17,18,19,20,23,26}, {1,3,6,7,8,9,13,17,18,19,20,23,26})

Except replace the second array with the results you want.

 

[hsandeep]

O,P,Q & A,B,C (from the above formula) doesn't match with the table's headers but probably it is like the table should be read as with column headers being A B C D E & to replace O,P,Q with A,B,C in your 1st formula. (Please correct me if I am wrong here).
The formula needs to be punched in F1 probably & copied down thr' F1:F13. (Please correct me if I am wrong here).

Some logic seems to be in your mind for adding another column V (column E in case of ABCDE). Can you share the logic?

 

[shg]

The columns weren't shown because they just contained the numbers used to calculate the valid subsets of comparisons:

[Table="width:, class:grid"][tr][td] [/td][td]

O​

[/td][td]

P​

[/td][td]

Q​

[/td][td]

R​

[/td][td]

S​

[/td][td]

T​

[/td][td]

U​

[/td][td]

V​

[/td][/tr]
[tr][td]

2​

[/td][td]

A1:B1​

[/td][td]

B1:C1​

[/td][td]

A1:C1​

[/td][td]

A1:B1​

[/td][td]

B1:C1​

[/td][td]

A1:C1​

[/td][td]

Case​

[/td][td]

CF​

[/td][/tr]

[tr][td]

3​

[/td][td]

0​

[/td][td]

1​

[/td][td]

2​

[/td][td]

<​

[/td][td]

<​

[/td][td]

<​

[/td][td]

1​

[/td][td]

??​

[/td][/tr]

[tr][td]

4​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

<​

[/td][td]

=​

[/td][td]

<​

[/td][td]

3​

[/td][td]

??​

[/td][/tr]

[tr][td]

5​

[/td][td]

0​

[/td][td]

2​

[/td][td]

1​

[/td][td]

<​

[/td][td]

>​

[/td][td]

<​

[/td][td]

6​

[/td][td]

??​

[/td][/tr]

[tr][td]

6​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td]

<​

[/td][td]

>​

[/td][td]

=​

[/td][td]

7​

[/td][td]

??​

[/td][/tr]

[tr][td]

7​

[/td][td]

1​

[/td][td]

2​

[/td][td]

0​

[/td][td]

<​

[/td][td]

>​

[/td][td]

>​

[/td][td]

8​

[/td][td]

??​

[/td][/tr]

[tr][td]

8​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

=​

[/td][td]

<​

[/td][td]

<​

[/td][td]

9​

[/td][td]

??​

[/td][/tr]

[tr][td]

9​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

=​

[/td][td]

=​

[/td][td]

=​

[/td][td]

13​

[/td][td]

??​

[/td][/tr]

[tr][td]

10​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

=​

[/td][td]

>​

[/td][td]

>​

[/td][td]

17​

[/td][td]

??​

[/td][/tr]

[tr][td]

11​

[/td][td]

1​

[/td][td]

0​

[/td][td]

2​

[/td][td]

>​

[/td][td]

<​

[/td][td]

<​

[/td][td]

18​

[/td][td]

??​

[/td][/tr]

[tr][td]

12​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

>​

[/td][td]

<​

[/td][td]

=​

[/td][td]

19​

[/td][td]

??​

[/td][/tr]

[tr][td]

13​

[/td][td]

2​

[/td][td]

0​

[/td][td]

1​

[/td][td]

>​

[/td][td]

<​

[/td][td]

>​

[/td][td]

20​

[/td][td]

??​

[/td][/tr]

[tr][td]

14​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

>​

[/td][td]

=​

[/td][td]

>​

[/td][td]

23​

[/td][td]

??​

[/td][/tr]

[tr][td]

15​

[/td][td]

2​

[/td][td]

1​

[/td][td]

0​

[/td][td]

>​

[/td][td]

>​

[/td][td]

>​

[/td][td]

26​

[/td][td]

??​

[/td][/tr]
[/table]

I changed the references to use the cells you asked for in the second formula I posted:

=LOOKUP(9*SIGN(A1-B1) + 3*SIGN(B1-C1) + SIGN(A1-C1) + 14, {1,3,6,7,8,9,13,17,18,19,20,23,26}, {1,3,6,7,8,9,13,17,18,19,20,23,26})

Some logic seems to be in your mind for adding another column V (column E in case of ABCDE).

Column V is there for you to specify what result you want for the CF formula for each of the 13 possible cases. Then you would use those to replace the second array in the LOOKUP formula.

 

[shg]

Is this easier to follow?

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

2​

[/td][td]

1​

[/td][td]D1: =LOOKUP(9*SIGN(A1-B1) + 3*SIGN(B1-C1) + SIGN(A1-C1) + 14, {1,3,6,7,8,9,13,17,18,19,20,23,26}, {1,3,6,7,8,9,13,17,18,19,20,23,26})[/td][/tr]

[tr][td]

2​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

3​

[/td][td][/td][/tr]

[tr][td]

3​

[/td][td]

0​

[/td][td]

2​

[/td][td]

1​

[/td][td]

7​

[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td]

8​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

1​

[/td][td]

2​

[/td][td]

0​

[/td][td]

9​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

9​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

13​

[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

18​

[/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

1​

[/td][td]

0​

[/td][td]

2​

[/td][td]

19​

[/td][td][/td][/tr]

[tr][td]

10​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

20​

[/td][td][/td][/tr]

[tr][td]

11​

[/td][td]

2​

[/td][td]

0​

[/td][td]

1​

[/td][td]

20​

[/td][td][/td][/tr]

[tr][td]

12​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

23​

[/td][td][/td][/tr]

[tr][td]

13​

[/td][td]

2​

[/td][td]

1​

[/td][td]

0​

[/td][td]

26​

[/td][td][/td][/tr]
[/table]