Using "and" and "or" to simplify nested (multiple) if/then equations

 

One of the equations used in calculations is called an if/then equation (e.g If (a=1, true, false)). Sometimes there are multiple if/then arguments in a calculation; called nested if/then equations

The example in the image here shows multiple if/then equations being used to generate a particular output:

If (Total Box Jumps >100,

If (Jumps 30 to 60 > 30,

If (Box Jumps at 30 > 20, "Legend",

"Average"), "Average"),"Average")

This means if "Total Box Jumps" are >100 AND "Jumps 30 to 60" are >30 AND "Box Jumps at 30" are >20 then the equation will return the word "Legend". If all of these conditions are not met it will return the word "Average". These equations are time consuming to write and you can often make mistakes writing them.

-Now this can be written much more succinctly using "and" (see the step below).

Using the "and" in a nested if/then equation allows you to write equations much more easily

Using the "and" in a nested if/then equation allows you to write equations much more easily

Instead of having to write all of the nested if/then equations so that each condition is listed in the "If /then"equation format, you can now write the equation and put all of the conditions in a set of brackets (as long as they are separate by a comma).

The original equation:

If (Total Box Jumps >100,

If (Jumps 30 to 60 > 30,

If (Box Jumps at 30 > 20, "Legend",

"Average"), "Average"),"Average")

The new equation written with "and""

If (and (Total Box Jumps >100, Jumps 30 to 60 > 30, Box Jumps at 30 > 20), "Legend", "Average")

The "and" is added before the separate conditions are listed.

The result is the same, but it much easier to write the second equation

Alternatively, you may have an equation which is written as an "or" equation (as shown here). If any of the conditions are met then a specific output is returned.

Alternatively, you may have an equation which is written as an "or" equation (as shown here). If any of the conditions are met then a specific output is returned.

The equation is written as:

If (Total Box Jumps >100, "Legend",

If (Jumps 30 to 60 > 30, "Legend",

If (Box Jumps at 30 > 20, "Legend",

"Average")))

This means if Total Box Jumps are greater than 100 then return "Legend". If that is not true, then move to the next condition and see if Jumps 30 to 60 are greater than 30. If that is true return "Legend", but if it is not true then move to the next condition. If Box Jumps at 30 is greater than 20 then return "Legend", but if that is not true then return "Average".

Now this can be written much more succinctly using an "or" (see the step below).

Using the "or" in a nested if/then equation allows you to write equations this type of equation much more easily

Using the "or" in a nested if/then equation allows you to write equations this type of equation much more easily

Instead of having to write all of the nested if equations in the "if /then"equation format, you can now write the equation and put all of the conditions in a set of brackets as long as they are separate by a comma.

Original equation:

If (Total Box Jumps >100, "Legend",

If (Jumps 30 to 60 > 30, "Legend",

If (Box Jumps at 30 > 20, "Legend",

"Average")))

New equation format:

If (or (Total Box Jumps >100, Jumps 30 to 60 > 30, Box Jumps at 30 > 20), "Legend", "Average")

The "or" is added before the separate conditions are listed.

The result is the same, but it much easier to write the second equation

The above example show 3 conditions being used, but you can use as many conditions as you need. If you need additional help with these or additional documentation just contact your distributor

 

0 Comments

Add your comment

E-Mail me when someone replies to this comment