Forum Discussion

Jimbobmcwalton's avatar
Jimbobmcwalton
Brass Contributor
Feb 27, 2019

Using Switch Formula but adding an exception

 

Hi There,

 

I have the below formula that works fine & calculates correctly.  

=SWITCH(W401=Z$2,TRUE,S401*0.1,FALSE,S401*0.025,)

 

I would like to add an exception to only perform this calculation if 3 different cells do not contain certain words from the same line (these words all change as come in from a different tab based on account information, the exception is only needed on one combination) , is this possible? Ideally I would like to do this all in the same cell IE add it to the above formula.  

 

Many Thanks 

 

Jim 

 

 

 

3 Replies

  • Something like this maybe?

     

    =IF(
    TEXTJOIN(",",TRUE,A401:C401)="Word1,Word2,Word3",
    "Exclude",
    SWITCH(
    W401=Z$2,
    TRUE,S401*0.1,
    FALSE,S401*0.025,"")
    )

    • Jimbobmcwalton's avatar
      Jimbobmcwalton
      Brass Contributor

      Thanks for that, the formula seemed to build ok but on pressing enter it returns an error 

       

      im using 

       

      =IF(TEXTJOIN(",",TRUE,U388:V388)="Route Cover Parcel,Reint,Yes","Exclude"SWITCH(W401=Z$2,TRUE,S401*0.1,FALSE,S401*0.025,""))

       

      I get the error thinking im not trying to enter a formula

       

      Cheers 

       

      Jim 

       

       

       

       

       

       

Resources