+3 votes
38 views
in Sell ​​on the Internet by (76.4k points)
reopened
How to use the IF-O function in Excel

1 Answer

+4 votes
by (506k points)
edited
 
Best answer

image

How to use the IF-O function in Excel

Most Microsoft users familiar with the Excel spreadsheet already know and use the popular IF function, which is used to create queries by specifying one or more conditions to obtain a specific result. If you combine the IF and OR functions, just one of the conditions is true to display a result. We explain here how to do it with simple examples..

Index
  1. What is the Excel SI-O function?
  2. Excel SI-O: practical example of combined functions
  3. Combine SI-O function

What is the Excel SI-O function?

Excel's IF-OR function is an extension of the IF function , also known as the IF-THEN function. Thanks to its enormous versatility, it is one of the most used Excel functions. It is used to check if a certain value corresponds to an expected value. If both values ​​are identical, a predefined state is activated. If not, another action occurs. To define whether a result is true or false, you must first set the conditions.

If you combine the IF function with the OR function , you have the possibility to establish several conditions. For the result to be TRUE, at least one of them must match the expected result. If conditions A or B are met, state C occurs. If neither of the two conditions is met, state D appears. The formula has the following structure:

 

  =SI(O(algo es VERDADERO;algo diferente es VERDADERO);valor si es VERDADERO;valor si es FALSO)  

In the above formula, the element? Something is TRUE? is condition A and? something different is TRUE? is condition B. Therefore, the? value if TRUE? corresponds to condition C and the? value if FALSE ?, to condition D..

Note

An alternative to the IF-OR function is the IF-AND function. With this formula, both conditions A and B must be true for state C. to appear. The related function YES-NO, on the other hand, only accepts a single condition.

Excel SI-O: practical example of combined functions

In the following table, column A lists the possible participants in a training program. To enroll in the educational center, people must have attended a preliminary basic course or have passed the entrance exam with a minimum of 600 points:

image
This Excel example allows you to see which of the participants have been admitted.

Since the conditions are clear, you can write the formula in the next step. First, select cell D2 . In this, the question about admission is answered with YES or NO. The YES status occurs if a participant has completed the basic course (value? Yes? In column B) or if they obtained more than 600 points in column C. The answer does NOT appear if neither condition is met. Now, enter the following formula in the edit bar:

  =SI(O(B2="Sí";C2>599);"SÍ";"NO")  

After confirming the formula with the return key, Excel displays the result in D2:

image
The answer? NO ?, appears in cell D2, since neither of the two conditions is met.

To also fill rows 3 through 6 of column D with the corresponding results, you do not need to re-enter the formula. Just mark cell D2 and drag the green frame to the last cell. Since the formula expresses a relative reference , B2 and C2 are automatically converted to B3 and C3..

image
To automatically fill the cells, drag the green frame to the last row.

Combine SI-O function

The SI-O function has the advantage that it can be combined with other very useful functions, allowing other more complex queries to be carried out. The above examples are listed below in a table. In this case, the names and surnames of the possible participants are used in columns A and B:

image
Table with the names and surnames of the participants and a personalized phrase in column E

In column E, do you want only the YES or NO answers to appear, but the complete sentence? [Name] [Surname] has been accepted? or? [Name] [Surname] has not been accepted ?. For this, you must integrate the CONCATENATE function in the SI-O function. Select cell E2, enter the following formula in the edit bar and drag it to the rest:

 

  =CONCATENAR(A2;" ";B2; " ";SI(O(C2="Sí";D2>599);"ha sido aceptado/a";"no ha sido aceptado/a"))  

The first part of the formula concatenates the contents of cells A and B. To prevent text elements from being inserted directly next to each other in the result, insert a blank space between quotation marks after A2 and B2. You then enter the IF-O function, which completes the sentence with the corresponding result.

image
Combined with the CONCATENATE function, the IF-O function generates complete sentences.

Excel's SI-O function can be combined with many other functions as you need. However, make sure to put the correct amount of parentheses when nesting formulas, as if you don't close all of them, you will get an error message. To avoid these errors, you can use the formula generator from the menu item? Insert function ?, Which automatically generates the correct syntax.


...