Excel If function application tutorial

　　if function

　　(i) if function description If a function is used to perform a true or false value judgement, a different result is returned according to the true or false value of the logical test, so the IF function is also called a conditional function. It is widely used and can be used to detect the values and formulas by using the IF function. Its syntax is if (logical_test,value_if_true,value_if_false). where logical_test represents any value or expression that evaluates to TRUE or FALSE. This parameter can use any comparison operator. Value_if_true Displays the value returned when Logical_test is TRUE, and value_if_true can also be other formulas. Value_if_false the value returned when Logical_test is false. Value_if_false can also be other formulas. In short, if the result returned by the first parameter logical_test is true, the result of the second parameter value_if_true is executed, otherwise the result of the third argument Value_if_false is executed. IF functions can be nested into seven layers, value_if_false and value_if_true parameters can be used to construct complex detection conditions. Excel also provides other functions that can analyze data based on a condition. For example, if you want to count the number of occurrences of a text string or number in a range of cells, you can use the COUNTIF worksheet function. If you want to sum a text string or number in a range of cells, you can use the SUMIF worksheet function.

　　(ii) If function application

1, the output with a formula blank form

Figure 1 Personnel Analysis Table 1

As an example of the Personnel Status Analysis table shown in the figure, the formula for the total column (in cell G5, for example) is as follows, as data on the composition of the individual departments is not yet filled in:

=sum (C5:F5)

We see the result calculated as 0. If such a form is printed out on the appearance of the page is not satisfactory. Is there a way to get rid of 0 of the total column? You might say that you don't have to write a formula. Of course this is a way, but if we take advantage of the IF function, we can also write the formula without displaying these 0. How to achieve it? Simply rewrite the formula in the total bar (for example, cell G5 only) to:

=if (sum (C5:F5), sum (C5:F5), "")

The popular explanation is that if sum (C5:F5) is not equal to zero, the result of sum (C5:F5) is displayed in the cell, otherwise the string is displayed.

　　A few notes:

(1) sum (C5:F5) is not equal to zero normal writing is sum (C5:F5) <>0, in Excel can omit <>0; (2) "" to indicate that the contents of the string is empty, so the result is that no characters appear in the cell.

If you have a good understanding of the above examples, it is easy to apply the IF function to a wider range of areas. For example, in the performance table according to different grades to distinguish between qualified and unqualified. Now we take a class of English results as an example to specify the use.

　

Figure 2

As shown in Figure 6 of a class, in order to make a final comprehensive assessment, we set the rules for judging whether the student's grades are qualified according to the average score. If the average score of more than 60 points is considered to be qualified, otherwise recorded as unqualified.

According to this rule, we write the formula in the comprehensive evaluation (take cell B12 as an example):

=if (b11>60, "qualified", "unqualified")

The syntax explains that if the value of cell B11 is greater than 60, then the second argument is executed to display the qualifying word in cell B12, or the third argument is to display the unqualified word in cell B12.

In the comprehensive evaluation column can be seen as a result of the C-column of the students on average divided into 54 points, comprehensive assessment of unqualified. The rest are qualified.

3. Application of Multilayer nesting function

In the above example, we simply distinguish between the results of qualified and unqualified, in the actual application, the result is usually a number of grades, such as excellent, good, medium, pass, fail, etc. Is there a way to distinguish one at a a lump? It can be implemented using a multi-tiered nesting approach. Still, for example, we set the rules for the comprehensive assessment to be rated as excellent when the average score of each division exceeds 90 o'clock. As shown in Figure 7.

Figure 3

Description: In order to explain the convenience, we only do two nested examples here, you can do more than the actual situation of nesting, but note that Excel's if function allows up to seven nested.

According to this rule, we write the formula in the comprehensive evaluation (take cell F12 as an example):

=if (F11>60,if (f11>90), "excellent", "qualified"), "unqualified")

The syntax is interpreted as if the value of cell F11 is greater than 60, then the second argument is executed, where the value of the cell F11 continues to be judged to be greater than 90 (in order to give you an understanding of the application of the and function, write and (f11>90), which can actually be written only f11>90) , if the display of good words in cell F12, does not meet the display of qualified words, if the value of the F11 is not satisfied with the above conditions, then the execution of the third parameter is to display unqualified words in cell F12.

In the comprehensive evaluation column can be seen because of the students in the F column of the average divided into 92 points, comprehensive assessment of excellent