Rumus Excel: Cara Pakai Kombinasi IF, OR, AND #SekilasInsight

Summary

This tutorial explains how to use the IF function combined with OR and AND in Excel to automate decision-making processes based on certain conditions. The video demonstrates creating an action column based on achievement categories (low, average, high, very high), and how to assign specific actions like coaching or bonuses. It also covers how to calculate bonus percentages based on employee ratings and performance, with a step-by-step guide on setting up nested IF formulas. The video aims to improve Excel skills for real-world tasks like performance evaluation and reward management.

Takeaways

• 😀 Learn how to combine the IF formula with the OR function for decision-making in Excel.
• 😀 The video explains the use of IF and OR formulas to determine actions based on achievement categories.
• 😀 The IF formula checks for specific conditions, like 'low' or 'average' achievement categories, to assign actions like coaching or bonuses.
• 😀 The OR function allows multiple conditions to be checked, and if any of them are true, the result is returned as true.
• 😀 Use of absolute references (e.g., $A$11) to lock specific cells in the formula, ensuring accuracy when applying to multiple rows.
• 😀 The script demonstrates filling in the 'action' column based on the achievement category and assigning either 'coaching' or 'bonus'.
• 😀 The script walks through combining IF and AND functions to calculate bonus percentages based on specific criteria (like rating 'A' or 'B').
• 😀 The IF and AND functions allow for nested conditions, such as checking if both the action is 'bonus' and the rating is 'A' or 'B'.
• 😀 The video guides viewers through autofilling data once formulas are correctly set up, ensuring efficient data entry.
• 😀 The final result shows how to assign bonus percentages (100% or 50%) based on specific conditions, with empty values for others.

Q & A

What is the main focus of the tutorial in this transcript?

The tutorial focuses on using Excel functions like IF, OR, and AND to manage data based on specific conditions, such as categorizing achievements and determining actions like 'coaching' or 'bonus'.

What does the IF function do in this context?

The IF function is used to check if a condition is true or false. In this tutorial, it helps determine whether an action should be 'coaching' or 'bonus' based on specific conditions.

How is the OR function used in combination with the IF function in this script?

The OR function is used inside the IF function to check if at least one of the conditions is true. If the value in D2 is either 'low' or 'average', the action will be 'coaching'.

Why is the A11 and A12 cell locked with a dollar sign in the formula?

The dollar sign is used to lock the cell references (A11 and A12) so they don't change when the formula is copied down to other rows.

What does the AND function do in the second part of the tutorial?

The AND function checks if all conditions are true. In this case, it ensures that both the action is 'bonus' and the evaluation is 'A' or 'B' before calculating the bonus percentage.

How does the formula determine the bonus percentage for each person?

The bonus percentage is determined based on the evaluation in column C. If the action is 'bonus' and the evaluation is 'A', the bonus is 100%. If the evaluation is 'B', the bonus is 50%.

What happens when the conditions for 'bonus' and 'A' are not met in the formula?

If the conditions for 'bonus' and 'A' are not met, the formula checks if the conditions for a 'bonus' and 'B' are met, giving a 50% bonus if true. Otherwise, it returns an empty value.

Why is an empty value returned when the conditions for both 'A' and 'B' are not met?

An empty value is returned because the formula is designed to leave the bonus cell empty if neither the 'A' nor 'B' conditions are satisfied, ensuring that no bonus is calculated in such cases.

What does the 'double-click to autofill' instruction mean in Excel?

The 'double-click to autofill' instruction means that after writing the formula in the first cell, you can double-click the fill handle (the small square at the bottom-right corner of the cell) to automatically fill the formula down to other rows.

What is the significance of using the IF function twice in the bonus calculation formula?

Using the IF function twice allows for more complex decision-making. The first IF checks if the bonus conditions are met, while the second IF handles the different bonus percentages based on the evaluation score (A or B).