How to Use the IF Function to Check for Larger, Equivalent, or Smaller Values in Excel

The IF function is one of the most commonly used functions in Excel. It allows you to perform a logical test and return different values based on the result of the test. The syntax of the IF function is:

=IF(logical_test, value_if_true, value_if_false)

• logical_test is the condition that you want to check. It can be a comparison between two values, a reference to a cell, or a formula that returns TRUE or FALSE.
• value_if_true is the value that you want to return if the logical test is TRUE. It can be a number, text, another formula, or a reference to a cell.
• value_if_false is the value that you want to return if the logical test is FALSE. It can be the same as value_if_true, or different.

How to Check for Larger, Equivalent, or Smaller Values

One of the most common uses of the IF function is to compare two values and return different results based on whether one value is larger, equal, or smaller than the other. To do this, you can use the following comparison operators in the logical test:

• > means greater than
• < means less than
• = means equal to
• >= means greater than or equal to
• <= means less than or equal to
• <> means not equal to

For example, if you want to check if A1 is larger than B1, you can use the formula:

=IF(A1>B1, "Larger", "Not Larger")

This formula will return “Larger” if A1 is greater than B1, and “Not Larger” otherwise.

How to Use the IF Function in an Excel Table

To illustrate how to use the IF function to check for larger, equivalent, or smaller values in an Excel table, let’s use the following scenario:

You have a table of sales data for different products and regions. You want to calculate the commission for each sale, based on the following rules:

• If the product is “A” and the region is “North”, the commission is 10% of the sales amount.
• If the product is “A” and the region is not “North”, the commission is 8% of the sales amount.
• If the product is not “A” and the region is “North”, the commission is 7% of the sales amount.
• If the product is not “A” and the region is not “North”, the commission is 5% of the sales amount.

The table looks like this:

To calculate the commission for each sale, you can use the following formula in the Commission column:

=IF(AND(Product="A", Region="North"), Sales*0.1, IF(AND(Product="A", Region<>"North"), Sales*0.08, IF(AND(Product<>"A", Region="North"), Sales*0.07, Sales*0.05)))

This formula uses nested IF functions to check for multiple conditions. The logic is as follows:

• The first IF function checks if the product is “A” and the region is “North”. If yes, it returns the sales amount multiplied by 0.1 (10%).
• If not, it goes to the second IF function, which checks if the product is “A” and the region is not “North”. If yes, it returns the sales amount multiplied by 0.08 (8%).
• If not, it goes to the third IF function, which checks if the product is not “A” and the region is “North”. If yes, it returns the sales amount multiplied by 0.07 (7%).
• If not, it returns the sales amount multiplied by 0.05 (5%).

The result of the formula is:

Other Approaches

There are other ways to use the IF function to check for larger, equivalent, or smaller values in Excel. Here are some examples:

• You can use the IFERROR function to handle errors that may occur in the logical test. For example, if you want to check if A1 is larger than B1, but B1 may contain a zero or a blank, you can use the formula:

=IFERROR(IF(A1>B1, "Larger", "Not Larger"), "Invalid")

This formula will return “Invalid” if B1 is zero or blank, instead of showing an error message.

• You can use the IFS function to simplify nested IF functions. The IFS function allows you to specify multiple conditions and values in pairs, without using AND or OR functions. For example, the formula for calculating the commission in the previous scenario can be rewritten as:

=IFS(AND(Product="A", Region="North"), Sales*0.1, AND(Product="A", Region<>"North"), Sales*0.08, AND(Product<>"A", Region="North"), Sales*0.07, TRUE, Sales*0.05)

This formula is shorter and easier to read than the nested IF formula.

• You can use the SWITCH function to evaluate an expression and return different values based on the result. The SWITCH function allows you to specify an expression, and a list of possible values and outcomes. For example, if you want to check the grade of a student based on their score, you can use the formula:

=SWITCH(Score, 90, "A", 80, "B", 70, "C", 60, "D", "F")

This formula will return “A” if the score is 90, “B” if the score is 80, and so on. If the score is not in the list, it will return “F”.