1. The first usage of IF function: single condition judgment

Purpose: To evaluate students based on their performance. If the Chinese score is greater than or equal to 60 points, the evaluation will be passed, otherwise it will be failed.

Formula: =IF(D3>=60,"pass","fail")

Explanation: The first parameter D3>=60 of the IF function indicates whether the [Chinese] score is greater than or equal to 60; the second parameter "pass" indicates that if the Chinese score is greater than or equal to 60 points, the returned result is pass; the third parameter The parameter "fail" means that when the Chinese score is less than 60 points, the returned result is failed.

2. The second usage of the IF function: Determine whether the budget has been exceeded

Purpose: Determine whether the budget has been exceeded. When the actual amount is greater than the budget amount, it is exceeded, otherwise it is not exceeded.

Formula: =IF(D3>C3,"Exceeded","Not exceeded")

Explanation: The first parameter D3>C3 of the IF function indicates whether the [actual amount] is greater than the [budget amount]; the second parameter "exceeds" indicates that the [actual amount] is greater than the [budget amount] and the returned result is excess. ;The third parameter "not exceeded" means that the [actual amount] is less than or equal to the [budget amount] and the returned result is not exceeded.

3. The third usage of IF function: calculating overtime allowance

Purpose: The regular working hours are 8 hours. If the working hours exceed 8 hours, the subsidy will be 20 yuan per hour.

Formula: =IF(C3>8,(C3-8)*20,0)

Explanation: The first parameter of the IF function, C3>8, represents whether the [working hours] is greater than 8 hours; the second parameter (C3-8)*20 represents whether the [working hours] is greater than 8 hours, and the returned result is TRUE. That is to say, for overtime work, then multiply the length of regular work by 20; the third parameter 0 represents less than or equal to 8 hours, and the returned result is FALSE, that is, no overtime is worked, and the overtime subsidy is 0.

4. The fourth usage of IF function: only display one result

Purpose: Arrange retraining when the employee’s quarterly assessment score is below 70 points

Formula: =IF(C3<70,"Training","")

Explanation: The first parameter of the IF function, C3<70, represents whether the [assessment score] is less than 70; the second parameter "training" represents the return result training if the [assessment score] is less than 70; the third parameter "" represents If the [assessment score] is greater than or equal to 70, the returned result will be "blank" (enter English double quotes).

5. The fifth usage of IF function: text condition judgment

Purpose: If you are a sales manager, you will receive a subsidy of 800 yuan for travel and accommodation expenses, and a subsidy of 500 yuan for other positions.

Formula: =IF(C3="Sales Manager",800,500)

Explanation: The first parameter of the IF function C3="Sales Manager" indicates that the [position] is "Sales Manager" (when the text content is used as the judgment condition, use the comparison operator equal sign to judge, which means the same); the second parameter 800 means assuming that [Position] is "Sales Manager", the returned result is 800; the third parameter 500 means that [Position] is not "Sales Manager", and the returned result is 800.

6. The sixth usage of IF function: multi-condition judgment to calculate performance commission

Purpose: Calculate performance commission. If the sales is more than 15,000, the commission is 15%; if the sales is more than 10,000, the commission is 10%; if the sales is more than 5,000, the commission is 5%

Formula: =IF(C3>15000,C3*15%,IF(C3>10000,C3*10%,C3*5%))

Note: Multi-condition judgment requires the use of multiple IF functions. First, the first parameter of the first IF function, C3>15000, means that assuming [sales] is above 15,000, a performance commission of C3*15% will be returned; the first IF The first parameter of the function C3>10000 means that assuming [sales] is above 10000, a performance commission of C3*10% will be returned. Finally, the remaining performance commission will be 10% based on the performance comparison between the two intervals, which is C3 *5%.

7. The seventh usage of the IF function: text to numerical conditional judgment

Purpose: To evaluate students based on their performance. If the Chinese score is greater than or equal to 60 points, the evaluation will be passed, otherwise it will be failed.

Formula: =IF(N(D3)>=60,"pass","fail")

Note: The first parameter of the IF function N(D3)>=60 is because there are "absent" in the current exam results. If it is judged directly through the IF function according to the normal conditions, "pass" will also be given to the students who are absent. ” evaluation, so the N function is nested here: the value converted into a numerical value, this In this way, the scores of "absent" will also be judged in numerical form; the second parameter "pass" means that assuming the [Chinese] score is greater than or equal to 60, the returned result is "pass"; the third parameter "fail" means What is more, if the [Chinese] score is less than 60, the returned result is "failed".

8. The 8th usage of IF function: multi-condition and multi-level nesting

Purpose: Telephone subsidy standards. If the department is the sales department and the level is A, the subsidy is 100 yuan, B level is 200 yuan, and C level is 300 yuan; if the department is the planning department and the level is A, the subsidy is 1,000 yuan, and B level is 1,000 yuan. and C-level are both 150 yuan.

Formula: =IF(C3="Sales Department",IF(D3="A-level",100,IF(D3="B-level",200,300)),IF(C3="Planning Department",IF(D3=" Class A",100,IF(D3="Class B",150,150))))

Note: Multi-condition judgment requires the use of multiple IF functions. First, the first IF function determines whether [department] is "Sales Department", that is, C3="Sales Department". At that time, the sales department determines the level by nesting an IF. When The subsidy for "Level A" is 100, which is D3="Level A", 100; the subsidy for "Level B" is 20 0, the level is "C-level" and the subsidy is 300, which is D3="A-level", 200; next, it is judged through IF whether the [department] is the "Planning Department", which is C3="Planning Department"; when the level is "A" The subsidy for "Grade B" is 100, and the subsidy for "Grade B" and "Grade C" is 150, that is, D3="Grade B",150,150.

9. The 9th usage of IF function: Judgment that satisfies multiple conditions at the same time

Purpose: To determine whether the student has passed the exam. The passing standard is: if the theoretical score and practical score are both greater than or equal to 60 points, it is passed, otherwise it is failed.

Formula: =IF(AND(D3>=60,E3>=60),"pass","not pass")

Note: For judgments that meet multiple specified conditions at the same time, the AND function can be nested: it means that only when multiple conditions are met at the same time can the "true" result be returned. If one of the conditions is not met, all of them will return "false" The result is AND(D3>=60,E 3>=60), according to the nested AND function condition judgment, the result returned is "pass" when it is satisfied, and the result returned is "fail" when it is not satisfied, that is, the final formula =IF(AND(D3>=60, E3>=60), "pass", "fail").

10. The 10th usage of the IF function: judging when one of the conditions is met

Purpose: Employee evaluation and selection. Sales volume greater than 80 or sales volume greater than 35,000 are the criteria for excellent evaluation.

Formula: =IF(OR(D3>80,E3>35000),"Excellent","")

Note: For the judgment that one of the conditions is met, the OR function can be nested: it means that the "true" result can be returned only when one of the multiple conditions is met. If all the conditions are not met, the "false" result will be returned, that is, OR( D3>80,E3>35000) , based on the conditions of the nested OR function, when one of the conditions is met, the result returned is "excellent", and when neither condition is met, the result returned is "blank", which is the final formula IF(OR(D3>80,E3> 35000),"Excellent","").

11. The 11th usage of IF function: judgment with AND and OR

Purpose: Determine whether an employee has retired. If a male employee reaches the age of 60, he will retire; if a female employee reaches the age of 55, he will retire.

Formula: =IF(OR(AND(C3="Male",D3>=60),AND(C3="Female",D3>=55)),"Retirement","")

Explanation: Function parameter AND(C3="Male",D3>=60) means to determine whether [gender] is "male" and whether [age] reaches "60 years old"; function parameter AND(C3="female",D3> =55 means to determine whether [gender] is "female" and whether [age] reaches "55 years old"; function parameters OR(AND(C3="Male",D3>=60),AND(C3="Female",D3>=55)) means to determine whether it is a male employee and the age reaches 60 years old, or whether it is a female employee and the age reaches 55 years old. Finally, the IF function is used to determine whether the result is "retirement" or "blank".

12. The 12th usage of IF function: conditional judgment and summation

Purpose: Calculate the total sales of Sales Department 2

Formula: =SUM(IF(C3:C8="Part 2",D3:D8,""))

The above is the relevant content about how to use the if function. I hope it will be helpful to you!