Forum Discussion

KG1's avatar
KG1
Resolver I
4 years ago
Solved

Nested If / And Statement

Hi   I have had the following formula sent to me in Excel - could you please advise how I would go about replicating this in a column?   I am really not expecting anyone to retype this out for me...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  KG1 ,

    I created some data:

    Here are the steps you can follow：

    1. Create calculated column.

    Column =
SWITCH(
    TRUE(), 'Table'[CD1]="TRUE"&&'Table'[P1]="TRUE"&&'Table'[P3]="TRUE"&&'Table'[Q1]="TRUE"&&'Table'[T1]="TRUE"&&'Table'[T3]="TRUE"
    ||
    AND('Table'[AC1]="TRUE",'Table'[AC2]="TRUE")
    ,"Receipting")

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • KG1's avatar
    KG1
    Resolver I
    4 years ago

    Great - thank you - is it a new switch at the start of each new IF e.g

     

    Test = switch ( True (),( [CD1] = "TRUE" && [P1] = "TRUE" && [P3] = "TRUE" && [Q1] = "TRUE" && [Q3] ="TRUE" && [T1] = "TRUE" && [T3] = "TRUE" ) ||[AC1] = "TRUE" && [AC2] = "TRUE" , "Receipting" , switch ( True (), [CD1] = "TRUE" && [CD2] = "TRUE" && [P2] = "TRUE" && [Q1] = "TRUE" && [Q4 - Positive] ="TRUE" && [R2] = "TRUE" && [S1] = "No" && [S4 - Positive] = "TRUE" && [Catalogue] = "NO", "Short-Costing - Procurement"))
  • KG1's avatar
    KG1
    Resolver I
    4 years ago

    switch ( True (),( [CD1] = "TRUE" && [P1] = "TRUE" && [P3] = "TRUE" && [Q1] = "TRUE" && [Q3] ="TRUE" && [T1] = "TRUE" && [T3] = "TRUE" ) ||[AC1] = "TRUE" && [AC2]  = "TRUE" , "Receipting"

     

    amitchandak  - can you please help with the above - it works fine up to the ||[AC1] = "TRUE" && [AC2]  = "TRUE"  - I can't get it to include this within the statement - the result should be false if either AC1 and AC2 are False but it keeps returning Receipting - it's like its completely ignoring the ||