IF AND in Excel

IF AND in Excel Formula

If And in excel are the two most different functions which are usually nested together, in a simple If function we can only test a single criteria but if we use the logical AND function we can test more than one criteria and give output based on the criteria, they are used as follows =IF ( Condition 1 AND Condition 2, Value if true , Value if False).

Explained

The usage of IF AND formula is widely applied as the combination of the two logical functions enables the user to evaluate multiple conditions using AND functions and based on the outcome of AND function the IF function returns the true or false value respectively. The IF formula in excel is the logical formula used to test and compare the condition expressed with the expected value by returning the desired result if the condition is either TRUE or FALSE.

The AND function, on the other hand, is also known as a logical function, this formula is used to test multiple criteria wherein it returns TRUE if all the conditions mentioned are satisfied or else returns FALSE if either of the one criteria is false. AND function can also be used with the IF formula to return the desired result.

Syntax

How to Use IF AND Formula in Excel? (with Examples)

Example #1

Consider the following table to understand the functioning of IF AND function.

The table has a comparative analysis for apartments to be made on the basis of the age of the building and the type of society. In this example, we would be using the combination of less than equal to excel operator and the equal to text functions in the condition to be demonstrated for IF AND function.

The function used to perform the check is

=IF(AND(B2<=2,C2=”Gated”),”Consider”,””)

Below is the screenshot of the condition applied to the table to perform the evaluation.

Press Enter to get the answer.

Drag the formula to find the answer for all apartments.

Explanation:- Based on the above example let us perform a check to evaluate the apartments for cell B2 less than and equal to 2 years and type of society as a gated community. So the IF AND function will be doing one of the following:-

• If both the arguments entered in AND function is TRUE then the IF function will mark the apartment to be considered.
• If either of the arguments is found to be FALSE or both the arguments entered in AND function is FALSE then the IF function will return a blank string.

Example #2

The IF AND function can also perform calculations based on whether AND function is TRUE or FALSE apart from returning only the predefined text strings.

Let us consider the below data table to understand this approach for calculating the employee hike based on the number of orders received and performance as the parameters for AND condition.

=IF(AND(B2>=200,C2=”A”),D2*10%,D2*5%)

Press Enter and see the final output of the above example.

Drag the formula to find the bonus of all employees.

Explanation:- The criteria to calculate bonus is done if the condition for the number of orders received is greater than equal to 300 and the performance is “A”. Based on the results the IF  function does the following:-

• If both the conditions are satisfied then AND function is TRUE then the bonus received is calculated as salary multiplied by 10%.
• If either of the one or both the conditions are found to be FALSE by AND function then the bonus is calculated as salary multiplied by 5%.

Example #3

As we have seen the above two examples had only two criteria to test and evaluate the AND conditions, but nothing is stopping you to use multiple arguments or conditions to test them for TRUE or FALSE. Let us evaluate three criteria an AND function in the below data table.

Referring to the data table we have five stocks with their respective financial ratios such as ROCE, ROE, Debt to equity and PE ratio. Using these parameters we will be analyzing the stocks which will have the best investment horizon for better growth. So let us apply the condition to arrive at the result.

As can be seen in the above table we have the stocks and their respective parameter details which we will be using to test the condition to invest in the suitable stocks.

The syntax used in the below table is:-

=IF(AND(B2>18%,C2>20%,D2<2,E2<30%),”Invest”,””)

• Press Enter and see the final output of the above example.

• Drag the formula to find the Investment Criteria.

In the above data table, the AND function tests for the parameters using the operators and the resulting output generated by the IF formula is as follows:-

• If all the four criteria mentioned in AND function are tested and satisfied then the IF function will be returning “Invest” text string.
• If either one or more of the four conditions or all the four conditions fail to satisfy the AND function then the IF function will be returning empty strings.

Things to Remember

1. The IF AND function in combination do not differentiate towards case insensitive texts that are uppercase and lower case letters.
2. The AND function can be used to evaluate up to 255 conditions for TRUE or FALSE and the total formula length must not 8192 characters.
3. Text values or blank cells are given as an argument to test the conditions in AND function would be ignored.
4. The AND function will return “#VALUE!” if there is no logical output found while evaluating the conditions.

Recommended Articles

This has been a guide to IF AND in Excel. Here we discuss how to use the two logical functions IF Formula combined with AND in excel along with examples and a downloadable template. You may also look at these useful functions in excel –

• IF Excel Function
• AverageIF  Function in Excel
• Excel SUMIF with Multiple Criteria
• Nested If Condition in Excel