How to Use IF, Else IF, AND Function Conditions in Excel

The logical IF statement in Excel is used for the recording of certain conditions. It compares the number and / or text, function, etc. of the formula when the values correspond to the set parameters, and then there is one record, when do not respond - another.

Logic functions - it is a very simple and effective tool that is often used in practice. Let us consider it in details by examples.



The syntax of the function «IF» with one condition

The operation syntax in Excel is the structure of the functions necessary for its operation data.

=IF(boolean;value_if_TRUE;value_if_FALSE)

Let us consider the function syntax:

  • Boolean – what the operator checks (text or numeric data cell).
  • Value_if_TRUE – what will appear in the cell when the text or numbers correspond to a predetermined condition (true).
  • Value_if_FALSE – what appears in the box when the text or the number does not meet the predetermined condition (false).

Example:

Example.

Logical IF functions.

The operator checks the A1 cell and compares it to 20. This is a «Boolean». When the contents of the column is more than 20, there is a true legend «greater 20». In the other case it’s «less or equal 20».

Attention! The words in the formula need to be quoted. For Excel to understand that you want to display text values.

Here is one more example. To gain admission to the exam, a group of students must successfully pass a test. The results are listed in a table with columns: a list of students, a credit, an exam.

list.

The statement IF should check not the digital data type but the text. Therefore, we prescribed in the formula В2= «done» We take the quotes for the program to recognize the text correctly.



The function IF in Excel with multiple conditions

Usually one condition for the logic function is not enough. If you need to consider several options for decision-making, spread operators’ IF into each other. Thus, we get several functions IF in Excel.

The syntax is as follows:

Here the operator checks the two parameters. If the first condition is true, the formula returns the first argument is the truth. False - the operator checks the second condition.

Examples of a few conditions of the function IF in Excel:

few conditions.

It’s a table for the analysis of the progress. The student received 5 points:

  • А – excellent;
  • В – above average or superior work;
  • C – satisfactory;
  • D – a passing grade;
  • E – completely unsatisfactory.

IF statement checks two conditions: the equality of value in the cells.

two conditions.

In this example, we have added a third condition, which implies the presence of another report card and "twos". The principle of the operator is the same.

Enhanced functionality with the help of the operators «AND» and «OR»

When you need to check out a few of the true conditions you use the function И. The point is: IF A = 1 AND A = 2 THEN meaning в ELSE meaning с.

OR function checks the condition 1 or condition 2. As soon as at least one condition is true, the result is true. The point is: IF A = 1 OR A = 2 THEN value B ELSE value C.

Functions AND & OR can check up to 30 conditions.

An example of using the operator AND:

operator AND.

It’s the example of using the logical operator OR.

example of using OR.

How to compare data in two tables

Users often need to compare the two spreadsheets in an Excel to match. Examples of the "life": compare the prices of goods in different bringing, to compare balances (accounting reports) in a few months, the progress of pupils (students) of different classes, in different quarters, etc.

To compare the two tables in Excel, you can use the COUNTIFS statement. Consider the order of application functions.

For example, consider the two tables with the specifications of various food processors. We planned allocation of color differences. This problem in Excel solves the conditional formatting.

Baseline data (tables, which will work with):

tables.

Select the first table. Conditional Formatting - create a rule - use a formula to determine the formatted cells:

formatted cells.

In the formula bar write: = COUNTIFS (comparable range; first cell of first table)=0. Comparing range is in the second table.

To drive the formula into the range, just select it first cell and the last. "= 0" means the search for the exact command (not approximate) values.

Choose the format and establish what changes in the cell formula in compliance. It’s better to do a color fill.

Select the second table. Conditional Formatting - create a rule - use the formula. Use the same operator (COUNTIFS). For the second table formula:

Download all examples in Excel

compare the characteristics.

Now it is easy to compare the characteristics of the data in the table.


en ru