The If function in Excel

Microsoft Excel has a powerful tool that can help you solve difficult computing tasks. One of the most used tools from this set is the "IF" function.

Function Value

When working in "Excel" it is necessary to understandvalue of the "IF" function to construct the correct syntactic queries. Due to its algorithm, a logical comparison is performed, depending on the results of which one of two actions will be performed.

In more simple terms, the function "IF" inthe case of the true value of an expression, performs one action, in the case of a false one, another. In this case, both the explicit value and a certain function can be used as actions, including "IF". Due to this in Excel, the function "IF" allows branching when executing some algorithm of actions for solving various tasks.

The syntax "IF"

A simple description of most syntacticdesigns - one of the main advantages that "Excel" is famous for. The function "IF" also refers to their number - after the keyword in parentheses, the condition, the action at the true value, and then at the false one are alternately indicated. In a schematic form it looks like this:

IF (log_expression; [value_if_true]; [value_if_long];

Nesting

One of the features that distinguishes the function"IF" is nesting. That is, within one design, there can be one more, on the value of which depends also the overall result of the query. In addition to the function itself, within the "IF" there may be others. But in the first case, this component can be located in any of the three parts of the syntactic structure.

Several conditions

When working with complex tasks, it is usedfunction "IF" with several conditions, however, at this stage most users have a problem. This is due to the specific task of the multi-ordering of the algorithm. In Excel, the function "IF" checks only one comparison operation in a logical expression, that is, it will not be possible to use a conjunction or disjunction. To test several conditions, you must use the nesting property.

To understand how to set several conditions in"IF", it is convenient to use an example. Let's check if the number in cell "A1" is in the given interval - from 5 to 10. As you can see, in this case it is required to check two conditions by checking the truth comparison with two values ​​- 5 and 10. To implement this example in "Excel", it is necessary to write the function in the following form:

= IF (A1> 5; IF (A1 <10; "enters the range;" "does not enter the range"); "not in the range")

To avoid multiple repetition of the outputphrases, it is worthwhile to apply the nesting principle one more time, as arguments, choosing to check the return value of the functions, depending on which and output, or at the very beginning use the "AND" function, combining all the conditions in it at once. This approach will complicate the understanding of the written construction with a small level of nesting, but with a significant number of conditions, this approach will be more optimal.

Special function options

It is worth noting that the function "IF" allowsleave one or more of their parameters blank. In this case, the results will depend on which arguments were omitted by the user.

If in place of a logical expression to leavevoid, the result of the function will be the execution of the action responsible for the false execution of the algorithm. The reason for this is the fact that the program associates an empty space with zero, which in logical language means "FALSE". If you leave empty one of the values ​​responsible for execution in the case of true or false, then when you select it, the result will be "0".

We should also note the case when instead ofThe logical expression introduced is not a construct that returns the value "TRUE" or "FALSE", but a certain set of characters or a reference to a cell. In the case when an expression is written as a parameter, containing something besides a numeric value or logical words, this will cause an error when executing the function. If you specify a cell address or write some number / logical value, the result will determine this content. When the number 0, the word "FALSE" or void is written in the cell or in the condition, the result will be a false execution of the function. In all other cases, the true action scenario will be executed.

When working with the English version of "Excel"It is necessary to take into account the fact that all functions are also written in English. In this case, the function "IF" will be written as IF, but otherwise the syntactic structure and algorithm of operation will remain the same.

What you should pay attention to

Excel allows you to use up to 64 nestedfunctions "IF" - this amount is enough to solve almost all problems, however, even this small number often becomes a problem for the user. There are several reasons for this: when creating a query, it is easy enough to make a mistake with writing a formula - according to statistics, each slightest inaccuracy in 25% of cases leads to an incorrect result, which is quite a large indicator.

Another disadvantage of the big IF nestingis low readability. Despite the program’s color highlighting of some parts of the request, even a few nested functions, which are very difficult to disassemble. Thus, if after some time you have to return to the structure or start working with someone else’s request, it will take a long time to understand the record. In addition, each function has its own pair of brackets, and accidentally putting it in the wrong place, you have to search for an error for a long time.

Examples

To consolidate understanding, it is worthwhile to consider in practice how the "IF" function works in Excel. The examples below demonstrate all the basic ways to use it.

The simplest example for parsing the functionis a comparison of two numbers. For the presence of variability, let us set the values ​​of two numerical variables in the cells A1 and B1, which we will compare with each other. To solve this problem, use the following entry:

= IF (A1 = B1; "numbers are equal"; "numbers are unequal").

In this case, if there are identical values ​​in both cells, the result will be the record "the numbers are equal", in all other cases - the "numbers are unequal."

To review the work of the conditional operator withseveral conditions, as an example, you can use the finding of the number of solutions of a quadratic equation. In this case, the test is performed on the discriminant - if it is less than zero, then there is no solution, if it is zero - it is one, in all other cases - there are two roots. To write this condition, it is enough to make a request of the following form:

For those who want to better understand all the possibilities possessed by the function "IF", Excel contains examples in the help section, which describes in detail the course of solving each of them.