5 Cases to Use MS Excel IF Function in Practice

Date:2019-9-4 Author:Cordelia

IF function, also known as conditional function, enables you to get corresponding results in Microsoft Excel spreadsheets when meeting the specific conditions.

How Does IF Function Work

The function syntax of IF function is: IF(logical_test,[value_if_true],[value_if_false]). If the specified conditions evaluate to true, this function will return a value; If the specified conditions evaluate to false, another value will be returned.

For example, if the value in A1 is larger than 10, the formula: =IF(A1>10, “More”, “Less or equal”) will return “More” as the result; if the value in A1 is not larger than 10, the formula will return “Less or equal”.

When Should You Use IF Function

IF function is one of the most commonly used function in Microsoft Excel, it’s very practical and also easy to grasp. There’re some situations that you can make use of it to significantly save your time and effort. Let’s have a look.

  • Compare 2 Columns of Data in Excel
  • Determine Whether Someone is Qualified (Pass or Fail)
  • Evaluate the Level According to Specific Criteria
  • Find Duplicates in an Excel Column
  • Plus or Minus a Number if Specific Conditions are Met

How to Use IF Function in Pratical

1. Compare 2 Columns of Data in Excel

When there’s a large amount of data in the table, it’s difficult to judge whether the data in 2 columns are the same. On this occasion, IF function can do the job for you quickly and accurately.

Steps:

Enter the formula next to the columns that you want to compare. Here I want to compare the data in A1 and B1 at first, so the formula can be: =IF(A1=B1,”Same”,”Different”).

Enter the formula of IF function

Press Enter or click anywhere outside the cell to see the result. You can then drag your mouse to apply this formula to all cells in column C using fill handle.

Apply the formula to all the cells

2. Determine Whether Someone is Qualified (Pass or Fail)

Similarly, if you want to mark the students whose grades are above a specific score or below it, you don’t need to judge their grades one by one which will doubtlessly take a lot of time.

Steps:

Assuming the students can pass with a score above 300, the formula can be: =IF(C2>300,”Pass”,”Fail”). Enter it beside the grade column.

Enter the formula of IF function

Use fill handle to apply the formula to multiple cells.

Apply the formula to all the cells

3. Evaluate the Level According to Specific Criteria

There are many fields that need to be graded, like the quality of products and the scores of students. With IF function, you can evaluate the level very conveniently.

Steps:

Confirm several scores as the points to divide different grades. In my case, I want to evaluate the grades of students in the table below, so the formula can be: =IF(C2<250,”D”,IF(C2<350,”C”,IF(C2<450,”B”,”A”))). It’s the combination of several conditions, and the grades will be divided into 4 levels: A(>450), B(350~450), C(250~350), D(<250).

Enter the formula of IF function

Enter the formula beside the existing column of scores, then use fill handle to apply it to all the selected cells. In this way the scores of students will graded according to the conditions you set within seconds.

Apply the formula to all the cells

4. Find Duplicates in an Excel Column

With IF function, you don’t need to find the duplicates manually as well. The formula can do better and quicker than your eyes.

Steps:

If the list of data is in column A, create a new column to return the result and enter the formula: =IF(COUNTIF(A:A,A2)>1,”Duplicates”,””).

Enter the formula of IF function

Thus only the repeated data will be marked with “Duplicates”.

Apply the formula to all the cellsvvvv

5. Plus or Minus a Number if Specific Conditions are Met

If you’ve ever been in charge of the payroll in a company, you must be familiar with the situation that among all these employees, some people from specific departments have higher bonuses and some people with larger working years have higher salaries. At this point, how to calculate the wage for all the people without spending too much time?

You may already know the answer – IF function. If you are in a similar situation and need to process a huge amount of data, let Excel run it automatically.

Steps:

The process is all the same. Just enter the proper formula in a new formula and press enter to return the corresponding value. What you need to pay attention to is entering a right formula according to the function syntax and also your actual situation.

For example, if you want to add 500 to all the employees whose working years are more than 3, the formula should be like: =IF(D2>3,E2+500,E2).

Enter the formula of IF function

Then use fill handle to apply it to other cells.

Apply the formula to all the cells

Data processing is not always so difficult as long as you use functions smartly and enter the right formula. IF function is one of the most powerful functions you can use a lot in work as well as daily life. Have a try, you’ll love the feeling of solving practical problems so quickly and easily.

Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

1 thought on “5 Cases to Use MS Excel IF Function in Practice”

  1. vurtil opmer says:

    I think other web site proprietors should take this website as an model, very clean and wonderful user genial style and design, let alone the content. You are an expert in this topic!

Leave a Reply

Your email address will not be published. Required fields are marked *