Case and Switch Logic

5.43K viewsFormulas and Functions
0

Nest IF/THEN formulas? Forget about ‘em!
If you are using nested IF/THEN formulas, please consider these two powerful conditional logic functions. Here is a summary:

CASE: evaluate one cell and if it is equal to then do different things based on the value of that cell.
SWITCH: evaluate different cells with any time of condition then do different things based on the different cells.

Can CASE and SWITCH be combined? Why yes then can.
For example:
CASE (Evaluation Location::Cell A,
[color=#00BF40:2os8ztsr]“open”,
SWITCH (New Evaluation Location::Test1=”Red”, do red logic,
New Evaluation Location::Test2=”Blue”, do blue logic),
“It is some other color”[/color:2os8ztsr],
[color=#FF0000:2os8ztsr]“closed”,
SWITCH (Alternate Evaluation Location::Signal 1<1000,do min logic,
Alternate Evaluation Location::Signal 2>1000,do max logic)
“Must be 1000”,[/color:2os8ztsr]
[color=#FFFF00:2os8ztsr]“I don’t know what to do”)[/color:2os8ztsr]

In the example above, I am checking the location Evaluation Location::Cell A. Depending on the value of that one cell, it go in two different directions (think fork in the road). Once I am on the new path, I evaluate two different another location called New Evaluation Location::Test 1 or Test 2. If Test 1 is “Red” then I do the red logic or next if Test 2 is Blue then I do the blue logic. If neither location is red or blue, I send the message that it I have found neither red or blue.
I repeat the tree if Cell A is closed. If Cell is neither open or closed, then I send the message that I am clueless as to what to do.
I’ve included a logic tree powerpoint to illustrate each function. Oh yeah, liberal use of white space makes these much easier to read. CTRL ENTER puts in a return in a formula!
Enjoy

PS: The forum posting doesn’t seem to like indentation so I tried to show the section with color. So I added the formatted structure to the PowerPoint file

Latest Questions