Logical Analysis and Decision Rules

Introduction

Business analysts routinely convert managerial policies into operational rules. Examples include classifying customers by value, approving or rejecting transactions, identifying at-risk employees, assigning service levels, and determining whether performance targets have been achieved. In Excel, logical functions provide a transparent way to express these rules and apply them consistently across large datasets.

A logical formula evaluates a condition as TRUE or FALSE and then uses that result to return a label, number, message, or action. The quality of a decision model depends not only on correct syntax, but also on precise policy interpretation, appropriate treatment of missing or exceptional data, and systematic testing. A formula that is technically valid can still produce poor business decisions if the underlying rule is ambiguous or if conditions are evaluated in the wrong order.

This chapter develops a framework for using IF, IFS, AND, OR, NOT, SWITCH, conditional aggregation, and nested logic. It also examines model transparency, exception handling, governance, and the limitations of spreadsheet-based decision systems.

Learning Objectives

By the end of this chapter, you should be able to:

• Translate managerial statements into explicit logical conditions.
• Use IF, IFS, AND, OR, NOT, and SWITCH appropriately.
• Combine logical functions to model policies, classifications, thresholds, and exceptions.
• Apply COUNTIF(S), SUMIF(S), and AVERAGEIF(S) to conditional analysis.
• Design formulas that are readable, testable, and maintainable.
• Identify logical errors caused by order, overlap, missing data, and boundary conditions.
• Evaluate the governance and ethical implications of automated spreadsheet decisions.

1. Business Logic as a Model

A business rule has three essential components:

1. Input variables: values used in the decision, such as revenue, credit score, region, or delivery time.
2. Conditions: tests applied to those values, such as revenue ≥ 100,000 or region = "East".
3. Outputs: the resulting classification, action, or measurement.

For example, a simplified credit policy might state:

• Approve an application if the credit score is at least 700 and debt-to-income ratio is no more than 35%.
• Refer it for review if the score is between 600 and 699.
• Reject it otherwise.

The policy should first be represented conceptually before it is written as a formula. This separation helps distinguish business meaning from Excel syntax.

A condition is a proposition that can be TRUE or FALSE. Common comparison operators are:

Text comparisons require quotation marks. Cell references, numbers, and dates generally do not. A date criterion is often safest when represented using a cell containing a valid Excel date rather than embedding a locale-dependent text date in a formula.

2. The IF Function

The basic structure is:

=IF(logical_test, value_if_true, value_if_false)

For example:

=IF(C2>=100000,"Target achieved","Below target")

If C2 contains 125,000, the formula returns “Target achieved.” If it contains 90,000, it returns “Below target.”

IF can return text, numbers, dates, another formula, or a blank string (""). A blank result is useful for presentation, but it should not be confused with a genuinely missing value. Hiding missing information can create analytical risk.

A numerical decision can also be expressed using IF. Suppose a sales representative receives a 5% commission on revenue above 50,000 and no commission below that threshold:

=IF(B2>50000,(B2-50000)*5%,0)

The formula assumes that commission applies only to the amount above 50,000, not to total revenue. If the policy instead grants 5% on all revenue once the threshold is reached, the correct formula would be =IF(B2>=50000,B2*5%,0). This illustrates why policy wording must be clarified before formula construction.

3. Combining Conditions with AND, OR, and NOT

AND

AND returns TRUE only when every supplied condition is TRUE:

=AND(B2>=700,C2<=35%)

An approval rule can therefore be written as:

=IF(AND(B2>=700,C2<=35%),"Approve","Do not approve")

AND represents a logical intersection: all requirements must be satisfied.

OR

OR returns TRUE when at least one supplied condition is TRUE:

=OR(D2="Priority",E2="Escalated")

For example:

=IF(OR(D2="Priority",E2="Escalated"),"Immediate attention","Standard queue")

OR represents alternative pathways. It is important to distinguish “at least one” from “exactly one.” OR does not require that only one condition be true.

NOT

NOT reverses a logical result:

=NOT(F2="Complete")

This can be useful when a rule is stated as an exception, although direct comparisons are often easier to read. For example, =IF(NOT(F2="Complete"),"Follow up","No action") identifies records not marked complete.

Combining logical functions

Functions can be nested:

=IF(AND(B2>=700,OR(C2="Low",C2="Medium")),"Eligible","Review")

This means the applicant must have a score of at least 700 and must belong to either the Low or Medium risk category.

4. Multiple Categories: Nested IF and IFS

A nested IF places one IF inside another:

=IF(B2>=90,"Excellent",IF(B2>=75,"Good",IF(B2>=60,"Satisfactory","Unsatisfactory")))

Excel evaluates the conditions from left to right. Once a TRUE condition is found, later conditions are not used. Therefore, threshold rules should normally be arranged from highest to lowest when using descending cut-offs.

The IFS function is often more readable:

=IFS(B2>=90,"Excellent",B2>=75,"Good",B2>=60,"Satisfactory",TRUE,"Unsatisfactory")

The final TRUE acts as a default condition. Without a condition that catches all remaining cases, IFS may return an error when no test is TRUE.

Boundary conditions

Suppose a policy defines performance as:

• High: ≥ 90%
• Medium: ≥ 75% and < 90%
• Low: < 75%

The formula =IFS(B2>=90%,"High",B2>=75%,"Medium",TRUE,"Low") correctly handles the boundaries. At exactly 90%, the first condition applies; at exactly 75%, the second applies.

A common error is overlapping or contradictory ranges, such as “High if ≥ 90” followed by “Medium if ≥ 80,” without understanding that values of 90 or more will never reach the second test. This may be correct if the order is intentional, but it should be documented.

5. SWITCH for Discrete Categories

SWITCH compares one expression with a list of possible values:

=SWITCH(A2,"East","Region 1","West","Region 2","North","Region 3","Other")

This is appropriate when one field contains discrete codes or categories. It is generally clearer than a long chain of equality-based IF statements.

SWITCH is not usually the best choice for ranges. A performance score of 82 is not a discrete code, so IFS or nested IF is more suitable. SWITCH can also use an expression such as TRUE to imitate condition testing:

=SWITCH(TRUE,B2>=90,"High",B2>=75,"Medium",TRUE,"Low")

Although this works, IFS is usually more immediately understandable for ordered conditions.

6. Conditional Aggregation

Logical analysis is not limited to row-by-row classification. Conditional aggregation summarizes only records meeting specified criteria.

COUNTIF and COUNTIFS

COUNTIF counts cells meeting one condition:

=COUNTIF(D2:D101,"East")

COUNTIFS counts rows satisfying multiple conditions:

=COUNTIFS(B2:B101,"East",C2:C101,">=100000")

The formula counts East-region records with revenue of at least 100,000.

SUMIF and SUMIFS

SUMIF adds values associated with one condition:

=SUMIF(B2:B101,"East",C2:C101)

SUMIFS supports multiple conditions:

=SUMIFS(D2:D101,B2:B101,"East",C2:C101,">=100000")

The first range in SUMIFS is the sum range, followed by pairs of criteria ranges and criteria.

AVERAGEIF and AVERAGEIFS

=AVERAGEIF(B2:B101,"East",C2:C101) calculates average revenue for the East region. AVERAGEIFS extends this to multiple conditions.

Criteria involving a cell reference must be concatenated. If F2 contains a threshold, use:

=COUNTIF(C2:C101,">="&F2)

The operator is text and the threshold is supplied by F2.

Consistency requirements

Conditional aggregation requires ranges with compatible dimensions. If a sum range contains 100 rows but a criteria range contains 99 rows, the result may be incorrect or misleading. Structured Excel Tables are useful because their column references expand automatically when new rows are added.

7. A Framework for Translating Policies into Formulas

A reliable process contains six steps.

Step 1: Identify inputs and outputs

List the fields that influence the decision and define the required output. Avoid using columns whose meaning is unclear.

Step 2: Rewrite the policy as atomic rules

Break statements into small conditions. For example, “Priority customers are high-value customers who have not complained in the last 30 days” becomes:

• customer value ≥ threshold;
• complaint date is blank or older than 30 days;
• both conditions are required.

Step 3: Resolve ambiguity

Clarify whether “more than” means > or >=, whether missing values qualify as exceptions, and whether a threshold applies to the entire amount or only the incremental amount.

Step 4: Order rules

Place specific exceptions before general classifications. Place higher thresholds before lower thresholds when using descending categories.

Step 5: Build and test the formula

Test normal, boundary, missing, invalid, and exception cases. Do not test only a few convenient records.

Step 6: Document the rule

Use a nearby policy table, comments, or a separate rule sheet. Complex business logic should not exist only inside a cell formula.

8. Worked Example: Service-Level Classification

Assume a service organization classifies cases using the following policy:

• Critical if the case is marked “Escalated,” or if response time exceeds 48 hours and customer value is at least 100,000.
• Priority if not Critical and response time exceeds 24 hours.
• Standard otherwise.
• Invalid if response time is missing or negative.

Suppose:

• B2 contains escalation status;
• C2 contains response time in hours;
• D2 contains customer value.

A robust formula is:

=IF(OR(C2="",C2<0),"Invalid",IF(OR(B2="Escalated",AND(C2>48,D2>=100000)),"Critical",IF(C2>24,"Priority","Standard")))

The formula first checks data validity. It then checks the critical rule, including its alternative pathways, and finally applies the priority rule. The order matters: a case satisfying the critical rule and the priority rule must be labelled Critical.

Consider four records:

The first record is Critical because escalation alone is sufficient. The second is Critical because both response time and customer value meet the second critical pathway. The third is Priority because it exceeds 24 hours but does not meet a critical condition.

For maintainability, a helper-column design may be preferable. Separate columns can calculate Valid?, Critical?, and Priority?, followed by a final classification. This increases visible columns but makes testing and auditing easier.

9. Applied Case: Illustrative Retail Credit Review

Background

A fictional retailer offers installment credit. Management wants a spreadsheet to classify applications as Auto-approve, Manual review, or Decline. The policy is:

• Auto-approve when credit score is at least 720, debt-to-income ratio is at most 30%, and no fraud flag exists.
• Manual review when the score is at least 650 and either debt-to-income ratio is between 30% and 40% or the application has a documentation warning.
• Decline otherwise.
• Any fraud flag requires Manual review, regardless of score.

Analysis

The fraud exception must be evaluated before automatic approval. Otherwise, an application with an excellent score could be incorrectly approved. The policy also contains an important distinction: fraud does not necessarily mean decline; it means manual review.

Assume:

• B2 = credit score;
• C2 = debt-to-income ratio;
• D2 = fraud flag, containing “Yes” or “No”;
• E2 = documentation warning, containing “Yes” or “No.”

A formula is:

=IF(D2="Yes","Manual review",IF(AND(B2>=720,C2<=30%,D2="No"),"Auto-approve",IF(AND(B2>=650,OR(AND(C2>30%,C2<=40%),E2="Yes")),"Manual review","Decline")))

The explicit D2="No" in the approval test makes the exclusion visible, even though the first IF already captures fraud. This redundancy can support readability, but excessive redundancy can create maintenance problems if rules change. An alternative is to document the precedence clearly and omit the repeated test.

Boundary testing

The following test cases should be included:

Testing values exactly at and just around each boundary detects many logical errors. The case with 30% demonstrates that “at most 30%” includes the boundary, whereas 30.1% does not.

Outcome and learning points

The model translates the policy into a repeatable classification, but implementation should not be treated as a substitute for credit governance. A manual reviewer should be able to see the reasons for a classification, not merely its final label. Helper columns such as Fraud exception, Approval criteria met, and Review criteria met can provide an audit trail.

10. Error Handling, Missing Data, and Exceptions

Logical formulas often behave unexpectedly when data is blank, text is stored as a number, or an error value is present. Before using IF, determine how missing values should be treated. A blank income field might mean zero, unknown, or not applicable; these meanings are not interchangeable.

IFERROR can provide a controlled result when a calculation produces an error:

=IFERROR(A2/B2,"Check data")

However, using IFERROR indiscriminately can hide genuine defects. A model should distinguish between an expected exception and a formula or data-quality error.

Useful validation practices include:

• restricting category fields with data validation;
• checking numeric fields for negative or impossible values;
• flagging blanks separately from valid zero values;
• standardizing text such as “Yes,” “YES,” and “Y”;
• using TRIM or data-cleaning procedures where spaces may affect comparisons;
• testing error values before aggregation.

11. Transparency, Testing, and Governance

A decision spreadsheet is a small information system. It should therefore have controls comparable to other analytical systems.

Transparency

Use meaningful column names, consistent indentation, and separate inputs from calculations. Avoid deeply nested formulas when helper columns or lookup tables would make the rule clearer.

Test design

A decision table is often more reliable than informal inspection. Include:

• ordinary cases;
• every boundary value;
• values just below and above boundaries;
• simultaneous conditions;
• conflicting conditions;
• missing and invalid inputs;
• exception cases;
• unexpected categories.

Version control

Record the policy owner, effective date, formula version, and changes made. If a business rule changes from “at least 700” to “greater than 700,” the change should be traceable.

Ethical and regulatory considerations

Automated classifications can affect credit, employment, pricing, insurance, and access to services. Analysts should examine whether inputs act as proxies for protected characteristics, whether outcomes are consistent across groups, and whether individuals can challenge an adverse decision. A spreadsheet should not conceal accountability behind a formula. Human review, access controls, secure storage, and appropriate retention policies may be required.

12. Contemporary Practice and Limitations

Modern Excel supports dynamic arrays, structured references, LET, LAMBDA, and Power Query, which can improve consistency and reuse. LET can assign names to intermediate expressions, making a long rule easier to read. LAMBDA can encapsulate a repeated business rule. These tools are useful extensions, but they do not remove the need for sound policy definition and testing.

AI assistants may help propose formulas or identify possible edge cases, but generated formulas must be verified against the approved policy. AI can misunderstand ambiguous language, mishandle missing data, or produce a formula that appears plausible but evaluates conditions in the wrong order. The analyst remains responsible for validation, documentation, and governance.

Spreadsheet logic also has limits. Very large datasets, highly interdependent rules, frequent policy changes, or legally sensitive decisions may require a database, rule engine, statistical model, or governed application. Excel is valuable because it is accessible and inspectable, but accessibility can also encourage uncontrolled copies and inconsistent versions.

Best-Practice Checklist

• State the policy in plain language before writing formulas.
• Use explicit boundaries such as >= and <=.
• Put exceptions and higher-priority rules first.
• Use AND for required conditions and OR for alternative pathways.
• Use IFS for ordered ranges and SWITCH for discrete categories.
• Use conditional aggregation for multi-record summaries.
• Separate data validation, rule evaluation, and presentation where possible.
• Test boundaries, blanks, errors, and conflicting conditions.
• Prefer readable helper columns over opaque nesting.
• Document ownership, assumptions, effective dates, and changes.
• Protect sensitive workbooks and restrict editing of formulas.
• Treat automated outcomes as governed decisions, not merely calculations.

Chapter Summary

Logical analysis in Excel converts business policies into testable conditions and outputs. IF provides the basic two-way decision structure, while AND, OR, and NOT combine or reverse conditions. Nested IF and IFS support ordered classifications, whereas SWITCH is effective for discrete categories. COUNTIF(S), SUMIF(S), and AVERAGEIF(S) extend logical analysis to summaries across multiple records.

Reliable decision models require more than correct syntax. Analysts must resolve ambiguity, define boundary behavior, order exceptions correctly, handle missing data explicitly, and test normal, edge, and invalid cases. Transparent helper columns, decision tables, documentation, and version control improve auditability. Because spreadsheet rules can influence important personal and commercial outcomes, ethical review, data protection, human oversight, and governance are essential.

Key Terms

Logical test: A condition that evaluates to TRUE or FALSE.

IF: A function that returns one result when a condition is TRUE and another when it is FALSE.

AND: A function that returns TRUE only when all supplied conditions are TRUE.

OR: A function that returns TRUE when at least one supplied condition is TRUE.

NOT: A function that reverses TRUE to FALSE or FALSE to TRUE.

IFS: A function that evaluates multiple conditions in sequence.

SWITCH: A function that matches an expression to discrete values or cases.

Nested logic: A logical function placed inside another function.

Condition: A test based on values, text, dates, or other expressions.

Threshold: A value at which a classification or action changes.

Boundary condition: A case exactly at, just below, or just above a threshold.

Exception rule: A rule that overrides a general rule.

Conditional aggregation: Summarizing values only for records meeting criteria.

COUNTIFS: A function that counts records meeting multiple conditions.

SUMIFS: A function that sums values associated with multiple conditions.

AVERAGEIFS: A function that calculates an average under multiple conditions.

Decision table: A structured representation of conditions, combinations, and outcomes.

Data validation: Controls that restrict or check allowable input values.

Auditability: The ability to inspect, explain, and reproduce a decision.

Helper column: An intermediate calculation used to make logic clearer and easier to test.

Review Questions

Short Answer Questions

1. What is the difference between AND and OR in a business rule?
2. When is SWITCH generally more suitable than IFS?
3. Why should exception rules often be evaluated before general rules?
4. What is the purpose of a default TRUE condition in an IFS formula?
5. Why are boundary values important in testing logical formulas?

Long Answer Questions

1. Explain how a managerial policy can be translated into inputs, conditions, and outputs. Illustrate your answer with an Excel example.
2. Compare nested IF, IFS, and SWITCH in terms of use cases, readability, and limitations.
3. Explain how COUNTIFS and SUMIFS can be used to create a regional performance report.
4. Discuss the treatment of missing, invalid, and blank data in decision formulas.
5. Evaluate the governance and ethical risks of using Excel to automate credit or employment decisions.

Analytical / Application Questions

1. Design a formula that classifies an order as Urgent when it is either flagged as expedited or is more than 72 hours late and worth at least 10,000; otherwise classify it as Normal. Include an invalid-data check.
2. A policy states that customers with revenue of at least 100,000 are High value, those with revenue from 50,000 to less than 100,000 are Medium value, and all others are Low value. Write an IFS formula and identify the correct results for values of 50,000, 99,999, and 100,000.
3. Construct a test plan for a formula that approves applications only when score is at least 700, debt-to-income ratio is no more than 35%, and fraud status is No. Include normal, boundary, exception, and missing-data cases.

Multiple Choice Questions with Answers

Question 1

Which Excel function returns TRUE only when all supplied conditions are TRUE?

A. OR

B. AND

C. NOT

D. SWITCH

Correct Answer: B. AND

Question 2

Which formula correctly labels revenue in C2 as achieved when it is at least 100,000?

A. =IF(C2>100000,"Achieved","Below")

B. =IF(C2<100000,"Achieved","Below")

C. =IF(C2>=100000,"Achieved","Below")

D. =IF(C2=100000,"Achieved","Below")

Correct Answer: C. =IF(C2>=100000,"Achieved","Below")

Question 3

Which function is generally most appropriate for classifying a score into ordered ranges?

A. IFS

B. SWITCH

C. COUNTIF

D. SUMIF

Correct Answer: A. IFS

Question 4

What does OR return when two conditions are supplied and exactly one condition is TRUE?

A. TRUE

B. FALSE

C. An error

D. A blank

Correct Answer: A. TRUE

Question 5

Which formula counts East-region records with revenue of at least 100,000?

A. =COUNTIF(B2:B101,"East",C2:C101,">=100000")

B. =COUNTIFS(B2:B101,"East",C2:C101,">=100000")

C. =SUMIFS(B2:B101,"East",C2:C101,">=100000")

D. =COUNTIFS(B2:B101,"East")

Correct Answer: B. =COUNTIFS(B2:B101,"East",C2:C101,">=100000")

Question 6

In an IFS formula, what is the purpose of a final TRUE condition?

A. It forces every previous condition to be TRUE

B. It provides a default result when earlier conditions are FALSE

C. It converts text to numbers

D. It reverses the previous condition

Correct Answer: B. It provides a default result when earlier conditions are FALSE

Question 7

A policy says a case is Critical if it is Escalated OR if response time exceeds 48 hours AND customer value is at least 100,000. Which logical structure represents this rule?

A. AND(status="Escalated",OR(time>48,value>=100000))

B. OR(status="Escalated",AND(time>48,value>=100000))

C. OR(status="Escalated",time>48,value>=100000)

D. AND(status<>"Escalated",time>48,value>=100000)

Correct Answer: B. OR(status="Escalated",AND(time>48,value>=100000))

Question 8

Why should a formula explicitly distinguish a blank value from zero when the business meanings differ?

A. Excel cannot calculate zero

B. Blank and zero may represent different business states

C. Blank values are always errors

D. Zero cannot be used in IF formulas

Correct Answer: B. Blank and zero may represent different business states

Question 9

Which function is designed to compare one expression against a list of discrete cases?

A. SWITCH

B. AVERAGEIFS

C. NOT

D. SUMIFS

Correct Answer: A. SWITCH

Question 10

What is the main purpose of testing values just below and just above a threshold?

A. To make formulas longer

B. To identify boundary and classification errors

C. To remove all blank cells

D. To replace data validation

Correct Answer: B. To identify boundary and classification errors