If function is one of the most used functions in Excel, it helps you calculate values under many different conditions. The following article gives you a detailed guide on how to use the multiple if function with examples.
The if function is simplified in the sense that the function tests a value with a given condition, if it satisfies the condition that returns 1 value, if not satisfies returns another value.
1. Syntax: If (logical_test, Value if true, Value if false)
Inside:
- Logical_test: Conditional expression.
- Value if true: Value returned if the condition is true.
- Value if false: The value returned if the condition is false.
In some cases where a problem contains multiple conditions, you need to use the Or, And function to combine multiple conditions.
- The problem has many conditions, conditions occur simultaneously, you need to use the And function in the conditional expression. The syntax of the And function is: And (logical1, logical2, .)
- The problem has many conditions, the return value only needs to satisfy one of the conditions you use the Or function in the conditional expression. The syntax of the Or function: Or (logical1, logical2, .). Where logical is conditional expressions.
2. Usage and illustrative example.
Example 1: Using the If function contains only 1 condition to consider.
For example, given the result of entrance exam to grade 10 based on the results of the 3 subjects, if the total score is greater than 24, the student passes the exam, whereas the candidate fails the exam.
In a cell to calculate, enter the formula: = If (H7> 24, 'Do', 'Slide')
Press Enter and copy the formula for the remaining values to get the result:
Example 2: Similar to example 1 but the admission requirements if a student passes must achieve a total of 3 subjects greater than 18 points and no subject will score 0.
In a cell to calculate, enter the formula: = If (And (H7> 24, E7 <> 0, F7 <> 0, G7 <> 0), 'Do', 'Slide')
Press Enter and copy the formula for the remaining values to get the result:
As you can see, there are a number of candidates who have a total score greater than 18 but have 1 subject with a score of 0 so they are unfortunately eliminated.
Example 3: Rank student learning based on grade point average.
- If the average score is greater than or equal to 8.5 => good academic grade
- If the average score is greater than or equal to 6.5 and less than 8.5 => good academic performance
- If the average score is greater than or equal to 5 and less than 6.5 => average learning force.
- Left if the average is less than 5 => weak learning capacity.
In the cell to calculate, enter the formula: = IF (H7> = 8.5, "Good", IF (AND (H7> = 6.5, H7 <8.5), "Fair", IF (AND (H7> = 5, H7
Hit Enter Copy the formula for the remaining values to get the result:
Example 4:
There is a list of households engaged in animal husbandry and rearing of various types, filling in the type of farming that corresponds to the animal of the household.
For example:
- Households raising chickens or ducks are classified into livestock raising, households growing peanuts or bananas in a farming group, households raising shrimp or fish in groups of aquaculture groups.
There are 2 conditions and conditions here or should use the Or function. In the cell to enter the formula: = IF (OR (D7 = "x", E7 = "x"), "Crop", IF (OR (F7 = "x", G7 = "x"), "Blanket farming "," Aquaculture "))
Press Enter and Copy the formula for the remaining values to result:
The above is a detailed guide on how to use the multiple if function with illustrative examples. Good luck!