How to combine the IF function with the AND function and the OR function in Excel

IF, AND, OR . function perhaps no stranger to those who regularly use Excel, however to combine the IF function with the AND function and the OR function in Excel Maybe not everyone understands the use and how to do it. The following article is how to combine the IF function with the AND function and the OR function in Excel for those who have mastered the knowledge and basic functions in Excel. And if you do not know all the basic functions in Excel, you can refer to it below because we will briefly introduce 3 functions: the IF function, the AND function and the OR function.

How to combine the IF function with the AND function and the OR function in Excel

What is the IF function?

The IF function is a conditional function in Excel that has the function of checking a certain value, if it satisfies or does not satisfy the condition in the function, it will return True or False. To better understand how to use this function, readers can read more about IF . functionhere.

What is the AND function?

The AND function in excel is a function that will return TRUE if all arguments of the evaluate function are TRUE and return FALSE if at least one of the arguments of the evaluate function is FALSE. To learn more about the AND function, readers can refer to the introductory reading AND . functionmore closely here.

What is the OR function?

The OR function is similar to the AND function in that it is responsible for checking the conditions of the values, but the difference is that as long as one of two or more conditions is true, the value is obviously True. And all false is obviously False.

Combining the IF and AND . functions

As shown above we will have a list of exemplary employees in a company ranked from 0 to 10 with 10 being the best. Evaluate the amount they receive knowing that if Male >= 8 points will be awarded 500,000 and Female >= 8 points will be awarded 600,000 VND.

Step 1: Here we use IF and AND functions to retrieve with IF as the condition require >= 8 point and the AND function here will find out where is Female >= 8 Points will be 600,000 VND and men will be 500,000 VND.

– Enter the formula first: =IF(AND(E5>=8,D5=”Female”),”600,000″,”500,000″).

* With E5 is the first score column position.

* D5 is the column gender used to determine after satisfying IF.

Step 2: Then press Enter you will see the first result has male gender and score is 8 should be rewarded 500,000 won satisfied with the topic.

Step 3: Applying with the remaining values, we will have 3 male results receiving 500,000 VND and 2 female results 600,000 won copper.

Combining the IF and OR . functions

Still taking the example of the above company, but here we will only consider the condition of what type of employee is evaluated, how much is the evaluation score and whether or not it is rewarded.

Step 1: Here we will use a combination of the IF function and the OR function with the IF function used to find the conditions that satisfy the requirements of the OR function and the OR function will have to find the true value in the two conditions of the sequence.

– First enter the formula: =IF(OR(E5>=8,D5=”Good”),”Awarded”, “No”)

* With E5 is the first score column position.

* D5 is the assessment using the evaluation column, 1 of 2 conditions of the topic.

Step 2: And the result will be as shown below, the first value that satisfies the good rating and the score >=8 should be rewarded.

Step 3: Applying to the following values ​​you will see, with the OR function, just satisfying 1 of 2 conditions will return True results, respectively rewarded.

Combine the IF function with the AND function and the OR function in Excel

In the 3rd example, we will find out that the employee is rewarded with VND 500,000 when he has to meet 45 working hours in which it must be greater than 45 hours in 5 days to be rewarded.

Our formula will be: Formula =IF(OR(true value), AND(value 1 true, value 2 true)), Return value is TRUE, return value is FALSE)

Step 1: Enter the first employee in the bonus column with the following code: =IF(OR(D5>=45, AND(D5>=40, E5 .)6)),”500,000″,”no>

Where D5 is the number of working hours of that employee and E5 is the number of working days.

Step 2: Immediately the system will execute the function and see that the first employee reaches 45 hours and works in 5 days, so he will be rewarded with 500,000 VND.

The result as you have seen when combining the IF function with the AND function and the OR function in Excel will be required to satisfy both AND and OR, of course in this example it is OR in AND ie must satisfy OR first.

Step 3: Apply to the remaining objects, you will see that there are 2 objects that do not satisfy the condition.

https://thuthuat.taimienphi.vn/cach-ket-hop-ham-if-voi-ham-and-va-ham-or-trong-excel-33121n.aspx
Above is a guide to combine the IF function with the AND function and the OR function in Excel with the combination of functions, you will see that there are many problems we can handle with just the above basic function. If you have mastered combining the IF function with the AND function and the OR function in Excel with this article, do you want to try combining the Sumif and Vlookup functions in Excel? Try with the tutorial Combine Sumif and Vlookup functions the following to see if these two functions combined, what will be the result?