4 Basic Usages of IF function in Excel

IF function is a widely used function in Excel formulas. In this article, we will discuss about its basic usage.

IF function will judge the values that you give and return the corresponding result. Although the basic usage is very simple, you can still fulfill many complex tasks through the combination of IF function and other functions.

1.The Basic Usage

Here you can know the original usage of the IF function.

In this image, you can see the sales volume of a sales representative.

Suppose you need to assess an employee’s sales volume, you can apply this function.

1. Click the target cell. In this example, we click cell E2.
2. Now input the IF formula into the cell:

=IF(D2>700,”Qualified”,”Unqualified”)

There are three arguments in IF function. The IF function will judge the first argument. If it is true, this formula will return the result of the second argument. Else it will return the third argument.

3. After you input the formula into the cell, press “Enter” or click any other cell. Thus, the result will appear in the cell.
4. Now click the cell again.
5. Double click the fill handle of the cell. Thus, you can get result of this assessment.

You can see that the original usage of IF function is actually very simple.

2.Nested IF function

In a formula of IF function, you can also nest IF function into the formula. You may also follow the examples below to see how it works. Still we use the same example again.

Now suppose you need to calculate the bonus of the sales volume. Bonuses are distinguished by the sales volume. You may refer to the table below:

Sales Volume	Commission
>1000	0.3
700-1000	0.2
<700	0

It is apparent that if you calculate one by one, this task will be time consuming. Therefore, here you can also use the IF function.

1. Click the target cell. Here we click F2.
2. Now input the formula into the cell:

=IF(D2>1000,(D2-1000)*0.3+60,IF(D2>700,(D2-700)*0.2,0))

In this formula, there is a nested IF function. IF the sales volume is greater than 1000, the formula can calculate the bonus directly. But if not, it will continue judge whether the number is greater than 700. Thus, here we nest a function in the formula.

3. And then press “Enter” or click any other cell. And you can see the result in F2.
4. Use the fill handle and calculate other bonus in the column.

3.Combine with Other Functions

Except for nesting IF function into an IF formula, you can also combine the IF function with other functions. Actually this also has a wide range of application. In this article, we will use a very easy example to illustrate the usage. Still we use the same example.

Suppose you need to assess the sales volume according to the two products instead of the total number. Only when the sales volume of DataNumen Excel Repair is greater than 300 and the sales volume of DataNumen Access Repair is greater than 450, the sales representative is qualified.  Therefore, you can combine the IF function with AND function.

1. Click the target cell E2.
2. Not input this formula into the cell:

=IF(AND(B2>300,C2>450),”Qualified”, “Unqualified”)

3. And then press “Enter” on the keyboard or click any other cell in the worksheet. The result of the first month will immediately appear.
4. Now use the fill handle to get the result of other month.

Actually, this is a very simple example of combination. And there are many other functions that can be used with IF function. In your actual need, if you find some task is hard to finish, you may try to find a way to use IF function with other functions.

4.Return an Area

The above examples all show the results of a certain value. But on the other hand, the IF function can also return a range.

In this example, suppose you need to calculate the average sales volume of the two products.

However, if the total number if less than 700, you don’t need to calculate the average number. Thus, you can combine the IF function with AVERAGE function. And the IF function will return the certain range for AVERAGE function.

1. First click the target cell. Here we click F2.
2. Now input the formula into the target cell:

=AVERAGE(IF(D2>700,B2:C2,0))

This formula is a combination of AVERAGE function and IF function. Here the IF function will return the corresponding area if the total sales volume is greater than 700.

3. After the step2, click any other cell in the sheet or press “Enter”. Thus, the result of the first cell will appear.
4. Now continue use the fill handle and get the result of other months.

From the 4 usages of IF function, you can see that no matter how the form change, the basic rule of the function will not change. If you can use it very well, you will be more sufficient in finishing your work.

Excel Crash will Destroy Your Arrangements

When most of your works need to rely on Excel, you need to pay more attention to this software. Because once it corrupts, your work will surely be in a mess. Thus, no matter how perfect your arrangement is, it will become out of order. We suggest that you can prepare an Excel repair tool at hand. Thus, whenever you find Excel corruption, you can fix it as soon as possible.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com