Excel is a popular spreadsheet editor that helps in solving many problems, both professional and domestic. Using tables, you can easily maintain some kind of bookkeeping or just make up convenient lists of something for yourself. But in this application, there are many more functions than just writing values to table cells. In this article, we analyze the logical function IF.
Function Syntax
First of all, you need to understand the syntax of the IF function in order to use it in practice. In fact, it is very simple and remembering all the variables is not difficult:
=(_;_;_)
Now, let’s take a look at the points above.
- "
=
" - the name of the function itself, which we will use; - "
_
" is the value to be checked. It can be entered both in numerical format and in text. - "
_
" - the value that will be displayed in the selected cell subject to the specified conditions in the "logical_expression". - "
_
" is the value that will be displayed if the conditions in the " _
" are not met.
If the syntax does not understand anything, then it’s okay, now it will be analyzed using an example.
Example IF function in Excel
To demonstrate an example of using this function, no complicated tables need to be created. We need only two cells. Suppose we have the number “45” in the first cell, and in the second we should have a value that will depend on the value in the first. So, if in the first cell the number is more than 50, then in the second “brute force” will be displayed, if less or equal - “shortage”. Below is an image illustrating all of the above.
As you can understand, all variables can be changed by yourself. You can change both a logical expression and values that display FALSE or TRUE. Please note that if the text is used as the value, then for correct display it is necessary to quote it.
IF nested function example in Excel
We looked at an example of an ordinary IF function when only one logical value is used. This is convenient when you are dealing with just a few cells; to interact with a large range of data, it is better to use the IF function in Excel.
First, let's figure out what it is. Nested refers to a function in which several conditions are specified. In other words, several functions will be written in the cell at once IF in a row. Let's look at an example.
Suppose we have a table that lists the names of students and their scores for the exam. In accordance with these points, we need to write down the result, which is expressed in the phrases “excellent”, “good”, “satisfactory”, “unsatisfactory” and, accordingly, ratings “5”, “4”, “3” and “2”. In order not to fill in all the fields yourself, you can use the nested IF function in Excel. It will look like this:
As you can see, her syntax is similar to the original:
=(_;_;(_;_;_))
Please note that depending on the number of repeated functions IF the number of brackets closing at the end depends.
So it turns out that initially you set a logical expression equal to 5 points, and prescribe that in the cell with the formula, if it matches, the word "excellent" should be displayed, and in the second part of the formula, indicate grade 4 and write that it is "good", and in For FALSE, write satisfactory. As a result, you can only select the formula and stretch it over the entire range of cells for the small square located in the lower right corner.
This is how you can simply use the IF function in Excel to work with a wide range of cells. But this is not all the possibilities of this function, so we move on.
Functional Extension IF
Along with the IF function, you can use AND or OR. This approach makes it possible to specify several logical values at once. The syntax of this converted IF function is great, for example, for finding equal values in a matrix of numbers. Let's analyze the use of such an IF function in Excel using an example.
We have entered numbers in the range of 3 by 3 cells. Some rows have the same meaning. Suppose we want to find out which ones. In this case, we write in the formula:
=((A1=B1;B1=C1;A1=C1); ; )
That is, we indicated that if there are identical values in at least two cells of the same row, then the formula should output the variable “there are equal values”, otherwise it will output “no equal values”.
Conclusion
So we have disassembled the IF function in Excel. Of course, it provides much more opportunities, in the article we tried only to explain the principle of its work. In any case, you can move away from the shown use cases and experiment with other syntax constructs.
We hope this article has been helpful to you.