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 the result returned is a pass if the Chinese score is greater than or equal to 60 points; the third parameter "fail" indicates that the result returned is a fail if the Chinese score is less than 60 points.
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 of the IF function, D3>C3, 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" indicates 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: Calculate 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 [working hours] is greater than 8 hours; the second parameter (C3-8)*20 represents whether [working hours] is greater than 8 hours, and the returned result is TRUE, which means overtime, and then multiplied by 20 by exceeding the regular working hours; 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, indicates whether the [assessment score] is less than 70; the second parameter "training" indicates that the [assessment score] is less than 70, and the returned result is training; the third parameter "" indicates that the [assessment score] is greater than or equal to 70, and the returned result is "blank" (enter double quotes in English).
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" represents the assumption that [Position] is "Sales Manager" (when the text content is used as a judgment condition, the comparison operator equal sign is used to judge, indicating the same); the second parameter 800 represents the assumption that [Position] is "Sales Manager", and the returned result is 800; the third parameter 500 represents 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 of all, 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 function 1 Parameter C3>10000 means that assuming [sales] is above 10000, a performance commission of C3*10% will be returned. Finally, based on the performance comparison between the two intervals, the remaining performance commission is 10%, 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")
Explanation: The first parameter of the IF function N(D3)>=60 is because there are "absences" in the current examination results. If it is judged directly through the IF function according to the normal conditions, students who are absent from the examination will also be given a "passing" evaluation, so the N function is nested here: the value converted into a numerical value, this In this way, the "absent" score 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 "failed" means that the [Chinese] score is less than 60, and 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 and C levels 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="A-level",100,IF(D3="B-level",150,150))))
Note: Multi-condition judgment requires the use of multiple IF functions. First, the first IF function determines whether [department] is a "sales department", that is, C3="Sales Department". At that time, the sales department determines the level by nesting an IF. When the level is "Level A", the subsidy is 100, which is D3="Level A",100; when the level is "Level B", the subsidy is 20 0, the subsidy for "Level C" is 300, which is D3="Level A", 200; next, IF is used to determine whether the [department] is the "Planning Department", which is C3="Planning Department"; when the level is "Level A", the subsidy is 100, and the subsidy for "Level B" and "Level C" is 150, which is D3="Level 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 a "true" result be returned. If one of the conditions is not met, all of them will return a "false" result, that 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: To judge if one of the conditions is met, the OR function can be nested: it means that a "true" result can be returned only when one of the multiple conditions is met. If all conditions are not met, a "false" result will be returned, that is, OR (D3>80, E3>35000) ，根据嵌套OR函数条件判断，满足其中一个条件时返回的结果为“优秀”，都不满足时返回的结果为“空白”的效果，也就是最终公式IF(OR(D3>80,E3>35000),"优秀","")。
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 judge whether [Gender] is "Male" and whether [Age] reaches "60 years old"; Function parameter AND(C3="Female",D3>=55 means to judge whether [Gender] is "Female" and whether [Age] reaches "55 years old"; Function parameter 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!