[P4] Fungsi Logika & Pengambilan Keputuan Excel APKA

Main Ideas and Lessons (Logic Functions & Decision Making in Excel)

• Purpose of logical functions in Excel:

  • Excel can evaluate conditions and return different outputs depending on whether a condition is TRUE or FALSE.
  • Logical functions are used for decision-making automation in data processing (e.g., pass/fail, graduation status, scholarship eligibility).

• Core concept:

  • Conditions typically result in two outcomes: TRUE or FALSE.
  • By using logical functions, Excel can automatically classify or generate labels/text based on rules.

---

Methodology / Instructional Breakdown (What the Video Teaches)

A) Pass/Fail using single IF

• Goal: Produce a pass or fail decision based on a passing threshold.
• Rule concept:
  • If a student’s score (example: 80) is ≥ the minimum passing score (example: 60), output “pass”.
  • Otherwise output “fail”.
• Implementation approach (as described):
  • Click the first output cell in the pass/fail column (e.g., G2).
  • Enter a formula like:
    • IF(score >= passing_threshold, "pass", "fail")
  • Copy/drag the formula down (e.g., to G3–G11 or the required range).

---

B) Categorization with nested IF (compound outcomes)

• Goal: Return more than two possible outcomes (e.g., multiple roles or predicates).
• Rule concept:
  • Use multiple IF checks sequentially inside one formula.
  • The first matching condition determines the output.
• Implementation approach (as described):
  • In a cell such as I2, use a nested IF structure, for example:
    • if score ≥ 85 → A
    • else if score ≥ 75 → B
    • else if score ≥ 65 → C
  • Copy/drag the formula down across rows.

---

C) Multi-condition AND logic with IF + AND (“n/and”)

• Goal: Return TRUE only when all conditions are met simultaneously.
• Rule concept:
  • TRUE if every condition is TRUE.
  • FALSE if any condition is FALSE.
• Scholarship example (conceptualized in the video):
  • Scholarship eligibility requires meeting requirements based on UTS and UAS; using only one is insufficient.
• Implementation approach (as described):
  • In the scholarship status/recipient column (video references C2), use an IF with AND logic like:
    • IF(AND(UTS condition, UAS condition), scholarship_value_or_status, alternative)
  • Copy/drag the formula down.

---

D) Remedial/exam decision with IF + OR

• Goal: Identify students who need remedial when at least one condition is met.
• Rule concept:
  • TRUE if one or both conditions are TRUE.
  • FALSE only if all conditions are FALSE.
• Remedial example (attendance/grade logic):
  • “Special attention / remedial” if:
    • assignment score is below a threshold OR
    • mid-term score is below a threshold.
• Implementation approach (as described):
  • Use IF with OR in the retest/remedial status column (video references copying logic and gives examples, such as Andi vs. Joko).

---

E) Combine IF with text-manipulation functions (IF + LEFT / PROPER / CONCATENATE)

• Goal: Format or construct output text based on conditions.
• General rule format mentioned:
  • IF(condition, text_if_true, text_if_false)
• Typically combined with text functions:
  • LEFT, RIGHT, MID, UPPER, LOWER, PROPER, CONCATENATE (as listed in the video).
• Specific examples given:
  • IF + LEFT
    • Use product-code prefixes (e.g., first two letters) to determine category/type.
    • Example idea: if code starts with AB → output a category/type (example referenced as “ATK”).
  • IF + PROPER
    • Convert name formatting into “capital at the beginning” style.
    • Example shown: “dinda” → capitalized format.
  • IF + CONCATENATE (joining text)
    • Combine a greeting with a student/name field based on code/category condition.
    • Output described like: “welcome …” vs. “welcome guest” depending on the match.

---

F) Error handling with IFERROR

• Goal: Prevent messy Excel error messages by returning a clean alternative value.
• Syntax idea described:
  • IFERROR(formula, value_if_error)
• Why needed (example logic):
  • Errors can occur during calculations, such as division by zero (e.g., A2/B2 when B2 = 0).
• Implementation approach (as described):
  • In the “report protection / data protection” column (video references L2):
    • Wrap the calculation with IFERROR.
    • If invalid/zero/missing input causes an error, display a clearer message like “data invalid / does not exist” instead of Excel’s default error output.

---

Case Study Application (How the Course Uses the Logic)

• The video demonstrates logical decision making using an academic evaluation + student graduation case study (10 students) for five departmental policies, including:
  1. Course graduation: decide who graduates vs. who must repeat.
  2. Accreditation predicate grouping: assign mastery levels (A, B, C, D, etc.).
  3. Scholarships for high achievers: determine eligibility using exam scores (UTS + UAS).
  4. Media program screening / remedial identification: identify students needing additional help.
  5. Validation and protection of report data: prevent system error messages in reports (using IFERROR).

---

Practical Steps (Summary of What to Do in Excel)

• Open the previous Excel file from prior meetings (the video says the worksheet is similar/basic).
• Step 1: Create a pass/fail column using IF based on the passing threshold (copy/drag).
• Step 2: Create an accreditation predicate column using nested IF (multi-threshold ranges).
• Step 3: Create scholarship recipient/status using IF + AND (requires both relevant scores to meet criteria).
• Step 4: Create retest/remedial status using IF + OR (needs improvement if one of the conditions triggers).
• Step 5: Protect/clean report calculations using IFERROR so invalid divisions don’t show raw Excel errors.
• Assignment / post-test announced:
  • Store a sales case study using Excel tables and add columns for automatic info (sales status, stock status, promotions, stock priority, sales categories).
  • Submit in PDF by the deadline mentioned in the video.

---

Speakers / Sources Featured

• Speaker: The course instructor (no name given in the subtitles; spoken throughout).
• Source/material referenced: Microsoft Excel and the course module/document (“modul”) from the “number pattern application course,” including practical/post-test materials.