Lesson 1.12 – Practical Examples & Exercises

(Complete Practice for Topic 1: Advanced Excel Formulas & Functions)

1. Real-Life Business Scenarios

Example 1 – Sales Commission Calculation with Error Handling

You have a sales data table:

Task:

• Calculate commission = Sales Amount × Commission %

• If Sales Amount is 0, display “No Sales” instead of #DIV/0! or 0.

Formula:

Example 2 – Product Lookup with IFNA

You have:

Task:

• Search for a product’s price. If not found, display “Not Available”.

Formula:

Example 3 – Conditional Sum with SUMIFS

You have:

Task:

• Find total sales of Apple in West.

Formula:

Example 4 – Text Processing with TRIM & PROPER

You have messy text: " rAjESh kumar "
Task:

• Remove extra spaces and capitalize each word.

Formula:

Example 5 – Dynamic Filter for Latest Data

You have:

Task:

• Show only sales after 01-08-2025.

Formula (Dynamic Array):

2. Practice Questions (Mixed)

1. Logical Test:
   If sales are above ₹20,000, show “Target Achieved”, else “Target Missed”.

2. Nested IF:
   Grade students based on marks:

   • ≥90 → A

   • ≥75 → B

   • ≥60 → C

   • Else → Fail

3. SUMIF:
   Total salary for employees in “IT” department only.

4. COUNTIFS:
   Count orders where product is “Laptop” and quantity > 5.

5. DATE Functions:
   Find number of working days between 01-01-2025 and 15-01-2025 excluding weekends.

6. Financial Function:
   EMI calculation for a ₹5,00,000 loan at 9% annual interest for 5 years.

7. VLOOKUP & IFERROR:
   Search employee ID in table; if not found, display “Invalid ID”.

8. TEXTJOIN:
   Combine first name and last name from two columns with a space.

9. FILTER & SORT:
   Show all products priced above ₹100, sorted in descending order.

10. ERROR.TYPE:
    Find error type code for a failing formula.

✅ With this lesson, your Topic 1: Advanced Excel Formulas & Functions is fully complete — covering theory, examples, and practical exercises for all functions.