Most people are familiar with using IF Functions to test a condition against a large dataset. However, they might not know the benefits of using them in conjunction with their OR, AND, or other operators and functions.

Before moving on, let's see how a simple IF Function looks and how to use the arguments correctly.

An Overview of Excel's IF Function

As shown above, the IF Function uses three arguments explained below:

1. Logical Test: It deals with the condition which you are evaluating to be true or false.
2. Value_if_true: This argument contains the text/information you want the function to return if the data meets the tested condition criteria.
3. Value_if_flase: Like the above argument, it also returns the information you will like the function to return if the condition is false.

The first argument is required for the IF function to execute; the other two are optional. You can add any text to the last two arguments or leave them empty. If you leave either one or both of the last two arguments empty, the result will also be an empty cell.

Now, let's see how you can use the IF Function to analyze more than one condition in a single formula. Furthermore, you'll also learn how to use it in Nested Formula with OR and AND operators.

Excel IF Function Example

Suppose you work at a jewelry store where you record the sales and revenue generated by each of seven workers working at the store. At the end of each week, the company gives a weekly bonus to only those employees who meet a defined threshold.

Below, you see the number of sales and revenue generated by each worker for one week.

Let's say the bonus threshold for this week is the quantity of sales equal to or greater than 4. To check which of the employee will get a bonus for this week, you'll use a simple IF function.

So, you will use the quantity of sales as a testing argument in the IF function, as shown below.

In highlighted formula, B4>=4 is test argument, “Eligible” is Value_if_true argument, while Value_if_false argument is left empty intentionally.

In cases where an argument is empty, always put a double quotation mark ("") around it; otherwise, the result will give an error or display zero in the cell that meets the condition.

After testing each worker's quantity of sales, the IF Function must return results as Eligible if the quantity of sales is greater than or equal to four; otherwise, leave the cells empty.

Press Enter key to implement the formula. As the quantity of sales of worker 1 is six, which is greater than four, the output of the function for the first cell will be Eligible.

You won't have to enter the formula for all cells individually. Instead, using Autofilling sequence function, move your cursor to the lower-left corner of the selected block and drag it down.

Doing so will implement the function to other cells down the row.

See how workers 1, 2, 4, and 7 achieve the threshold of just under four sales and are thus eligible for a bonus, whereas the rest of the cells remain empty as these workers don't meet the threshold.

Let's say; instead of leaving the second argument empty, you've put Ineligible there. In that case, the final output will be as shown below.

Related: How to Use Data Validation in Excel

Using AND Operator With IF Function

For another week, the company has changed its policy of giving bonuses and added revenue in the set threshold with the quantity of sales. Thus, you have to analyze the same data but with two testing conditions instead of one.

The company gives the bonus to workers who generate equal to or greater than four sales with revenue greater than 2500. You will use an AND operation in this case, and the formula will be as follows:

        =IF(AND(B4>=4,C4>2500),"Eligible","Ineligible")
    

Here, in the above formula, AND operator is used as a test argument as you have to test two parameters.

Just like the previous case, if input data (Quantity of sales and Revenue) fulfills criteria, the function will return "Eligible" as its output, otherwise "Ineligible."

Press Enter Key to execute the function and then drag it downwards to apply the same formula to the rest of the dataset. You'll see the final results as follows.

You can see, only workers 1, 2, and 4 are the ones that have generated greater than or equal to four sales with revenue more than 2500. So, they are eligible for the bonus.

Although worker 7 has generated four sales that met the first criteria, its revenue is less than 2200. So, he is ineligible for a bonus due to not meeting the second condition.

Using OR Operator With IF Function

For the third week, the company has made a good profit and is giving a bonus to workers who have met any one of the two conditions. In this case, you can use OR operator as a test argument for the IF statement to filter out an exact number of workers.

Thus, workers who sold four items or more or generated a revenue of more than 2500 will qualify for the bonus.

The formula will look like this:

        =IF(OR(B4>=4,C4>2500), "Eligible", "Ineligible")
    

Press Enter to execute the formula, and by dragging it down the row, you will get this result.

You can see that Worker 7 is also eligible for a bonus in this case as although he hasn't met the revenue threshold but has made four sales. He fulfills one condition, which makes him eligible for the bonus.

Similarly, you can use the IF Function with AND and OR operators and with other functions to filter out results from a large set of data.

Related: How to Become a Microsoft Excel Power User Quickly

Simplify Your Calculations With IF Function in Nested Formulas

When you combine the IF function with other functions, you can simultaneously test multiple conditions on a large dataset.

This eliminates the need to manually test multiple conditions individually, saving you both time and effort. Automating basic functions will also enable you to be more productive.