Here are some examples of IF-THEN statements in Excel:
1. Grading System:
- Assign grades based on student performance.
- Formula:
excel =IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", "D"))) - Explanation: This formula checks the score in cell A2 and assigns an appropriate grade.
2. Sales Commission Calculation:
- Scenario: Calculate different commission rates based on sales amounts.
- Formula:
excel =IF(B2>=10000, B2*0.1, IF(B2>=5000, B2*0.07, B2*0.05)) - Explanation: This formula calculates commission rates based on the sales amount in cell B2.
3. Product Categorization:
- Scenario: Categorize products into different price ranges.
- Formula:
excel =IF(C2<10, "Low", IF(C2<50, "Medium", "High")) - Explanation: This nested IF statement checks the price in cell C2 and categorizes it into low, medium, or high.
4. Date Validation:
- Scenario: Check if a cell contains a valid date.
- Formula:
excel =IF(ISDATE(A2), "Valid Date", "Not a Date") - Explanation: This formula uses IF with ISDATE to determine if the value in cell A2 is a valid date.
5. Project Evaluation:
- Scenario: Check if a project is on schedule and within budget.
- Formula:
excel =IF(AND(B2="On Schedule", C2<=50000), "Success", "Issues") - Explanation: This formula uses the AND function to check if the project is on schedule and within budget.
6. Error Handling:
- Scenario: Avoid errors in a calculation.
- Formula:
excel =IFERROR(A2/B2, "Error in Calculation") - Explanation: This formula calculates A2 divided by B2, displaying an error message if there’s an issue.
These examples show Excel’s IF-THEN statements’ versatility, ranging from simple grading systems to more complex scenarios involving multiple conditions and error handling. Understanding and applying these formulas can significantly improve your Excel data analysis and manipulation skills.