If And Statement Excel Everything You Need To Know

This tutorial is all about If and Statements and everything you need to know about the IF function in Excel.

There are things in the world were infinite; some are infinite hence the Excel IF Function is one of these things. Therefore, in our article, we will have a handful of if and statements functions, that you will discover.

Moreover, we will look at how you can use IF and the AND function to evaluate two or more conditions simultaneously.

What does the if function do in excel

The Excel if function handles logical tests and returns one value with a true or false result. Meanwhile, if more than one condition is being tested use nesting If functions. It also allows the combination of functions AND and OR for logical testing.

Moreover, financial analysts often use this in evaluating and analyzing data in specific conditions. Apparently, the text, values, and even errors are functions considered in evaluating.

Considering its function, it is not limited to checking if a set of criteria is equal to another or returns a single result. However, we can use mathematical operators to perform various calculations depending on the criteria. We can also nest multiple IF functions together to perform multiple comparisons.

For example, to “pass” grades above 75: =IF(A1>75,”Pass”,”Fail”).

Logical Operators

Here are the IF function to construct a test using the following logical operators.

How to use IF function in Excel

Example 1 – Simple Excel IF Statement

In this example, we’ll workout on the worksheet, where we want to assign points based on the name in column A. If the name is “Smith”, the result should be 95. If the name is “Jones”, the result should be 90.

Hence we use two IF functions, one nested from another to achieve the task. The formula in H5 is input as follows:

=IF(A2="Smith",95,IF(A2="jones",90))

Note: IF the value in A2 is “Smith”, return 95. Otherwise return 90, if the value in A2 is “Jones”.

There are things that need to remember in executing the example:

1. The value of A2 should be “Smith” and “Jones” if the formula returns False.
2. The IF function is case insensitive and will match “SmiTh” or “smith” or “SMITH”
3. The text values “Smith” and “Jones” must be enclosed in double quotes (“”).

What we had is also a simple NESTEDIF function. To learn more complex nested If below is the discussion.

Example 2 – Excel Nested IF statements

This time, if you want to consider testing multiple logical tests in a single formula, you need to nest several functions into one another.

Basically, this function is called NestedIF function. Particularly, it proves how useful it is in returning different values that depend on the logical function results.

For instance take a look at this scenario which assumes you want to know the students who are qualified with the following criteria: “Poor”, “Good”, and “Very Good” according to their scores.

• Poor: Less than <75 (<75)
• Good: between 80 and 95 (>80 and <95)
• Very good : 95 or greater than(>=95)

Remember: In creating a formula consider the order of the functions carefully which you are going to nest. Hence, Excel logical test required to appear in the order of the formula.

Meanwhile, if the conditions’ results TRUE and the subsequent conditions are no longer be tested, clearly formula will stop once the first returns TRUE.

In our case, the functions are arranged from this:

=IF(B2>=75, "Good", IF(B2>=95, "Very Good", "Poor", IF(B2<75, poor)))

Additionally, you can nest functions up to 64 in modern versions if needed.

Example 3 – Excel IF Statement IF with AND, OR, NOT

In our daily tasks, evaluating several tasks with multiple conditions is inevitable. Thus, it is best if will be utilizing AND & OR functions at a time.

In our sample table, suppose you have the following criteria for checking the exam results:

• Condition 1: exam1>40 and exam2>60
• Condition 2: exam1>50 and exam2>50

Hence the exam will rate as passed if either meets the condition.

If we are going to mind the formula it looks a bit more tricky, however it is not what you think. You just express each of the above conditions as an AND statement and nest them in the OR function (since it’s not necessary to meet both conditions, either will suffice).

• OR(AND(B2>50, C2>50), AND(B2>40, C2>60)

Afterward, use the OR function in logical testing of the IF function, and now you can supply the desired value_if_true and value_if_false values. As the result, you get the following IF formula with multiple AND / OR conditions:

• =IF(OR(AND(B2>50, C2>50), AND(B2>40, C2>60), "Pass", "Fail")

The figure below indicates that we get the result we want to achieve:

Naturally, you are not limited to using only two AND/OR functions in your IF formulas. You can use as many of them as your business logic requires, provided that:

• In Excel 2003 and lower, the arguments will not be more than 30 arguments, and the total length of characters does not exceed 1, 024.
• In Excel 2007 and higher, you have no more than 255 arguments, and the total length of the IF formula does not exceed 8,192 characters.

Summary

In summary, we have discussed everything you need to know about If And Statement Excel, so here some of the recap:

• In most cases, a non-zero value is determined as TRUE while FALSE is all zero. It means Excel functions will work if the logical test results in a numeric value.
• #VALUE! error – It means when the given logical_test argument cannot be evaluated as TRUE or FALSE.