Important points regarding Excel’s IF function
- The IF function in Excel can handle all types of data, including numbers, texts, dates, and logical values. However, text values must be enclosed in double “.
- To compare numbers in the IF function, you must use operators such as greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=) and in text values of special functions such as SEARCH, FIND or used EXACT.
- By using logical operators such as AND and OR, you can combine several different conditions into one IF statement.
- The nested IF function allows you to check a wide range of conditions simultaneously and evaluate more complex conditions.
First example: To better understand the IF function, let’s start with a simple example that is related to checking students’ grades and determining their pass and fail status. In this example, we put the names of the students in column A1 and the grades received in column B1; So, putting the data into the general formula, the function will look like this:
As you can see, based on this formula, if the value of B1 is greater than or equal to 10, it will be displayed as passed and otherwise rejected. You can type the formula in column C1 in the same way, or select Logical and then IF in the Formulas section.
In this case, in the first box, only the expression is enough B1>=10 and in the second and third lines respectively accept And rejected Enter and then OK.
Multiconditional Excel function
Second example: Using the IF function, more complex problems can be investigated. For example, suppose we want to check the status of people based on their age, income and amount of savings to get a mortgage. For this purpose, we will need several different conditions. In Excel, the AND function can be used to check several conditions at the same time using the IF function.
In short, the AND function is used to check multiple conditions at the same time and checks whether all the conditions are true by checking several different criteria. The structure of the AND function is as follows:
In column A1, we enter the age of individuals, in the second column, the annual income, and in the third column, the amount of savings. The general formula using the AND function will be as follows:
In this example, the combination of IF and AND function will display “yes” if all conditions are met and “no” otherwise. In fact, even if one of the conditions is met, “No” will still be displayed. Now suppose we want to write the formula in such a way that with at least one of the conditions being met, the function considers the person in question as eligible to receive a loan; Thus, using the OR function, we write the formula as follows:
Nested IF function
The IF function can also be written in a nested form. A nested IF refers to a formula where at least one IF function is nested inside another to test more conditions and provide more possible results. Each IF statement must be carefully and somehow nested inside the other to display the correct result. You should pay attention to the fact that if the first condition is met, the function will not go to the next conditions.
Third example: Consider the following formula to check students’ grades:
In the above example, if the value of B1 is less than 70, F will be displayed. If it is less than 75, D is displayed, if it is less than 85, C is displayed, if it is smaller than 95, B is displayed, otherwise, A is displayed.
Fourth example: Another example can be checking students’ grades based on good, average, and poor. In this case, the formula can be written as follows. Here, if the cell value is greater than or equal to 80, it will display the good function, and if it is greater than or equal to 60, it will display the average function and otherwise, it will display the poor function.
Behavior of the IF function with text values
You can also use the IF function in Excel to check text values. The IF function in Excel can perform various operations based on the result of the condition by checking the text values.
Fifth example: As a simple example, suppose we want to see if the number of letters in each name is greater than 5 or not. For this purpose, we can use the LEN function to write the formula as follows:
Sixth example: In the following example, we are going to return a value of “yes” and “no” by checking the delivered and undelivered items. In such situations, a combination of ISNUMBER and SEARCH (case insensitive) or FIND (case sensitive) can be used. In this case, the formula can be written as follows:
Applications of the IF function
The IF function in Excel is a very useful tool for data analysis and decision-making, which allows users to perform conditional operations; From simple tasks such as score calculation to complex scenarios including nested expressions and evaluation of multiple criteria. By mastering this function, users can advance the data analysis process more easily.
