The logical IF operator in Excel is used to record certain conditions. Numbers and / or text, functions, formulas, etc. are matched. When the values meet the specified parameters, then one record appears. Do not answer - another.
Logic functions are a very simple and effective tool that is often used in practice. Let's take a closer look at examples.
The syntax for the IF function with one condition
The syntax of an operator in Excel is the structure of a function, the data required for its operation.
IF (boolean_expression; value_if_true; value_if_false)
Let's analyze the syntax of the function:
Boolean_expression- WHAT the operator checks (text or numeric data of the cell).
Value_if_true- WHAT will appear in the cell when the text or number meets the specified condition (true).
Value if_false- WHAT will appear in the graph when the text or number DOES NOT meet the specified condition (false).
Example:
The operator checks cell A1 and compares it to 20. This is boolean_expression. When the content of the column is more than 20, the true inscription "greater than 20" appears. No - "less than or equal to 20".
Attention! Words in the formula must be enclosed in quotation marks. For Excel to understand that it needs to output text values.
One more example. To be admitted to the exam, group students must successfully pass the test. We will put the results in a table with columns: list of students, credit, exam.
Please note: the IF statement should check not a digital data type, but a text one. Therefore, we have prescribed B2 = "credits" in the formula. We put it in quotes so that the program recognizes the text correctly.
IF function in Excel with multiple conditions
Often, in practice, one condition for a logical function is not enough. When it is necessary to take into account several options for making decisions, we lay out the IF operators in each other. Thus, we get several IF functions in Excel.
The syntax will look like this:
IF (boolean_expression; value_if_true; IF (boolean_expression; value_if_true; value_if_false))
Here the operator checks two parameters. If the first condition is true, then the formula returns the first argument, which is true. False - the operator checks the second condition.
Examples of multiple conditions for the IF function in Excel:
Performance analysis table. The student received 5 points - "excellent". 4 - "good". 3 - "satisfactory". The IF operator tests 2 conditions: equality of the value in cell 5 and 4.
Expansion of functionality using operators "AND" and "OR"
When you need to check several true conditions, the AND function is used. The essence is as follows: IF a = 1 AND a = 2 THEN the value in ELSE is the value of c.
The OR function tests condition 1 or condition 2. As soon as at least one condition is true, the result will be true. The bottom line is: IF a = 1 OR a = 2 THEN the value in the ELSE value c.
The AND and OR functions can test up to 30 conditions.
An example of using the AND operator:
An example of using the OR function:
How to compare data in two tables
Users often have to compare two tables in Excel for matches. Examples from "real life": compare the prices of goods in different deliveries, compare balances (accounting reports) for several months, the progress of pupils (students) in different classes, in different quarters, etc.
To compare 2 tables in Excel, you can use the COUNTIF operator. Let's look at the order in which the function is applied.
For example, let's take two tables with the technical characteristics of different food processors. We conceived of highlighting the differences with color. Conditional formatting solves this problem in Excel.
Initial data (tables with which we will work):
Select the first table. Conditional Formatting - Create Rule - Use a formula to define the cells to format:
In the formula bar we write: = COUNTIF (the compared range; the first cell of the first table) = 0. The range being compared is the second table.
To drive a range into the formula, simply select its first cell and the last. "= 0" means a command to find exact (not approximate) values.
We choose a format and set how the cells will change when the formula is observed. Better to fill it with color.
Select the second table. Conditional Formatting - Create Rule - Use Formula. We apply the same operator (COUNTIF).
Here, instead of the first and last cell in the range, we inserted the column name that we assigned to it in advance. You can fill out the formula in any of the ways. But the name is easier.
Function in Excel Is a preset formula that performs calculations using specified values in a specific order. Excel uses many different functions for all occasions. Functions can be used to speed up tasks, simplify formulas, and implement calculations that would not have been possible without using them. In this lesson, you will learn what a function is, what its syntax and arguments are.
Excel contains a number of simple functions that can be useful for quickly finding sums, averages, maximum and minimum values, and for calculating data. To use functions correctly, you need to understand their syntax, i.e. write rule.
Excel function syntax
To work correctly, the function must be written in a specific sequence, which is called syntax. The basic syntax for a function is an equal sign (=), a function name (such as SUM), and one or more arguments. Arguments contain information to be calculated. The following example function sums the values in the range A1: A20.
There are functions in Excel that do not contain any arguments. For example, the function TODAY() returns the current date from your computer's system time.
Working with arguments
Arguments can refer to both individual cells and ranges of cells and must be enclosed in parentheses. Excel functions can use one argument or more, depending on the syntax.
For example, the function = AVERAGE (B1: B9) will calculate the average over the range of cells B1: B9. This function contains only one argument.
Multiple arguments must be separated by semicolons. For example, the function = SUM (A1: A3; C1: C2; E2) sums the values of all cells in three arguments.
Functions represent the dependence of one element (result) on other elements (arguments, .. those inside :-)). It's kind of understandable. In order to use any function in, you must enter it as a formula (nuances are described) or as part of a formula in a cell of a worksheet.
The sequence in which the symbols and arguments used in the formula should appear is called function syntax... All functions use the same syntax rules. If you violate these rules, Excel will display a message that there is an error in the formula and will not be friends with you. But believe me, in Excel functions everything is quite the same, and once you figure it out, on one or two functions, in other cases everything will be quite simple.
Syntax rules for writing functions
If a function appears at the very beginning of a formula, it must be preceded by an equal sign, as is the case at the beginning of any formula. I already talked about this in previous articles, but it's not a sin to repeat it.
After that, the name of the function is entered, followed immediately by the list of arguments in parentheses. The arguments are separated from each other by a semicolon ";". The parentheses allow Excel to tell where the argument list starts and ends.
Note that the function entry must contain opening and closing parentheses, and you cannot insert spaces between the function name and parentheses. Otherwise Excel will display an error message.
You can use numbers, text, booleans, arrays, error values, or references as arguments. In this case, the parameters specified by the user must have valid values for the given argument.
For example, the formula below sums the values in cells B2, B3, B4, B5, and E7, with some of the cells from B2 to B5 represented as a continuous range.
Consider the operation of the ROUND (arg1; arg2) function, which returns a number rounded to the specified number of decimal places and has two arguments:
arg1 is the address of the cell with the number (or the number itself) to be rounded;
arg2 - the number of digits after the decimal point in the number after rounding.
To round the number 2.71828 in cell A1 to one, two or three decimal places and write the results of the calculations to cells B1, C1 and D1, respectively, proceed as follows.
- Enter the number 2.71828 in cell A1.
- Enter the following formulas in cells B1, C1 and D1:
ROUND (A1; 1)
ROUND (A1, 2)
ROUND (A1, 3)
Arguments can be both constants and functions. Functions that are arguments to another function are called nested.
For example, let's sum up the values of cells A1 and A2, having previously rounded these values to two decimal places:
SUM (ROUND (A1,2); ROUND (A2,2))
Here the ROUND function is nested two times, but that's okay, in Excel formulas you can use up to seven levels of nesting functions.
It is worth noting that there are functions in Excel that have no arguments. Examples of such functions are PI (returns the value of π rounded to 15 digits) or TODAY (returns the current date). When using such functions, in the formula bar, immediately after the name of the function, put empty parentheses with no arguments... In other words, to get the value of the number p or the current date in the cells, you should enter formulas of the following form:
TODAY()
Excel function types
For the convenience of the user when building formulas, functions in Excel are divided into categories: database and list management functions, date and time functions, financial, statistical, textual, mathematical, logical.
Text functions are used for text processing, namely: searching for the desired characters, writing characters to a strictly defined place in the text, etc.
Via Date and Time functions you can solve almost any problem related to calendar dates or times (for example, calculate the number of working days for any period of time).
Logic functions are used to create complex formulas that, depending on the fulfillment of certain conditions, will implement various types of data processing. They are especially interesting, and we will talk about them in a separate article.
Excel is widely represented Math functions and I have already given some in examples.
The user also has a library at his disposal. Statistical functions, with which you can search for the average value, maximum and minimum elements, etc.
Arguments Is information that a function uses to compute a new value or perform an action. Arguments are always to the right of the function name and are enclosed in parentheses. Most of the arguments are of a specific type. In fact, the argument you supply must either be of a suitable type, or a type that Microsoft Excel can convert to a suitable type.
Function arguments can be numeric values, cell references, ranges, names, text strings, and nested functions.
If, when describing the syntax of a function, an argument is followed by an ellipsis (...), this means that there can be several arguments of the same type. Some functions can take up to 30 arguments, provided that the total number of characters in the formula does not exceed 1024. For example, the syntax for the MAX function is as follows:
MAX (number1; number2; ...)
Any of the following formulas are valid:
MAX (26; 31; 29)
Functions with empty parentheses after the name do not require arguments, however, you must include those empty parentheses in the formula for Microsoft Excel to recognize the function.
Many of the argument names in the syntax description of a function hint at what information should be supplied as the actual argument value. For example, the function ROUND (number; num_digits) must have the first argument a number and the second must also be a number.
Likewise, words such as number, reference, flag, text, array, if used as an argument name, indicate that the argument must be of the appropriate type. Word Meaning implies that the argument can be anything that is a single value. That is, the value can be a number, text, boolean, or error value.
Using arguments
The argument can be anything that delivers a value of the required type. For example, a SUM function that sums up its arguments can accept 1 to 30 arguments. The SUM function can be passed arguments of any of the following four kinds, as long as they deliver a number or numbers:
- a value that is a number, for example: SUM (1; 10; 100);
- a formula that results in a number, for example: SUM (0.5 + 0.5; AVERAGE (5; 5); 10 ^ 2)... Functions that are used as arguments to other functions, as in the previous example, are called nested functions. In this example, the function AVERAGE is a function argument SUM... The nesting level of functions in formulas can be up to seven;
SUM(A1; A2)
SUM (A1: A5)
The second example is equivalent to the formula SUM (A1; A2; A3; A4; A5)... The advantage of using an interval is that the argument A1: A5 counts as one argument, while A1, A2, A3, A4, A5 count as five arguments. If you need to add more than 30 numbers, then you will have to use intervals, because the function cannot have more than 30 arguments;
- a name that refers to a value, formula, cell, or range of cells containing numbers or formulas delivering numbers, for example: SUM (Base; Increment).
Argument types. Function arguments can be any of the following objects.
Numbers. Examples of numbers are 5.003, 0, 150.286, and -30.05. Numbers without a decimal point are called whole numbers. Examples of integers are 5, 0, 150, and -30. Numbers can have up to 15 significant digits.
Text. Examples of texts are "a", "Word", "mark / punctuation" and "" (empty text). Text values used in formulas must be enclosed in double quotes. If the text itself contains double quotes, then they should be doubled. For example, to determine the length (in characters) of text "in the" good "old days, you can use the formula:
DLSTR ("in" "good" "old time")
Text values can be up to 32,000 characters long, counting double quotes. A text constant that does not contain characters is written like this: "" and is called "empty text".
Note . If the text used as an argument is not enclosed in double quotes, then Microsoft Excel assumes that this is a name and tries to substitute the value that the name refers to. If the unquoted text is not a name, and therefore does not matter, Microsoft Excel will return an error value #NAME?.
Boolean values. Boolean values are TRUE and FALSE. Boolean arguments can also be expressions such as B10> 20, whose values are TRUE or FALSE.
Error values. The error values are # DIV / 0 !, # N / A, # NAME ?, # EMPTY !, # NUMBER !, # LINK! and #VALUE!.
Links. Examples of links are $ A $ 10, A10, $ A10, A $ 10, R1C1 or RC [-10]... References can point to single cells, cell ranges, or multiple cell selections, and can be relative, absolute, or mixed. If the link is used as an argument, which must be a number, text, Boolean, or error value, then the actual argument is the contents of the cell specified by the link.
SUM ((E5: E8; E10: E18); AVERAGE (A1: A5))
Arrays. Arrays allow you to control how arguments and functions are entered into cells. Using arrays makes it easier to design some of the worksheet formulas and saves memory. Microsoft Excel defines two types of arrays: array ranges and constant ranges. An array range is a contiguous range of cells that use a common formula; a constant range is a collection of constants used as arguments to functions.
Using semicolons in argument lists
Individual arguments must be separated by semicolons, but there must be no extra semicolons. If a semicolon is used only to mark the location of an argument, and the argument itself is not specified, then Microsoft Excel substitutes a default value for that argument, unless the argument is required. For example, if you enter (; arg2; arg3) as the argument list for a function with three arguments, Microsoft Excel substitutes the appropriate value for arg1. If you enter (arg1 ;;), then suitable values will be substituted for arg2 and arg3.
For functions that count the number of arguments before being evaluated, extra semicolons will be counted when counting the number of arguments and therefore will affect how the function's value is calculated. For instance, AVERAGE (1; 2; 3; 4; 5) is equal to 3 but AVERAGE (;; 1; 2; 3; 4; 5) is 2.14.
For most arguments, the value substituted for an omitted argument is 0, FALSE, or "" (empty text), depending on what the type of the argument should be. For an omitted reference argument, the default is usually the active cell or selection.
The IF function is the most popular among Excel boolean functions, it returns one value if the specified condition results in TRUE, and another value if the condition results in FALSE.
Description of the IF function
The IF function returns one value if the specified condition evaluates to TRUE and another value if the condition evaluates to FALSE. For example, the formula = IF (A1> 10, "Greater than 10", "10 or less")
returns the string Greater than 10 if the value in cell A1 is greater than 10, and 10 or less if it is less than or equal to 10.
Syntax
= IF (logical_test, [value_if_true], [value_if_false])Arguments
log_expression value_if_truevalue_if_false
Required argument. Any value or expression that results in TRUE or FALSE. For example, A10 = 100 is a boolean expression; if the value in cell A10 is 100, this expression evaluates to TRUE; otherwise, it evaluates to FALSE. Any comparison operator can be used in this argument.
Optional argument. The value that is returned if logical_test is TRUE. For example, if the given argument is the string "Within budget" and the log_test argument is TRUE, the IF function returns the text "Within budget." If boolean_test is TRUE and value_if_true is omitted (that is, there is only a comma after log_test), the value 0 is returned. To display the word TRUE, use Boolean TRUE as the argument.
Optional argument. The value that is returned if log_test is FALSE. For example, if the given argument is the string "Over budget" and log_test is FALSE, the IF function returns the text "Over budget". If boolean_test is FALSE and value_if_false is omitted (that is, there is no comma after value_if_true), the IF function returns the boolean value FALSE. If boolean_test is FALSE and value_if_false is empty (that is, there is only a comma after value_if_true), IF returns 0 (zero).
Remarks
- You can use up to 64 nested IF functions to create more complex checks for value_if_true and value_if_false arguments (see example 3 for using nested IF functions). In addition, LOOKUP, VLOOKUP, HLOOKUP, and SELECT can be used to check multiple conditions (see example 4 for the VIEW function).
- If any of the arguments to the IF function are an array, the IF function evaluates all the elements of the array.
- Excel provides additional functionality that you can use to analyze data using conditions. For example, to count the number of occurrences of a text string or number in a range of cells, use the COUNTIF and COUNTIFS worksheet functions. Use the SUMIF and SUMIFS functions to calculate the sum of values that fall within the range specified by a text string or numbers.
