Results 1 to 4 of 4

Thread: CF in a cell with 6 conditions

  1. #1

    Lightbulb CF in a cell with 6 conditions



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    121
    Articles
    0
    Excel Version
    Microsoft Excel 2013
    You can write a similar formula for each CF using:

    Code:
    =AND(A1<B1,B1<C1,A1<C1)
    Just change the "<", ">", "<=", ">=" symbols around as required to meet your conditions.

    However a word of caution; your logic doesn't work:

    - CF6 is logically impossible;
    - You have no CF that will identify (A1=1, B1=1, C1=2);
    - CF4 meets several conditions: (A1=1, B1=1, C1=1) and (A1=2, B1=1, C1=1) and (A1=3, B1=2, A1=1) and (A1=2, B1=2, C1=1); and
    - CF5 meets two conditions: (A1=3, B1=1, C1=2) and (A1=2, B1=1, C1=2).

    Cheers,

  3. #3
    However a word of caution; your logic doesn't work:

    - CF6 is logically impossible;
    - You have no CF that will identify (A1=1, B1=1, C1=2);
    - CF4 meets several conditions: (A1=1, B1=1, C1=1) and (A1=2, B1=1, C1=1) and (A1=3, B1=2, A1=1) and (A1=2, B1=2, C1=1); and
    - CF5 meets two conditions: (A1=3, B1=1, C1=2) and (A1=2, B1=1, C1=2).

    CF6 (amended)=6:A1 is greater than or equal to B1; B1 is LESS THAN C1 and A1 is less than C1
    CF5 to be made 2 parts: CF5 & CF7. CF5=5:
    A1 is greater than or equal to B1; B1 is less than C1 and A1 is greater than C1
    CF7=7:
    A1 is greater than or equal to B1; B1 is less than C1 and A1 is equal to C1

  4. #4
    For your nice' point: CF4 meets several conditions: (A1=1, B1=1, C1=1) and (A1=2, B1=1, C1=1) and (A1=3, B1=2, A1=1) and (A1=2, B1=2, C1=1)

    Please 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

    I hope all the conditions are met.




Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •