Excel IF Function

Excel’s IF function is a versatile formula that performs logical tests, returning one value if a condition is true and another if false. With its clear syntax, it’s ideal for checking criteria and can be combined with other functions for complex formulas.

1. Excel IF Statement

The IF function evaluates a logical condition and returns different outputs based on the result,

=IF(condition, value_if_true, value_if_false)

Parameters:

• condition: The logical test (e.g., A1 > 10).
• value_if_true: The output if the condition is true.
• value_if_false: The output if the condition is false.

Supported Operators: <, >, <=, >=, =.

2. IF Formula in Excel

The basic IF formula checks a logical condition and returns one value if the condition is true and another if the condition is false. For example, we can test if a cell's value is greater than a certain number and return custom messages or values based on that condition.

Example Formula:

=IF(A1 > 10, "Greater than 10", "10 or less")

• If A1 contains a value greater than 10, the formula returns "Greater than 10".
• If A1 contains 10 or less, it returns "10 or less".

This basic IF formula helps simplify decision-making in spreadsheets, allowing to categorize or analyze data automatically based on defined conditions.

Note: we have the flexibility to define both the condition and the return values.

3. How to Use IF Function in Excel (With Example)

Suppose we want to determine if all the students in a class PASSED or FAILED their tests based on their scores. Instead of manually checking each student's score, we can define the IF condition for one cell.

Step 1: Enter Data in Spreadsheet

First, open MS Excel and enter the relevant data into our worksheet. For example, the data should include students' names in Column A and their corresponding marks in Column B. We want to check whether each student has passed or failed based on their marks.

Step 2: Select the Cell and Enter the IF Formula

Now, select the cell where we want to display the pass/fail status. For instance, go to Cell C2 and enter the following formula:

=IF(B2>=35, "Pass", "Fail")

This formula checks if the marks in Cell B2 are greater than or equal to 35. If the condition is true (marks ≥ 35), it will display "Pass" otherwise, it will display "Fail".

Step 3: Apply the Formula to the Rest of the Cells and Preview Result

Select Cell C2, hover over the bottom-right corner until the cursor changes to a "+" (fill handle), then drag it down to apply the formula to the rest of the column.

4. How to Use the IF with AND Function in Excel (With Example)

The IF function with AND in Excel allows we to check multiple conditions at once. It returns a value if all the specified conditions are true and another value if any condition is false.

Syntax:

=IF(AND(condition 1, condition 2), Value if true, Value if false)

For example, =IF(AND(A1>90, B1>90), "A1 Grade", "Fail") will return "A1 Grade" if both A1 and B1 are greater than 90, otherwise, it will return "Fail"..

Step 1: Enter the Data into the Sheet

Start by entering the data into our worksheet. For this example, let's assume Column A contains scores for Subject 1 and Column B contains scores for Subject 2.

Step 2: Enter the IF with AND Formula

Next, in Cell C2, enter the IF with AND formula to check if both scores are greater than 90.

=IF(AND(A2>90, B2>90), "A1 Grade", "Fail")

This formula checks if both A1 and B1 are greater than 90. If both conditions are true, it will return "A1 Grade"; if either of the conditions is false, it will return "Fail".

Step 3: Drag down the Formula and Preview Results

Drag the Formula to apply it to rest of the cells.

5. How to Use the IF with OR Function in Excel

The IF function with OR in Excel allows we to test multiple conditions and return a result if at least one condition is true. The syntax is:

=IF(OR(condition1, condition2), value_if_true, value_if_false)

For example, if we want to check if a student's score in either Subject 1 or Subject 2 is greater than 90, we can use the OR function. If either condition is true, the formula will return "Pass", otherwise, it will return "Fail".

Step 1: Enter the Data

First, enter the data into our worksheet. For example, let's assume Column A contains the scores for Subject 1 and Column B contains the scores for Subject 2.

Step 2: Enter the IF with OR Formula

Now, in Cell C2, enter the IF with OR formula to check if either of the two subject scores is greater than 90:

=IF(OR(A2>90, B2>90), "Pass", "Fail")

This formula will return "Pass" if either A1 or B1 is greater than 90. If neither condition is true, it will return "Fail".

Step 3: Drag Down and Apply the Formula

Drag down and apply the formula to other rows as well

Note: The IF with OR function in Excel will only evaluate numeric values. If the cell contains text or non-numeric values, it will be considered as TRUE, as Excel treats text as a non-zero value. Ensure that the cells contain only numeric data for accurate evaluations.

6. How to Use the IF with NOT Function in Excel

The IF with NOT function in Excel is used to negate a condition. It returns TRUE if the condition is FALSE and FALSE if the condition is TRUE. Essentially, it reverses the result of the condition being tested. It's syntax is:

=IF(NOT(Condition 1), Value if true, Value if false)

For example, if we want to check whether the color of a ball is not red, the formula would be:

=IF(NOT(A1="Red"), "Selected", "Rejected")

• If A1 is not red, it will return "Selected".
• If A1 is red, it will return "Rejected".

Step 1: Enter Data into the Sheet

First, enter our data. For this example, we'll assume that Column A contains the color of balls.

Step 2: Enter the IF with NOT Formula

In Cell B1, enter the IF with NOT formula to check if the ball color is not red. The formula will return "Selected" if the color is not red and "Rejected" if it is red.

=IF(NOT(A1="Red"), "Selected", "Rejected")

This formula checks if the value in A1 is not "Red". If the condition is true (i.e., the ball color is not red), it returns "Selected"; otherwise, it returns "Rejected".

Step 3: Apply the Formula to Other Rows and Preview Result

Drag down the formula and Preview Results

This way, the IF with NOT function allows we to check and reverse the outcome based on a specific condition (in this case, checking if the ball is not red).

7. Nested If Function in Excel with Examples

A Nested IF function in Excel is used when we need to evaluate multiple conditions and return different results based on the conditions. In simple terms, it's a combination of multiple IF functions within one formula to handle more than one condition. The syntax is:

=IF( condition1, Value if true1 ,IF(Condition2, Value if true2, Value if false2)). 

Below is an example of the Nested if function.

Step 1: Enter our Data

Let's take a dataset of students with their marks and we have to assign the division based on the marks.

Step 2: Enter Nested IF Formula

=IF(B2>=60,"1st Division",IF(B2>=30,"2nd Division",IF(B2>=10,"3rd Division","Fail")))

This formula checks multiple conditions:

• If the marks in B2 are greater than or equal to 60, the formula returns "1st Division".
• If the marks are between 30 and 59 (i.e., B2 >= 30 but less than 60), the formula returns "2nd Division".
• If the marks are between 10 and 29 (i.e., B2 >= 10 but less than 30), the formula returns "3rd Division".
• If the marks are less than 10, the formula returns "Fail".

8. IF Statement to Return Another Cell

In Excel, "return another formula with IF" means using the IF function not just to return static text or numbers but to output the result of another formula. This approach dynamically applies calculations or functions based on specific conditions.

For example:

We want to assign a discount based on the purchase amount in cell B2:

• 20% Discount for purchases of 500 or more.
• 10% Discount for purchases between 200 and 499.
• No Discount for purchases less than 200

Step 1: Open our Spreadsheet and Enter Data

Open Excel with existing data or enter new data and go to the cell where we want the discount to appear (e.g., C2).

Step 2: Start with the First Condition

Type the IF formula:

=IF(B2>=500,"20% Discount"

This checks if the value in B2 is greater than or equal to 500.

If TRUE, it returns "20% Discount".

Step 3: Add the Second Condition

If the first condition is FALSE, add another IF function for the next range using the below formula:

=IF(B2>=500,"20% Discount",IF(B2>=200,"10% Discount"

This checks if the value in B2 is greater than or equal to 200 (but less than 500, since the first condition is FALSE).

If TRUE, it returns "10% Discount".

Step 4: Add the Final Condition

Add a condition for all remaining cases (less than 200), use the below formula:

=IF(B2>=500,"20% Discount",IF(B2>=200,"10% Discount","No Discount"))

If neither of the first two conditions is TRUE, it returns "No Discount".

Step 5: Press Enter

Hit Enter to complete the formula. The formula dynamically calculates the discount based on the value in B2.

9. IF Function with Specific Text

When we need to check whether a cell contains specific text, we can use the IF function combined with SEARCH or REGEXMATCH to achieve this. This allows we to perform conditional actions based on whether a given text string exists within another cell.

For example, if we want to check whether a cell contains the word "approved" and return "Yes" if true or "No" if false, we can use:

Step 1: Enter data into the Sheet

Open MS Excel and Enter data into the sheet

• Step 2: Type the Formula

Select the Cell, where we want to type the Formula and Press Enter

=IF(ISNUMBER(SEARCH("approved",A2)),"Yes","No")

Understand the Components

a) SEARCH("approved", A1):

1. Looks for the text "approved" in cell A1.
2. If found, it returns the starting position of the text (a number).
3. If not found, it returns an error.

b) ISNUMBER(...):

1. Checks whether the result of SEARCH is a number (indicating the text was found).
2. Returns TRUE if a number exists, otherwise FALSE.

c) IF(ISNUMBER(...),"Yes","No"):

1. "Yes" if the text is found.
2. "No" if the text is not found.

Step 3: Drag the Formula and Preview Results

Drag down the formula to apply it to other cells as well and preview Results.