How to set greater than or equal to in Excel. Excel functions if (if) and ifs (ifs) for several conditions
Example
Technical details
Function NOT reverses the value of its argument.
Usually the function NOT used to extend the capabilities of other functions that perform logical testing. For example, the function IF performs a logical test and returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. Using the function NOT as argument "log_expression" of the function IF allows you to check several different conditions instead of just one.
Syntax
NOT(boolean_value)
Function Arguments NOT are described below.
Boolean_value Required. A value or expression that evaluates to TRUE or FALSE.
Notes
If boolean_value is FALSE, the function NOT returns TRUE; if it evaluates to TRUE, the function NOT returns FALSE.
Examples
Below are some common examples of how to use the function: NOT both separately and in combination with functions IF, AND And OR.
Formula | Description |
|---|---|
|
NOT(A2>100) |
A2 NOT more than 100 |
|
IF(AND(NOT(A2>1),NOT(A2<100));A2;"Значение вне допустимого диапазона.") |
50 is greater than 1 (TRUE) AND is less than 100 (TRUE), so the function NOT changes both arguments to FALSE. To function AND returned TRUE, both of its arguments must be true, so in this case it returns FALSE. |
|
IF(OR(NOT(A3<0);НЕ(A3>50)); A3; "Value outside the interval") |
100 is not less than 0 (FALSE) and greater than 50 (TRUE), so the function NOT changes the argument values to TRUE and FALSE. To function OR returned TRUE, at least one of its arguments must be true, so in this case it returns TRUE. |
Commission calculation
Below is a solution to a fairly common problem: using functions NOT, IF And AND determines whether the sales employee has earned a bonus.
=IF(AND(NOT(B14<$B$7);НЕ(C14<$B$5));B14*$B$6;0) – IF total sales NOT less target AND number of contracts NOT less than the target, total sales are multiplied by the premium percentage. Otherwise, 0 is returned.
additional information
You can always ask a question in the Excel Tech Community, ask for help in the Answers community, or suggest a new feature or improvement on the Excel User Voice website.
See also:
Video: additional features
if in the formula
functions, functions and functions are described,
Function IF (IF) in Excel is a great tool for testing conditions for TRUE or FALSE. If the values of your calculations are equal to the specified parameters of the function as TRUE, then it returns one value, if FALSE, then another.
What does the function return?
The value you specify when two conditions are true is TRUE or FALSE.
Syntax
=IF(logical_test, , )- English version
=IF(logical_expression; [value_if_true]; [value_if_false]) - Russian version
Function Arguments
- logical_test (logical_expression) is the condition you want to test. This function argument must be logical and evaluate to FALSE or TRUE. The argument can be either a static value or the result of a function or calculation;
- ([value_if_true])– (optional) – this is the value that the function returns. It will be displayed if the value you are testing meets the TRUE condition;
- ([value_if_false])– (optional) – this is the value that the function returns. It will be displayed if the condition you are testing matches the FALSE condition.
Additional Information
If function in Excel examples with multiple conditions
Example 1: Testing a simple numeric condition using the IF function
When using the function IF (IF) in Excel, you can use various operators to check the status. Here is a list of operators you can use:
Below is a simple example of using the function to calculate student grades. If the sum of points is greater than or equal to “35”, then the formula returns “Passed”, otherwise it returns “Failed”.
Example 2: Using a Nested IF Function to Test the Condition of an Expression
The function can accept up to 64 conditions at a time. Although it is not practical to create long nested functions, in rare cases you can create a formula that has multiple conditions sequentially.
In the example below we are checking two conditions.
- The first condition checks whether the sum of points is less than 35 points. If this is TRUE, then the function will return “Failed”;
- If the first condition is FALSE and the sum of points is greater than 35, then the function checks the second condition. If the total score is greater than or equal to 75. If this is true, then the function returns the value “Excellent”, in other cases the function returns “Pass”.
Example 3: Calculate the amount of sales commission using the IF function in Excel
The function allows you to perform calculations with numbers. A good example of use is calculating sales commission for a sales representative.
In the example below, the sales representative:
- does not receive commissions if sales volume is less than 50 thousand;
- receives a commission of 2% if sales are between 50-100 thousand
- receives 4% commission if sales volume exceeds 100 thousand.
=IF(B2<50,0,IF(B2<100,B2*2%,B2*4%)) - English version
=IF(B2<50;0;ЕСЛИ(B2<100;B2*2%;B2*4%)) - Russian version
In the formula used in the example above, the calculation of the commission amount is performed in the function itself IF. If the sales volume is between 50-100K, then the formula returns B2 * 2%, which is a 2% commission based on the sales volume.
Example 4. Using logical operators (AND/OR) in the IF function in Excel
You can use Boolean operators (AND/OR) inside a function to test multiple conditions at the same time.
For example, let's say you have to select students for scholarships based on grades and attendance. In the example below, a student is only eligible if they have scored more than 80 points and have more than 80% attendance.
You can use a function together with a function IF (IF) to first check whether both of these conditions are met or not. If the conditions are met, the function returns “Eligible”, otherwise it returns “Not Eligible”.
The formula for this calculation is:
=IF(AND(B2>80,C2>80%),”Yes”,”No”)- English version
=IF(AND(B2>80;C2>80%);”Yes”;”No”) - Russian version
Example 5. Convert errors to “0” values using the IF function (IF)
Using this function, you can also remove cells containing errors. You can convert error values to spaces or zeros or any other value.
The formula for converting errors in cells is as follows:
=IF(ISERROR(A1),0,A1)- English version
IF(ISERROR(A1),0,A1)- Russian version
The formula returns “0” if there is an error in the cell, otherwise it returns the value of the cell.
NOTE. If you are using Excel 2007 or versions after it, you can also use the IFERROR function for this.
You can handle empty cells in the same way. In case of empty cells, use the ISBLANK function, in the example below:
=IF(ISBLANK(A1),0,A1)- English version
=IF(EMPLANTY(A1),0,A1)- Russian version
Testing the truth of conditions and logical comparisons between expressions, which are common to many problems. To create conditional formulas, you can use the " And", "," And " If ".
For example, the function If uses the following arguments.
Formula using the if function
Logical expression: the condition to be tested.
Value_if_true: Return value if the condition is true.
Value_if_false: Return value if the condition is false.
For more information about how to create formulas, see Create and delete formulas.
In this article
Creating a conditional formula that results in a Boolean value (true or false)
To accomplish this task, use the and functions and operators. or And Not
Example
Copying an example
Important:
Formulas in Group Formula dependencies click the button Show formulas.
|
|
For more information on how to use these features, see Features and Features rather than Features.
Creating a conditional formula that results in a different calculation or values other than true or false
To accomplish this task, use functions and operators If, and and or, as shown in the following example.
Example
To make this example easier to understand, copy it onto a blank sheet of paper.
Copying an example
Highlight the example provided in this article.
Important: Do not highlight row or column headings.
Highlighting an example in help
Press CTRL+C.
In Excel, create a blank workbook or sheet.
Select cell A1 on the sheet and press CTRL+V.
Important: For the example to work correctly, it must be inserted into cell A1.
To switch between viewing the results and viewing the formulas that return those results, press CTRL+` (accent mark) or on the tab Formulas in Group Formula dependencies click the button Show formulas.
By copying the example onto a blank sheet, you can customize it to suit your needs.
|
|
For more information about using these functions, see the articles Function and Function.
Boolean expressions are used to write conditions that compare numbers, functions, formulas, text, or Boolean values. Any logical expression must contain at least one comparison operator, which defines the relationship between the elements of the logical expression. Below is a list of Excel comparison operators
>= Greater than or equal to
The result of a logical expression is the logical value TRUE (1) or the logical value FALSE (0).
IF function
The IF function has the following syntax:
=IF(logical_expression, value_if_true, value_if_false)
The following formula returns 10 if the value in cell A1 is greater than 3, and 20 otherwise:
IF(A1>3,10,20)
You can use other functions as arguments to the IF function. The IF function can use text arguments. For example:
IF(A1>=4;"Passed the test","Failed the test")
You can use text arguments in the IF function so that if the condition is not met, it will return an empty string instead of 0.
For example:
IF(SUM(A1:A3)=30,A10,"")
The boolean_expression argument of the IF function can contain a text value. For example:
IF(A1="Dynamo";10;290)
This formula returns 10 if cell A1 contains the string "Dynamo" and 290 if it contains any other value. The match between the text values being compared must be exact, but not case sensitive.
Functions AND, OR, NOT
Functions AND (AND), OR (OR), NOT (NOT) - allow you to create complex logical expressions. These functions work in conjunction with simple comparison operators. The AND and OR functions can have up to 30 Boolean arguments and have the syntax:
=And(boolean_value1,boolean_value2...)
=OR(boolean_value1, boolean_value2...)
The NOT function has only one argument and the following syntax:
=NOT(boolean_value)
Arguments to the AND, OR, and NOT functions cannot be Boolean expressions, arrays, or cell references containing Boolean values.
Let's give an example. Let Excel return the text "Passed" if the student has a GPA greater than 4 (cell A2) and a class absence rate less than 3 (cell A3). The formula will look like:
=IF(AND(A2>4,A3
Even though the OR function has the same arguments as the AND function, the results are completely different. So, if in the previous formula we replace the AND function with OR, then the student will pass if at least one of the conditions is met (average score more than 4 or absenteeism less than 3). Thus, the OR function returns the logical value TRUE if at least one of the logical expressions is true, and the AND function returns the logical value TRUE only if all the logical expressions are true.
The function does NOT reverse the value of its argument to the opposite boolean value and is usually used in combination with other functions. This function returns the logical value TRUE if the argument is FALSE and the logical value FALSE if the argument is TRUE.
Nested IF Functions
Sometimes it can be very difficult to solve a logic problem using only comparison operators and AND, OR, NOT functions. In these cases, you can use nested IF functions. For example, the following formula uses three IF functions:
=IF(A1=100,"Always";IF(AND(A1>=80;A1 =60;A1
If the value in cell A1 is an integer, the formula reads: "If the value in cell A1 is 100, return the string "Always." Otherwise, if the value in cell A1 is between 80 and 100, return "Usually." otherwise, if the value in cell A1 is between 60 and 80, return the row "Sometimes." And, if neither of these conditions are true, return the row "Never." A total of 7 levels of nesting of IF functions are allowed.
Functions TRUE and FALSE
The TRUE and FALSE functions provide an alternative way to write the Boolean values TRUE and FALSE. These functions have no arguments and look like this:
=TRUE()
=FALSE()
For example, cell A1 contains a Boolean expression. Then the following function will return the value "Pass" if the expression in cell A1 evaluates to TRUE:
IF(A1=TRUE();"Pass";"Stop")
Otherwise, the formula will return "Stop".
EMPTY function
If you need to determine whether a cell is empty, you can use the ISBLANK function, which has the following syntax:
=EBLANK(value)
There are many different functions in Excel that work by checking logical conditions. For example, these are the functions IF, COUNTIF, SUMIF, etc. Logical conditions can also be specified in regular formulas if you need to get an affirmative answer: Yes or No. For example, by asking simple logical conditions, you can answer the following questions:
- 5 is more than 8?
- Is the content of cell A5 less than 8?
- Or maybe equal to 8?
Comparison Operators in Excel
Excel has a number of standard operators that are used to set simple Boolean conditions. All six possible comparison operators are shown in the table below:
How to set a condition in Excel
Comparison operators allow you to specify conditions that return the Boolean values TRUE or FALSE. Examples of using logical conditions are presented below:
=A1=B1– This condition will return TRUE if the values in cells A1 and B1 are equal, or FALSE otherwise. By setting this condition, you can compare text strings in a case-insensitive manner. For example, comparing “JANUARY” and “January” the formula will return TRUE.
=A1>B1– The following formula will return TRUE if the value in cell A1 is greater than that in B1. Otherwise, the formula will return FALSE. Such comparisons can also be set when working with text.
For example, if cell A1 contains the value “Orange” and B1 contains “Watermelon,” the formula will return FALSE because “Watermelon” is lower in alphabetical order than “Orange.” The lower, the more.
=A1<=B1 – The formula will return TRUE if the value in cell A1 is less than or equal to the value in cell B1. Otherwise the result will be FALSE.
=A1<>B1– The formula will return TRUE if the values of cells A1 and B1 are not equal. Otherwise - FALSE.
There are logical functions in Excel TRUE() And LIE(), which have no arguments. These features exist primarily to provide compatibility with other spreadsheets. You can enter TRUE and FALSE values directly into cells or formulas without using the function notation form, Excel will understand everything just fine.
If you are confident that you have already mastered this topic well enough, you can refer to the article Using Excel's logical functions to set complex conditions to learn how to set conditions using various logical functions, for example AND() or OR().