Excel Conditional Functions Logic Operators and Referencing Notes
Excel Conditional Logic and Decision-Making Functions
Structure of the IF Statement:
- The
IFfunction 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.
- The
Single-Condition Bonus Calculation Example:
- Profit equation:
- Specified condition: Profit must be greater than
- If true: Compute bonus as () of profit
- If false: Display
- Excel formula standard:
=IF(Profit > 200, 0.10 * Profit, 0)
Logical Compound Operators: AND and OR Functions
Multi-Criteria Logic Using AND:
- The
ANDfunction 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 >
- Criteria 2: Region (located in cell
C2) ="East" - Combined
IFandANDformula standard:=IF(AND(Profit > 200, C2 = "East"), 0.10 * Profit, 0)
- The
Multi-Criteria Logic Using OR:
- The
ORfunction is used when at least one condition out of multiple criteria needs to evaluate to true. - Syntax structure:
OR(condition1, condition2, ...) - Conceptual distinction:
ANDrequires all conditions to be met.ORrequires any single condition to be met.- Example criteria:
- Criteria 1: Profit >
- Criteria 2: Number of units sold (located in cell
F2) > - Combined
IFandORformula standard:=IF(OR(Profit > 200, F2 > 15), 0.10 * Profit, 0)
- The
Nested IF Statements for Multiple Outcomes
Handling Scenario Outcomes Beyond Binary Options:
- Standard
IFfunctions only handle binary outcomes (true or false). When a dataset requires three or more possible outcomes (e.g., categorical outputs"small","normal", and"large"), multipleIFstatements must be nested within one another.
- Standard
Nested Formula Implementation Example:
- Categorization of Sales in cell
A2into three ranges: - Outcome 1: Sales Output
"small" - Outcome 2: Sales Output
"normal" - Outcome 3: Sales > Output
"large" - Combined nested
IFformula 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.
- Categorization of Sales in cell
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).
SUMIFSFunction: Introduced after Excel , this function supports single or multiple evaluation criteria. It is the designated standard for coursework, quizzes, and exams.
Syntax Comparison and Parameter Order:
SUMIFSSyntax:=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). SUMIFSyntax:=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:
SUMIFSputssum_rangefirst, whereasSUMIFputscriteria_rangefirst.
Single-Criterion Summation Example (East Region Sales in Cell P3):
- Range parameters across rows through :
- Sum Range: Sales in
F2:F106 - Criteria Range: Region in
B2:B106 - Criteria:
"=East"or"East" - Standard
SUMIFSformula:=SUMIFS(F2:F106, B2:B106, "=East") - Standard
SUMIFformula equivalent:=SUMIF(B2:B106, "=East", F2:F106)
Alternative Summation via Helper Columns:
- Aggregations can also be achieved by using a basic
IFstatement in an adjacent column to extract sales matching the target region, followed by a standardSUMfunction applied to the new helper column.
- Aggregations can also be achieved by using a basic
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")
- Aggregating total sales for product
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").
- Explicit equals signs (
Cell Referencing and Absolute Reference Automation
Cell Reference vs. Hardcoded Values:
- Criteria arguments can directly reference cell locations containing target values (e.g., cell
M14containing"East") instead of hardcoding text strings:=SUMIFS(F2:F106, B2:B106, M14)
- Criteria arguments can directly reference cell locations containing target values (e.g., cell
Down-Fill Range Drift Problem:
- Standard relative down-filling shifts input ranges dynamically (e.g., changing
F2:F106toF3:F107), which corrupts dataset boundaries.
- Standard relative down-filling shifts input ranges dynamically (e.g., changing
Absolute Referencing with F4:
- Highlight the target range references (
F2:F106andB2:B106) and press keyF4once. - This inserts dollar signs (
$) to lock range boundaries across row transformations:$F$2:$F$106and$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 through , while allowing target criterion cells (e.g.,
M14,M15,M16,M17) to update dynamically.
- Highlight the target range references (
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.