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.

 

[ParamRay]

.
.

Please clarify: are you talking about conditional formatting or custom functions?

 

[hsandeep]

.
.

Please clarify: are you talking about conditional formatting or custom functions?

It is conditional formatting to be done in B1

 

[Ragnar1211]

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


=and(a1<B1,B1<C1,A1<C1)
=and(a1<B1,B1>=C1,A1<C1)
=and(a1<B1,B1>=C1,A1>=C1)
=and(a1>=B1,B1>=C1,A1>=C1)
=and(a1>=B1,B1<C1,A1>=C1)
=and(a1>=B1,B1>=C1,A1<C1)

 

[Ragnar1211]

giving up... formatting wins

 

[shg]

You need to put a space after < so it's not interpreted as the start of an html tag.

 

[ztodd]

You need to put a space after < so it's not interpreted as the start of an html tag.

He doesn't use any... oh i see, he was probably trying to use one<s< html=""></s<>

 

[shg]

CF6=6: A1 is greater than or equal to B1; B1 is greater than or equal to C1 and A1 is less than C1

That one, at least, is impossible.

 

[shg]

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

[Table="width:, class:grid"][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]
[/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?

 

[shg]

Just need a place to park this:

[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]

9​

[/td][td]

Case​

[/td][td]

A1:B1​

[/td][td]

B1:C1​

[/td][td]

A1:C1​

[/td][td]

CF​

[/td][/tr]

[tr][td]

10​

[/td][td]

0​

[/td][td]

<​

[/td][td]

<​

[/td][td]

<​

[/td][td]

1​

[/td][/tr]

[tr][td]

11​

[/td][td]

1​

[/td][td]

<​

[/td][td]

<​

[/td][td]

>=​

[/td][td]

impossible​

[/td][/tr]

[tr][td]

12​

[/td][td]

2​

[/td][td]

<​

[/td][td]

>=​

[/td][td]

<​

[/td][td]

2​

[/td][/tr]

[tr][td]

13​

[/td][td]

3​

[/td][td]

<​

[/td][td]

>=​

[/td][td]

>=​

[/td][td]

3​

[/td][/tr]

[tr][td]

14​

[/td][td]

4​

[/td][td]

>=​

[/td][td]

<​

[/td][td]

<​

[/td][td]

??​

[/td][/tr]

[tr][td]

15​

[/td][td]

5​

[/td][td]

>=​

[/td][td]

<​

[/td][td]

>=​

[/td][td]

5​

[/td][/tr]

[tr][td]

16​

[/td][td]

6​

[/td][td]

>=​

[/td][td]

>=​

[/td][td]

<​

[/td][td]

impossible​

[/td][/tr]

[tr][td]

17​

[/td][td]

7​

[/td][td]

>=​

[/td][td]

>=​

[/td][td]

>=​

[/td][td]

4​

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