The IF function is one of the most used Excel functions. If you know how to combine the IF function with other functions such as AND, OR, NOT, the user will be more convenient in data declaration and automatic rating and evaluation.
Of course, with the IF, AND or OR functions, those who have learned to program will be easier to grasp, but those who have never learned it can fully comprehend after a few examples.
To master the IF function in Excel, an advanced but also very useful usage, anyone can refer to the below tutorial.
Excel IF function instructions
To use the IF function we have the following structure: = IF (Condition Clause, Value 1, Value 2)
Where value 1 is used if the conditional clause is true, value 2 is used if the conditional clause is false.
An example would be when we have a statement of the number of days off in the year in column A, and want column B to be the automatic award rating for those who do not take any leave. Then, corresponding to the value in cell A2, we will enter the function structure for cell B2 as follows:
= IF (A2 = 0, "Yes", "No") (including the quotes is to specify the value as text)
The best part is that when a value in column A changes, the corresponding value in column B is also updated.
This is the case when we have a statement of the number of days off in the year in column A, and want column B to be a bonus rating for those who do not take any leave. Then, corresponding to the value in cell A2, we will enter the function structure for cell B2 as follows: = IF (A2 = 0, "Yes", "No").
Apply the same formula for the whole column B corresponding to column A, we will have the diligent reward rankings.
Instructions on how to use the IF function with multiple conditions
If we want to make our chart with more than one case, we can nest multiple IF functions together with the following structure:
= IF (Conditional Clause 1, Value 1, IF (Condition Clause 2, Value 2.1, Value 2.2))
The above means that if clause 1 is false, then Excel will consider the conditional clause 2. The example for the use of the IF function with multiple conditions might be when we have a balance statement. each day is in column A and wants column B to automatically rate whether that date is a loss or profit.
An example of the case of using the IF function with multiple conditions is when we have a daily balance sheet in column A and want column B to automatically rate whether the date is loss or profit. Then we can nested 2 or more IF functions together.
Applying the same formula for the whole column B, column B will automatically rate whether the date of loss or profit corresponds to column A value.
Instructions on how to use the IF function in combination with AND, OR
There will be many cases where the condition of the IF function is comprised of multiple component conditions, and then we will have to combine the conditions with the functions AND, OR .
A good example is when we have the graduation report for the students in column A, there is the military test score report in column B, and we want column C to automatically classify the student with the diploma. Any student will be suspended.
The condition here is that neither graduation nor military marks can be less than 5. So we will have to incorporate the AND function into the IF clause in cell C2 to ensure that both are met. Component conditions are as follows:
= IF (AND (A2> = 5, B2> = 5), "Issued with", "Hanging with")
This is the case when we have the graduation report for the students in column A, have the military test score report in column B, and want column C to automatically classify the student with the diploma, any student will be suspended.
The condition here is that neither graduation nor military marks can be below 5. So we will have to incorporate the AND function into the IF's conditional clause to ensure that both the conditions are met. part.
Applying the same formula for both column C, we will have an automatic ranking which student is granted a diploma, which student will be suspended according to the values provided from column A and column B.
The OR function differs from the AND function, when both of the component conditions are not needed, but only 1 of the 2 component conditions is needed. Still with the above example, we can nest the OR function and the IF function conditional as follows to ensure the final result is the same:
= IF (OR (A2 <5, B2 <5), "Not Issued", "Issued")
Still with the above example, we can nest the OR function and the IF function clause as shown in the figure to ensure the final result is the same.