Lesson 4.2 – Advanced Logical & Statistical Functions in Excel

Lesson Overview

Logical and statistical functions help perform decision-making calculations and analyze data efficiently. Advanced usage includes nested logic, conditional aggregation, and multiple criteria calculations.

1. Logical Functions

1.1 IF Function

• Returns a value based on a condition

• Syntax:

• Example:
  Check if sales > 50,000:

1.2 Nested IF Statements

• Use multiple IFs to evaluate more than two conditions

• Example:

1.3 AND & OR Functions

• AND: Returns TRUE if all conditions are TRUE

• OR: Returns TRUE if at least one condition is TRUE

• Can be combined with IF

• Example (AND):

• Example (OR):

1.4 IFS Function

• Simplifies multiple conditions without nesting

• Syntax:

• Example:

2. Conditional Aggregation Functions

2.1 SUMIFS Function

• Sum values based on multiple criteria

• Syntax:

• Example:
  Sum sales in East region > ₹50,000:

2.2 COUNTIFS Function

• Count values meeting multiple criteria

• Syntax:

• Example:
  Count employees in “Sales” department with sales > 50000:

2.3 AVERAGEIFS Function

• Average values meeting multiple criteria

• Syntax:

• Example:
  Average sales in East region:

3. Tips for Logical & Statistical Functions

• Always check data types (number vs text)

• Use named ranges for clarity and dynamic formulas

• Combine IF + AND/OR for complex decision rules

• Avoid excessive nesting; use IFS for readability

• Test formulas with sample data before full application

4. Real-Life Examples

• HR: Determine employee performance ratings

• Sales: Identify high-performing regions or products

• Finance: Conditional bonuses based on sales and targets

• Operations: Count tasks completed within deadlines

5. Practice Questions

1. Write an IF formula to classify sales > 50,000 as “High” and <=50,000 as “Low”.

2. Create a nested IF to categorize sales as “Excellent” (>80,000), “Good” (50,001–80,000), “Average” (<=50,000).

3. Use AND with IF to mark employees eligible for bonus if sales >50,000 and region = East.

4. Write a SUMIFS formula to sum sales for West region above 60,000.

5. Use COUNTIFS to count employees in Marketing department with sales > 70,000.