Excel IF Function

The IF function in Excel is one of the most powerful and commonly used formulas that allows you to perform logical tests and return different values based on whether the condition is true or false. If you’ve ever needed to check whether a value meets certain criteria, then the IF function is the tool you’ll want to master. In simple terms, the IF function evaluates a condition and returns one result if the condition is true and another if it’s false. The best part is that its syntax is clear and can be combined with other functions to create more complex formulas.

Let's explore how the IF function works in Excel, its syntax, and practical examples of its usage to help you get started.

What is IF Function in Excel

The IF function in Excel is a powerful tool that allows users to perform logical tests within a spreadsheet. It checks whether a given condition is true or false and returns different values based on the outcome. The basic syntax of the IF function is:

Excel IF Statement

The IF function serves a single purpose, i.e., to run tests for specific conditional statements. 

=IF(condition, value_if_true, value_if_false)

where,

• condition: The logical test or condition to evaluate (e.g., A1 > 10).
• value_if_true: The value to return if the condition is true.
• value_if_false: The value to return if the condition is false.

This function enables dynamic decision-making in spreadsheets by automating responses based on conditions, making it a valuable tool for data categorization, analysis, and productivity.

The IF function supports the logical operators( <,>,<=,>=,=)  to test logical arguments.

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, you 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 you to categorize or analyze data automatically based on defined conditions.

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

How to Use IF Function in Excel (With Example)

Suppose you 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, you can define the IF condition for one cell.

Step 1: Enter Data in Spreadsheet

First, open MS Excel and enter the relevant data into your 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 you 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.

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

The IF function with AND in Excel allows you 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 your 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.

How to Use the IF with OR Function in Excel

The IF function with OR in Excel allows you 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 you want to check if a student's score in either Subject 1 or Subject 2 is greater than 90, you 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 your 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.

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 you 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 your 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 you to check and reverse the outcome based on a specific condition (in this case, checking if the ball is not red).

Nested If Function in Excel with Examples

A Nested IF function in Excel is used when you 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 your 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

Formula used is : =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".

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 Your Spreadsheet and Enter Data

Open Excel with existing data or enter new data and go to the cell where you 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.

IF Function with Specific Text

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

For example, if you want to check whether a cell contains the word "approved" and return "Yes" if true, or "No" if false, you 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 you want to type the Formula and Press Enter

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

Understand the Components

SEARCH("approved", A1):

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

ISNUMBER(...):

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

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

• Uses the result from ISNUMBER to determine the output:
  • "Yes" if the text is found.
  • "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.

Conclusion

The IF function in Excel is an invaluable tool for anyone looking to automate decision-making within their spreadsheets. Whether you're using it for basic comparisons, more complex scenarios involving AND or OR, or even combining it with other formulas to return dynamic results, the IF function can significantly enhance your productivity and efficiency. By understanding how to set up conditions with true or false outcomes, and applying it to specific texts or numerical values, you can tackle a wide range of tasks, from data analysis to customized reports. Mastering the IF function opens the door to more advanced Excel techniques, making it a fundamental skill for anyone working with spreadsheets.