Conditional functions in Excel help us to make decisions. They can be combined and nested (you can place a function inside another function) depending on the situation. Let's know each one of them, but first let's see the table of logical operators:
The logical operators are:
> = Greater Equal
<= Equal Minor
Every conditional function depends on logical testing. The idea is that the function returns you a value depending on a criteria you set. These operators will be used in these functions in their logical tests and conditions.
The function E returns us TRUE if all logical tests are true, or false if one of them is false. Let's exemplify:
Function Syntax E is: = E (Logical; ...; Logical)
We can do several logical tests on the function. See example:
In this example the function will return TRUE because 1 is smaller than 30 and 30 is different from 20 and 32 is larger than 10, all tests are true, so the function return will be TRUE.
In this example:
The function return will be FALSE because 1 is not greater than 30, when one of the tests is not true, the function return will be FALSE.
We can conclude that the function E will only return TRUE when all tests are true. When you go into logical tests of your spreadsheet, ideally you should use cell references instead of putting absolute values in your calculations, so that whenever you change a value, the function will be recalculated and give a new answer.
The OR function behaves contrary to the E function if only one of the logic tests is TRUE, it will return TRUE, even if the others are false. Its syntax is as follows:
= OR (Logical; ...; Logical)
The return of the function in the example will be TRUE because one of the logical tests is true, see: 10 is not larger than 40, but 45 is different from 50, the result of that test is enough for the return function TRUE since 6 is not smaller than 3.
In this example:
The return from the above example will be FALSE because all logic tests are false. See: 10 is not greater than 40; 45 is not greater than 50; 6 is not smaller than 3.
We can conclude that the function OU will only return FALSE when all tests are false.
With the SE function differently from the E ou OU, in addition to testing whether the condition is true or false, we can tell her what to do in each situation, whether she will perform a calculation or whether she will return a message. It will give us the ability to automate our worksheet intelligently.
Its syntax is: = SE (logical_test, True_Value, True_value)
Logical_test: as in the OR and E functions in the IF function we have a logical test that will determine if the situation is true or false, this test can compare two values that we can indicate in the following ways: 10> 20; A2 <> B2; 10> C2. We can both use absolute values as reference of cells.
Valor_se_Verdadeiro: is what the function will return if the logical_test be true, we can indicate a calculation, reference a cell, or even display a message. In the case of the message the text should be enclosed in quotation marks as in the examples:
A2; (A2 + A4); "This is the Greatest Value";
Value_se_Falso: is what the function will return if the logical_test whether false or true, we can indicate a calculation, reference a cell or even present a message, in this case the message must also be enclosed in quotation marks, besides also being able to use the operator FALSE which is reported without quotation marks as in the examples:
A2; (A2 + A4); "This is the Greatest Value"; FALSE
The function SE gives us the possibility of combining it with the function E ou OU. Let's assume that we need to have several logical tests, and all the true results to return the value_is true, for this we can combine the function SE with the function E. Or that we need only a true value, in this case we can combine the function SE with the function OU. Let's use these functions within the function SE No. logical_test. See the examples:
= SE (E (A2 <10; B2 <20); "Lower price than all competitors"; "Higher price than competitors")
= SE (OR (A2 <10; B2 <20); "Low price"; "High price")
Note that the equal sign appears only before the function SE and does not follow the other functions.
The nested SE is the use of the SE function within the SE function. We can use nested SE when we have two or more distinct logical tests to perform. Let's suppose that if the student took an average> than 7 it is approved, but its faults can also not be greater than 6.
To solve this situation, our function would look like this:
In the first logical_test we will check if the student has passed the average, then if approved by the average, we will insert another function SE No. value_is true and test if it has not failed for faults, which would look like this:
= SE (B2> = 7;SE (C2 <= 6; "Approved"; "Disapproved");"Disapproved")
We can also nest other functions SE always in value_is true of each one or even in the value_se_false according to the necessity.
Already the nested functions are not limited to the function SE, we can insert another function in the value_is true as well as in PROCH ou PROCV, among other applications.
Were these tips helpful to you? Want to learn other Excel features? Keep following our blog to find out everything you can do with this tool!