Home

Using Switch Formula but adding an exception

Jimbobmcwalton
Contributor

 

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,"")
)

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 

 

 

 

 

 

 

Jim, at least comma is missed

image.png

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies