Excel Conditional Functions Logic Operators and Referencing Notes

Excel Conditional Logic and Decision-Making Functions

  • Structure of the IF Statement:

    • The IF function evaluates a logical condition and returns one value if the condition is met (TRUE) and another value if it is not (FALSE).
    • Basic syntax standard: =IF(logical_test, value_if_true, value_if_false)
    • Arguments:
    • Logical Test: The condition or expression being evaluated.
    • Value if True: The action or output returned when the condition evaluates to true.
    • Value if False: The action or output returned when the condition evaluates to false.
  • Single-Condition Bonus Calculation Example:

    • Profit equation:     Profit=Sales−Cost\text{Profit} = \text{Sales} - \text{Cost}
    • Specified condition: Profit must be greater than 200200Profit>200\text{Profit} > 200
    • If true: Compute bonus as 10%10\% (0.100.10) of profit     Bonus=0.10×Profit\text{Bonus} = 0.10 \times \text{Profit}
    • If false: Display 00
    • Excel formula standard: =IF(Profit > 200, 0.10 * Profit, 0)

Logical Compound Operators: AND and OR Functions

  • Multi-Criteria Logic Using AND:

    • The AND function is used when all specified conditions must simultaneously evaluate to true.
    • Syntax structure: AND(condition1, condition2, ...)
    • Double Quotes Rule: Text strings in logical comparisons (such as region names) must always be enclosed in double quotation marks (e.g., "East").
    • Example criteria:
    • Criteria 1: Profit > 200200
    • Criteria 2: Region (located in cell C2) = "East"
    • Combined IF and AND formula standard:     =IF(AND(Profit > 200, C2 = "East"), 0.10 * Profit, 0)
  • Multi-Criteria Logic Using OR:

    • The OR function is used when at least one condition out of multiple criteria needs to evaluate to true.
    • Syntax structure: OR(condition1, condition2, ...)
    • Conceptual distinction:
    • AND requires all conditions to be met.
    • OR requires any single condition to be met.
    • Example criteria:
    • Criteria 1: Profit > 200200
    • Criteria 2: Number of units sold (located in cell F2) > 1515
    • Combined IF and OR formula standard:     =IF(OR(Profit > 200, F2 > 15), 0.10 * Profit, 0)

Nested IF Statements for Multiple Outcomes

  • Handling Scenario Outcomes Beyond Binary Options:

    • Standard IF functions only handle binary outcomes (true or false). When a dataset requires three or more possible outcomes (e.g., categorical outputs "small", "normal", and "large"), multiple IF statements must be nested within one another.
  • Nested Formula Implementation Example:

    • Categorization of Sales in cell A2 into three ranges:
    • Outcome 1: Sales ≤300\le 300 →\rightarrow Output "small"
    • Outcome 2: Sales ≤331\le 331 →\rightarrow Output "normal"
    • Outcome 3: Sales > 331331 →\rightarrow Output "large"
    • Combined nested IF formula standard:     =IF(A2 <= 300, "small", IF(A2 <= 331, "normal", "large"))
    • Text String Rule: Any returned text output string (such as "small", "normal", "large", or personal names) must always be placed within double quotes inside Excel formulas.

Conditional Summation Functions: SUMIF and SUMIFS

  • Overview of Conditional Summation Functions:

    • Used to aggregate numeric data dynamically based on criteria (e.g., summing regional sales).
    • SUMIFS Function: Introduced after Excel 20192019, this function supports single or multiple evaluation criteria. It is the designated standard for coursework, quizzes, and exams.
  • Syntax Comparison and Parameter Order:

    • SUMIFS Syntax:     =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
    • Parameter 1: sum_range (the column to add up).
    • Parameter 2: criteria_range1 (the column containing the data to filter).
    • Parameter 3: criteria1 (the filter condition).
    • SUMIF Syntax:     =SUMIF(criteria_range, criteria, [sum_range])
    • Parameter 1: criteria_range (the column containing the data to filter).
    • Parameter 2: criteria (the filter condition).
    • Parameter 3: sum_range (the column to add up).
    • Key Structural Difference: SUMIFS puts sum_range first, whereas SUMIF puts criteria_range first.
  • Single-Criterion Summation Example (East Region Sales in Cell P3):

    • Range parameters across rows 22 through 106106:
    • Sum Range: Sales in F2:F106
    • Criteria Range: Region in B2:B106
    • Criteria: "=East" or "East"
    • Standard SUMIFS formula: =SUMIFS(F2:F106, B2:B106, "=East")
    • Standard SUMIF formula equivalent: =SUMIF(B2:B106, "=East", F2:F106)
  • Alternative Summation via Helper Columns:

    • Aggregations can also be achieved by using a basic IF statement in an adjacent column to extract sales matching the target region, followed by a standard SUM function applied to the new helper column.

Multi-Criteria SUMIFS and Operator Guidelines

  • Multi-Criteria Summation Example:

    • Aggregating total sales for product "Bellin" restricted to the "West" region:
    • Sum Range: Sales in F2:F106
    • Criteria Range 1: Product column in A2:A106
    • Criterion 1: "Bellin" (or "=Bellin")
    • Criteria Range 2: Region column in B2:B106
    • Criterion 2: "West" (or "=West")
    • Formula standard:     =SUMIFS(F2:F106, A2:A106, "Bellin", B2:B106, "West")
  • Relational Operators in Criteria:

    • Explicit equals signs (=) are optional for text/value matching in criteria arguments.
    • Greater than (>) or less than (<) operators must be explicitly included inside double quotes within criteria arguments (e.g., ">200").

Cell Referencing and Absolute Reference Automation

  • Cell Reference vs. Hardcoded Values:

    • Criteria arguments can directly reference cell locations containing target values (e.g., cell M14 containing "East") instead of hardcoding text strings:     =SUMIFS(F2:F106, B2:B106, M14)
  • Down-Fill Range Drift Problem:

    • Standard relative down-filling shifts input ranges dynamically (e.g., changing F2:F106 to F3:F107), which corrupts dataset boundaries.
  • Absolute Referencing with F4:

    • Highlight the target range references (F2:F106 and B2:B106) and press key F4 once.
    • This inserts dollar signs ($) to lock range boundaries across row transformations:     $F$2:$F$106 and $B$2:$B$106
    • Standard locked formula:     =SUMIFS($F$2:$F$106, $B$2:$B$106, M14)
    • Down-filling result: Preserves input data range bounds static across rows 22 through 106106, while allowing target criterion cells (e.g., M14, M15, M16, M17) to update dynamically.

Course Operational Policies and Submissions

  • Canvas Submission:

    • Completed class spreadsheets must be submitted directly to Canvas under the designated class submission portal.
  • Physical Name Tag Requirements:

    • Name tags must be visibly displayed on desks during every class meeting starting Wednesday onwards.
    • Professional remarks and participation marks are updated after every individual class session based on compliance.
  • Academic Schedule Constraints:

    • Friday is off (no class session).
    • regular class meetings resume on Monday and Wednesday.