Part II: Essential Excel Functions

Mathematical Functions

🎯 Learning Objectives

• Use SUM, AVERAGE, MIN, MAX for basic calculations
• Apply ROUND, ROUNDUP, ROUNDDOWN for precision control
• Understand ABS, MOD, and POWER for advanced math
• Master conditional functions: SUMIF, COUNTIF, AVERAGEIF and their multi-criteria variants
• Use INT, CEILING, FLOOR for rounding variations
• Solve 40+ real-world problems using mathematical functions

SUM & AVERAGE Functions

Theory — The SUM Function

The SUM function is the most frequently used function in Excel. It adds up all the numbers you specify. Think of it as a calculator that can add thousands of numbers in a single keystroke. Every accountant in India — from a small shop in Chandni Chowk to the finance department at Reliance Industries — uses SUM daily.

Example 1 — Sum a Simple Range (Monthly Sales)

A Flipkart seller wants to know total sales for Q1 (January–March):

Formula in B5: =SUM(B2:B4)

Explanation: The function takes the range B2:B4 (all three months) and adds them: 1,25,000 + 1,48,000 + 1,62,000 = 4,35,000.

Example 2 — Sum Non-Contiguous Cells (Expense Report)

An employee at TCS has expenses in cells that are not next to each other:

Formula: =SUM(B2, B4, B6) — to sum only Travel, Hotel, and Cab expenses.

Explanation: By passing individual cells separated by commas, you can pick specific items: 8,500 + 12,000 + 3,800 = 24,300.

Example 3 — 3D Reference SUM Across Sheets

A Reliance Jio store manager has monthly sales in separate sheets (Jan, Feb, Mar), with totals always in cell B10 of each sheet:

Formula: =SUM(Jan:Mar!B10)

Explanation: A 3D reference lets you sum the same cell across multiple worksheets. This is powerful for monthly/quarterly consolidation. If Jan!B10 = 5,00,000, Feb!B10 = 6,20,000, Mar!B10 = 5,80,000, the result would be ₹17,00,000.

Theory — The AVERAGE Function

The AVERAGE function calculates the arithmetic mean — it adds all numbers and divides by the count. In Indian schools, teachers use this constantly: average marks of a class, average attendance percentage, or average score across subjects.

Example 1 — Class Average (CBSE School)

A teacher at Delhi Public School wants to find the average Mathematics marks for 5 students:

Formula in B7: =AVERAGE(B2:B6)

Calculation: (87 + 92 + 76 + 95 + 83) ÷ 5 = 433 ÷ 5 = 86.6

Example 2 — Average Monthly Sales (Zomato Delivery Partner)

Formula: =AVERAGE(B2:B7)

Example 3 — Average with Non-Contiguous Cells

Average only the first and last exam scores: =AVERAGE(B2, B6)

MIN, MAX & ROUND Functions

The MIN Function

Example 1 — Lowest Marks in Class

Formula: =MIN(B2:B6)

Example 2 — Lowest Salary in Department

HR at Wipro wants to find the minimum CTC in the Engineering department:

Formula: =MIN(B2:B5)

Example 3 — MIN Across Multiple Ranges

=MIN(B2:B5, D2:D5) — finds the minimum value across two separate columns (e.g., comparing two departments' salaries).

The MAX Function

Example 1 — Highest Marks (Topper)

Using the same student data above: =MAX(B2:B6)

Example 2 — Peak Daily Sales

Formula: =MAX(B2:B7)

Example 3 — Difference Between Highest & Lowest (Range)

=MAX(B2:B7) - MIN(B2:B7)

The ROUND Function Family

Example 1 — Round GST Calculation

Product price: ₹1,249. GST @18%:

=ROUND(1249 * 0.18, 2)

Without ROUND: 224.82 exactly. But what if the result were 224.825? ROUND would make it 224.83 (rounds 5 up).

Example 2 — ROUNDUP for Ceiling Pricing

An Amazon seller wants to always round up to the nearest rupee:

=ROUNDUP(224.82, 0)

Example 3 — ROUNDDOWN for Conservative Estimates

=ROUNDDOWN(3.789, 2)

Round to Nearest 10, 100, 1000

ABS, MOD & POWER Functions

ABS — Absolute Value

Example 1 — Budget Variance

Budgeted: ₹5,00,000. Actual: ₹5,43,000. What's the absolute difference?

=ABS(500000 - 543000)

Example 2 — Temperature Difference

=ABS(-4 - 8)

Example 3 — Stock Price Movement

Opening: ₹2,340. Closing: ₹2,298. Absolute change: =ABS(2340-2298)

MOD — Remainder After Division

Example 1 — Check Odd or Even

Is roll number 247 odd or even?

=MOD(247, 2)

Combine with IF: =IF(MOD(A2,2)=0, "Even", "Odd")

Example 2 — Every 3rd Row Highlight Logic

To check if a row number is divisible by 3 (for conditional formatting):

=MOD(ROW(), 3) = 0

Example 3 — Remaining Items After Packaging

A factory has 1,247 chocolates. Each box holds 12. How many left over?

=MOD(1247, 12)

POWER — Exponentiation

Example 1 — Compound Interest

Principal ₹1,00,000 at 8% for 5 years compounded annually:

=100000 * POWER(1 + 0.08, 5)

Breakdown: 1,00,000 × (1.08)⁵ = 1,00,000 × 1.469328 = ₹1,46,932.81

Example 2 — Area of Circle

Radius = 7 cm: =3.14159 * POWER(7, 2)

Example 3 — Population Growth

India's population 140 crore, growing at 0.7% per year. After 10 years:

=140 * POWER(1.007, 10)

INT, CEILING & FLOOR

Practical Use — Pricing to Nearest ₹49/₹99

E-commerce sites price products at ₹X99. To round ₹1,247 to the nearest ₹99-ending:

=CEILING(1247, 100) - 1

SUMIF, COUNTIF & AVERAGEIF — Conditional Functions

These are the "smart" versions of SUM, COUNT, and AVERAGE. Instead of calculating all values, they only include values that meet a specific condition. This is enormously useful in real business scenarios.

SUMIF — Conditional Sum

Example 1 — Sum All Laptop Sales

=SUMIF(A2:A7, "Laptop", C2:C7)

Example 2 — Sum Sales from North Region

=SUMIF(B2:B7, "North", C2:C7)

Example 3 — Sum Sales Greater Than ₹2,00,000

=SUMIF(C2:C7, ">200000")

SUMIFS — Multiple Criteria

Example — Laptop Sales in North Region Only

=SUMIFS(C2:C7, A2:A7, "Laptop", B2:B7, "North")

COUNTIF — Count with Condition

Example 1 — Count Students Who Passed (≥33)

=COUNTIF(B2:B7, ">=33")

Example 2 — Count Specific Product

=COUNTIF(A2:A7, "Phone") (from sales data above)

Example 3 — Count Blank Cells

=COUNTIF(A2:A20, "")

COUNTIFS — Multiple Conditions

Example — Count Laptops Sold in North

=COUNTIFS(A2:A7, "Laptop", B2:B7, "North")

AVERAGEIF & AVERAGEIFS

Example 1 — Average Salary of Engineers

=AVERAGEIF(B2:B6, "Engineering", C2:C6)

Example 2 — Average Marks Above 60

=AVERAGEIF(B2:B7, ">60")

Example 3 — AVERAGEIFS with Multiple Criteria

=AVERAGEIFS(C2:C6, B2:B6, "Engineering", C2:C6, ">70000")

Practice Exercises, MCQs & Interview Questions

Practice Exercises

Exercise 1 — Monthly Household Budget

Create a spreadsheet with the following monthly expenses for a family in Pune: Rent ₹18,000, Groceries ₹8,500, Electricity ₹2,800, Internet ₹1,200, Transport ₹4,500, Education ₹6,000, Entertainment ₹3,000, Medical ₹2,000. Use SUM for total, AVERAGE for average expense, MAX for highest, MIN for lowest, and ROUND to round average to nearest ₹10.

Answer: Total = ₹46,000 | Average = ₹5,750 | Max = ₹18,000 (Rent) | Min = ₹1,200 (Internet) | Rounded = ₹5,750

Exercise 2 — GST Invoice Calculator

Create a product list: Item A ₹1,250, Item B ₹3,475, Item C ₹890, Item D ₹6,200, Item E ₹2,150. Calculate: (a) CGST @9% for each item using ROUND to 2 decimals, (b) SGST @9% (same), (c) Total amount with GST, (d) ROUNDUP the grand total to nearest ₹100.

Answer (Item A): CGST = ROUND(1250×0.09, 2) = ₹112.50 | SGST = ₹112.50 | Total = ₹1,475 | Grand total all items ROUNDUP = ₹16,500

Exercise 3 — Odd/Even Classification

Create a list of 20 roll numbers (101–120). Use MOD and IF to classify each as "Odd" or "Even".

Formula: =IF(MOD(A2,2)=0, "Even", "Odd")

Exercise 4 — Fixed Deposit Calculator

Use POWER to calculate maturity amount for: Principal ₹2,00,000, Rate 7.5%, Period 3 years (compounded quarterly).

Formula: =200000 * POWER(1 + 0.075/4, 4*3)

Answer: ₹2,49,696.89

Exercise 5 — Conditional Sales Analysis

Using the product sales dataset from Section 4.4, calculate: (a) Total Phone sales, (b) Number of South region transactions, (c) Average sales for North region, (d) Count of sales above ₹2,00,000.

Answers: (a) SUMIF: ₹4,00,000 (b) COUNTIF: 2 (c) AVERAGEIF: ₹1,88,333 (d) COUNTIF: 4

Exercise 6 — Employee Salary Summary

Create a dataset with 15 employees (Name, Department [IT/Sales/HR], City [Delhi/Mumbai/Bangalore], Salary). Use SUMIFS to find total IT salary in Delhi. Use COUNTIFS to count Sales employees in Mumbai. Use AVERAGEIFS for average HR salary in Bangalore.

Exercise 7 — Temperature Data Analysis

Record temperatures for 7 cities: Delhi 45°C, Mumbai 33°C, Shimla 18°C, Jaipur 44°C, Srinagar 8°C, Chennai 38°C, Bangalore 28°C. Find: SUM, AVERAGE, MIN, MAX, difference between hottest and coldest, ABS difference between Delhi and Shimla.

Answers: SUM=214 | AVG=30.57 | MIN=8 | MAX=45 | Range=37 | ABS(45-18)=27

Exercise 8 — CEILING & FLOOR Pricing

Given prices: ₹127, ₹243, ₹589, ₹1,067, ₹3,842. Round each to nearest ₹50 using CEILING and FLOOR. Show the difference.

Exercise 9 — Cricket Score Analysis

Create a dataset of 10 IPL batsmen with Name, Team, Runs, Matches. Use SUMIF for total runs by team, COUNTIF for players with 400+ runs, AVERAGEIF for average runs of a specific team.

Exercise 10 — Attendance Tracker

30 students, 20 working days. Use COUNTIF to count "P" (present) and "A" (absent) for each student. Use AVERAGEIF for average attendance of students with more than 15 days present.

Exercise 11 — Inventory Management

Create an inventory: 10 products with Category [Electronics/Clothing/Food], Quantity, Price. Use SUMIF for total value of Electronics, COUNTIF for items with quantity below 10, SUMIFS for Electronics with price above ₹1,000.

Exercise 12 — EMI Calculator

Loan ₹10,00,000, Rate 9.5%, Tenure 20 years. Use POWER to calculate: (a) Monthly interest factor (1+r)^n, (b) Total interest paid, (c) Use INT to show whole-number EMI.

Exercise 13 — CBSE Board Results

50 students with marks in 5 subjects. Calculate: total marks (SUM), percentage (AVERAGE), highest in each subject (MAX), students scoring above 90% (COUNTIF), average marks of students above 80% (AVERAGEIF).

Exercise 14 — Zomato Order Analysis

20 orders: Customer, Restaurant, Area [Koramangala/Indiranagar/HSR], Amount, Rating. Use SUMIF for total orders from Koramangala, AVERAGEIF for average rating above 4, COUNTIFS for Koramangala orders above ₹500.

Exercise 15 — SIP Calculator

Monthly SIP ₹5,000, Expected return 12% p.a. (1% monthly), Period 15 years. Use POWER and formula: FV = P × [(1+r)^n - 1]/r × (1+r). Calculate the future value.

MCQ Quiz — Chapter 4

Interview Questions

Mini Project & Chapter Summary

Logical Functions

🎯 Learning Objectives

• Write IF statements for single-condition decisions
• Build nested IF formulas for multi-level grading and tax slabs
• Use IFS as a cleaner alternative to nested IF
• Combine conditions with AND, OR, NOT
• Handle errors gracefully with IFERROR
• Use SWITCH for value-based matching
• Apply logical functions to real Indian scenarios: tax, grading, loan eligibility

IF Function & Nested IF

Theory — The IF Function

The IF function is the most important logical function in Excel. It tests a condition and returns one value if TRUE, another if FALSE. Think of it as a simple question: "If this condition is met, do this; otherwise, do that." It's like a traffic signal — green means go, red means stop.

Example 1 — Pass / Fail (CBSE)

In CBSE, minimum passing marks = 33 out of 100:

Results: Arjun → Pass, Meena → Fail, Vishal → Pass (33 is exactly the pass mark)

Example 2 — Bonus Eligibility

Employees at Infosys with rating ≥ 4 (out of 5) get a bonus:

=IF(C2>=4, "Eligible", "Not Eligible")

Example 3 — Discount Based on Purchase Amount

A BigBazaar store offers 10% discount on purchases above ₹2,000:

=IF(B2>2000, B2*0.10, 0)

Theory — Nested IF (Multiple Conditions)

When you have more than two outcomes, you nest IF functions inside each other. The value_if_false of one IF becomes another IF. Think of it as a decision tree: each branch leads to either a result or another question.

Example 1 — CBSE Grading System

CBSE uses this grading scale: 91–100 = A1, 81–90 = A2, 71–80 = B1, 61–70 = B2, 51–60 = C1, 41–50 = C2, 33–40 = D, Below 33 = E (Fail)

Results: Priya → A1 | Raj → B1 | Sunita → C2 | Deepak → E

Example 2 — Indian Income Tax Slabs (New Regime 2024-25)

For the new tax regime:

Simplified formula for tax slab identification (not total tax):

=IF(B2<=300000, "Nil",
  IF(B2<=600000, "5%",
    IF(B2<=900000, "10%",
      IF(B2<=1200000, "15%",
        IF(B2<=1500000, "20%", "30%")))))

For income ₹8,50,000: the formula checks each slab and returns "10%".

Example 3 — Discount Tiers (E-Commerce)

A Myntra-style discount structure:

=IF(B2>5000, B2*0.20, IF(B2>=3000, B2*0.15, IF(B2>=1000, B2*0.10, B2*0.05)))

Cart value ₹4,200: → ₹4,200 × 0.15 = ₹630 discount

IFS Function — Cleaner Alternative

Example — CBSE Grading with IFS (much cleaner!)

=IFS(B2>=91, "A1",
     B2>=81, "A2",
     B2>=71, "B1",
     B2>=61, "B2",
     B2>=51, "C1",
     B2>=41, "C2",
     B2>=33, "D",
     TRUE,   "E")

This is functionally identical to the nested IF but far easier to read and maintain.

AND, OR, NOT — Combining Conditions

AND Function

Example 1 — Eligibility for Merit Scholarship

Student needs BOTH: marks ≥ 90 AND attendance ≥ 85%:

=AND(B2>=90, C2>=85)

Example 2 — Loan Approval (Bank)

SBI loan requires: Age 21-60, Income ≥ ₹25,000/month, Credit Score ≥ 700:

=AND(B2>=21, B2<=60, C2>=25000, D2>=700)

Example 3 — IF + AND Combination

=IF(AND(B2>=90, C2>=85), "Scholarship", "Not Eligible")

OR Function

Example 1 — Weekend Check

=OR(A2="Saturday", A2="Sunday")

Example 2 — Discount Eligibility

Customer gets discount if EITHER: member = "Yes" OR purchase > ₹5,000:

=IF(OR(B2="Yes", C2>5000), "10% Discount", "No Discount")

Example 3 — Emergency Leave Approval

Auto-approve if reason is "Medical" OR "Family Emergency":

=IF(OR(C2="Medical", C2="Family Emergency"), "Auto Approved", "Needs Manager Approval")

NOT Function

Example 1 — Not in Specific Department

=NOT(B2="HR") — returns TRUE for everyone NOT in HR.

Example 2 — Combine with IF

=IF(NOT(C2="Completed"), "Pending", "Done")

Example 3 — Complex Combination

Approve if: (Rating ≥ 4 OR Experience ≥ 5 years) AND NOT from Intern category:

=IF(AND(OR(C2>=4, D2>=5), NOT(E2="Intern")), "Approved", "Rejected")

Real-World Application — Employee Bonus Criteria (Infosys-style)

Results: Priya → 10% Bonus (rating ≥4, attendance ≥85, but years < 5) | Ravi → 5% Bonus (years ≥8? No, but rating ≥3) | Anita → 20% Bonus (all conditions + 5 years) | Kunal → No Bonus (rating 2.5 < 3 and years 2 < 8)

IFERROR & SWITCH Functions

IFERROR — Error Handling

Example 1 — Division by Zero Protection

Calculating per-unit cost when quantity might be 0:

=IFERROR(B2/C2, "N/A")

Example 2 — VLOOKUP Error Handling

When looking up a product that might not exist:

=IFERROR(VLOOKUP(A2, Products!A:B, 2, FALSE), "Product Not Found")

Example 3 — Return 0 Instead of Error

=IFERROR(B2/C2, 0) — returns 0 instead of error. Useful when the result feeds into further SUM calculations (errors would propagate otherwise).

SWITCH Function

Example 1 — Department Code Lookup

=SWITCH(B2, "IT", "Information Technology", "HR", "Human Resources", "FIN", "Finance", "MKT", "Marketing", "Unknown")

Example 2 — Day Number to Name

=SWITCH(WEEKDAY(A2), 1,"Sunday", 2,"Monday", 3,"Tuesday", 4,"Wednesday", 5,"Thursday", 6,"Friday", 7,"Saturday")

Example 3 — Grade to Grade Point

Convert CBSE grades to grade points:

=SWITCH(C2, "A1",10, "A2",9, "B1",8, "B2",7, "C1",6, "C2",5, "D",4, "E",0)

Practice Exercises, MCQs & Interview Questions

Practice Exercises

Exercise 1 — Pass/Fail System

Create a marks sheet for 15 students with 5 subjects. A student passes if they score ≥33 in EVERY subject. Use IF and AND to determine "Pass" or "Fail" for each student.

Formula: =IF(AND(C2>=33,D2>=33,E2>=33,F2>=33,G2>=33),"Pass","Fail")

Exercise 2 — CBSE Grading

For each subject, assign CBSE grades (A1 through E) using nested IF or IFS. Then convert grades to grade points using SWITCH.

Exercise 3 — Income Tax Calculator

Create a tax calculator using the new regime slabs. Input: Annual Income. Output: Tax slab, Tax rate, Tax amount, Education cess (4%), Total tax payable. Use nested IF for slab identification.

Hint for total tax calculation: You need to calculate tax on each slab separately, not just apply the rate to the full income. For ₹10,00,000: 0 + 15,000 + 30,000 + 15,000 = ₹60,000.

Exercise 4 — Loan Eligibility Checker

Create a loan eligibility tool. Criteria: Age 21-58, Income ≥ ₹30,000, CIBIL Score ≥ 650, Existing EMIs < 40% of income. Use IF with AND/OR. Show: Eligible/Not Eligible and the reason for rejection.

Exercise 5 — Electricity Bill Calculator

Indian domestic electricity slabs: 0-100 units @ ₹2.50, 101-200 @ ₹4.00, 201-300 @ ₹5.50, 301-500 @ ₹7.00, 500+ @ ₹8.50. Use nested IF to calculate the bill amount for any given units consumed.

Exercise 6 — Student Report Card

For 20 students: display grade, result (Pass/Fail), eligible for distinction (percentage ≥ 75%), scholarship (marks ≥90 AND attendance ≥85). Handle division errors with IFERROR.

Exercise 7 — Employee Performance Review

Categories: Outstanding (rating 5), Excellent (4-4.9), Good (3-3.9), Needs Improvement (2-2.9), PIP (below 2). Bonus: Outstanding 30%, Excellent 20%, Good 10%, Needs Improvement 0%, PIP "Warning". Use IFS.

Exercise 8 — Delivery Status Tracker

Map status codes to descriptions using SWITCH: D→Delivered, T→In Transit, P→Processing, R→Returned, C→Cancelled. Combine with IF to flag any order that has been "In Transit" for more than 5 days.

Exercise 9 — Multi-Criteria Discount

Customer gets discount if: (Member AND purchase ≥ ₹2,000) OR (Non-member AND purchase ≥ ₹5,000) OR (Festival sale period). Member discount = 15%, Non-member = 10%, Festival = 20%. Use IF with OR and AND.

Exercise 10 — Age-Based Insurance Premium

Calculate premium based on age and smoker status: Age 18-30 → ₹5,000 (non-smoker) / ₹8,000 (smoker). Age 31-45 → ₹8,000 / ₹12,000. Age 46-60 → ₹12,000 / ₹18,000. Age 60+ → ₹20,000 / ₹30,000. Use IF with AND.

Exercise 11 — GST Rate Finder

Use SWITCH to assign GST rates by product category: Food Grains→0%, Medicine→5%, Electronics→18%, Luxury→28%, Gold→3%, Services→18%. Then calculate GST amount and total price.

Exercise 12 — School Admission Eligibility

Criteria: Age ≥ 3 (for nursery), marks ≥ 60% (for Class 1+), distance from school ≤ 8km, OR sibling already enrolled. Use AND/OR to determine "Eligible"/"Not Eligible".

MCQ Quiz — Chapter 5

Interview Questions

=IF(B2<=300000, 0,
  IF(B2<=600000, (B2-300000)*0.05,
    IF(B2<=900000, 15000 + (B2-600000)*0.10,
      IF(B2<=1200000, 45000 + (B2-900000)*0.15,
        IF(B2<=1500000, 90000 + (B2-1200000)*0.20,
          150000 + (B2-1500000)*0.30)))))

Mini Project & Chapter Summary

Text Functions

🎯 Learning Objectives

• Extract substrings with LEFT, RIGHT, MID
• Join text with CONCATENATE, CONCAT, TEXTJOIN, and the & operator
• Change case with UPPER, LOWER, PROPER
• Clean data with TRIM, SUBSTITUTE, FIND, SEARCH
• Format values with TEXT and convert text to numbers with VALUE
• Use REPT for text-based charts and EXACT for case-sensitive comparison

LEFT, RIGHT, MID & LEN — Extracting Text

LEFT — Extract from the Beginning

Example 1 — Extract State Code from PAN

PAN format: ABCDE1234F. The first 5 characters are alphabets:

=LEFT("BWFPS1234K", 5)

Example 2 — Extract First Name

If names are formatted as "Priya Sharma" and you want just the first name:

=LEFT(A2, FIND(" ", A2)-1)

Example 3 — Extract STD Code from Phone

Phone "011-26854321": =LEFT(A2, 3)

RIGHT — Extract from the End

Example 1 — Last 4 Digits of Aadhaar (for Masking)

=RIGHT("7423 8156 9012", 4)

Example 2 — Extract File Extension

=RIGHT("report_2024.xlsx", 4)

Example 3 — Last Name Extraction

From "Rahul Dravid": =RIGHT(A2, LEN(A2)-FIND(" ",A2))

MID — Extract from the Middle

Example 1 — Extract Birth Year from Aadhaar Enrolment ID

Enrolment ID format: 1234/56789/01234. Extract the middle segment:

=MID("1234/56789/01234", 6, 5)

Example 2 — Extract Invoice Number

Invoice "INV-2024-00567": extract the year:

=MID("INV-2024-00567", 5, 4)

Example 3 — Mask Aadhaar Number (Show Only Last 4)

Display "XXXX XXXX 9012" for Aadhaar "7423 8156 9012":

="XXXX XXXX " & RIGHT(A2, 4)

LEN — Count Characters

Example 1 — Validate Mobile Number Length

=IF(LEN(A2)=10, "Valid", "Invalid")

Example 2 — PAN Validation

PAN must be exactly 10 characters: =IF(LEN(A2)=10, "Valid PAN", "Check PAN")

Example 3 — Character Count for SMS

=LEN("Your OTP is 456789. Valid for 10 minutes.")

Combine: =IF(LEN(A2)>160, "Over SMS limit!", LEN(A2)&" chars")

CONCATENATE, CONCAT, TEXTJOIN & the & Operator

CONCATENATE / CONCAT — Joining Text

Example 1 — Full Name from First + Last

Example 2 — Create Email Address

=LOWER(LEFT(A2,1) & B2 & "@company.com")

Example 3 — Invoice ID Generator

="INV-" & TEXT(TODAY(), "YYYY") & "-" & TEXT(ROW()-1, "00000")

TEXTJOIN — Join with Delimiter

Example 1 — Create Full Address

=TEXTJOIN(", ", TRUE, A2, B2, C2, D2)

Example 2 — Join with Dash (Skipping Blanks)

If C2 is blank: =TEXTJOIN("-", TRUE, "A", "", "C")

Example 3 — Join a Range

=TEXTJOIN("; ", TRUE, A2:A10) — joins all names from A2 to A10 with semicolons.

SUBSTITUTE — Replace Text

Example 1 — Remove Dashes from Phone Numbers

=SUBSTITUTE("91-98765-43210", "-", "")

Example 2 — Replace Department Name

=SUBSTITUTE("IT Department", "IT", "Information Technology")

Example 3 — Replace Only Second Occurrence

=SUBSTITUTE("a-b-c-d", "-", "|", 2)

UPPER, LOWER, PROPER, TRIM, FIND, SEARCH, TEXT, VALUE, REPT & EXACT

Case Conversion Functions

Example — Clean Imported Employee Names

If names were entered inconsistently: =PROPER(A2) standardizes them all.

TRIM — Remove Extra Spaces

Example 1 — Clean Messy Data

=TRIM(" Rajesh Kumar ")

Example 2 — Combine with PROPER

=PROPER(TRIM(" aMiT jOsHi "))

Example 3 — Remove Non-Breaking Spaces

=TRIM(SUBSTITUTE(A2, CHAR(160), " "))

FIND & SEARCH — Locate Text Position

Example 1 — Find Space Position (for Name Splitting)

=FIND(" ", "Sachin Tendulkar")

Example 2 — Case-Insensitive Search

=SEARCH("excel", "Microsoft Excel Training")

Example 3 — Check if Text Contains a Word

=IF(ISNUMBER(SEARCH("Delhi", A2)), "Delhi Branch", "Other")

TEXT — Format Values as Text

Common Format Codes

Example — Indian Currency Format

="₹" & TEXT(1543267, "##,##,##0.00")

VALUE — Convert Text to Number

Example — Convert Text-Formatted Numbers

Cell A2 contains "12345" as text (left-aligned, green triangle): =VALUE(A2)

REPT — Repeat Text

Example — In-Cell Bar Chart

=REPT("█", B2/10) — if B2=75, displays 7.5 → 7 blocks: ███████

Example — Star Rating Display

=REPT("★", C2) & REPT("☆", 5-C2)

EXACT — Case-Sensitive Comparison

Example — Password Validation

=EXACT(B2, "SecurePass@123")

Practice Exercises, MCQs & Interview Questions

Practice Exercises

Exercise 1 — Name Splitting

Given a list of 15 full names (e.g., "Virat Kohli", "MS Dhoni", "Rohit Sharma"), extract First Name and Last Name into separate columns.

First Name: =LEFT(A2, FIND(" ",A2)-1)

Last Name: =RIGHT(A2, LEN(A2)-FIND(" ",A2))

Exercise 2 — Email Generator

From employee data (First Name, Last Name, Company Domain), generate email addresses in the format: first.last@domain.com (all lowercase).

=LOWER(B2 & "." & C2 & "@" & D2)

Exercise 3 — Phone Number Formatting

Given 10-digit numbers like 9876543210, format as: +91-98765-43210

="+91-" & LEFT(A2,5) & "-" & RIGHT(A2,5)

Exercise 4 — PAN Card Extraction

From a text like "PAN: ABCDE1234F, DOB: 15/08/1990", extract: (a) PAN number, (b) 4th character of PAN (entity type), (c) Validate PAN length.

Exercise 5 — Aadhaar Masking

Given Aadhaar numbers, display masked format: XXXX XXXX 1234. Also validate that the original has exactly 12 digits (excluding spaces).

="XXXX XXXX " & RIGHT(SUBSTITUTE(A2," ",""),4)

Exercise 6 — Data Cleaning Challenge

Given messy data: " rAjEsH kUMAR " → clean to "Rajesh Kumar". Combine PROPER, TRIM, and SUBSTITUTE.

Exercise 7 — Address Standardization

Standardize addresses: replace "Rd" with "Road", "St" with "Street", "Blvd" with "Boulevard". Remove extra spaces.

Exercise 8 — Indian Currency Formatting

Convert numbers to Indian format with ₹ symbol: 1234567 → "₹12,34,567". Use TEXT function.

Exercise 9 — In-Cell Rating System

Create a star rating system: for ratings 1-5, display filled and empty stars using REPT.

Exercise 10 — GSTIN Breakdown

GSTIN format: 27AABCU9603R1ZM (15 chars). Extract: State Code (first 2), PAN (chars 3-12), Entity Number (char 13), Z (char 14), Checksum (char 15).

State Code: =LEFT(A2,2) | PAN: =MID(A2,3,10)

Exercise 11 — Word Count

Count words in a sentence: =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1

Exercise 12 — CSV Builder

Use TEXTJOIN to create CSV-formatted rows from data columns. Join 5 columns with commas, handling blank cells.

MCQ Quiz — Chapter 6

Interview Questions

Mini Project & Chapter Summary

Date & Time Functions

🎯 Learning Objectives

• Understand Excel's date serial number system
• Use TODAY() and NOW() for dynamic dates
• Construct dates with DATE() and extract parts with DAY, MONTH, YEAR
• Calculate differences with DATEDIF and DAYS
• Navigate months with EDATE and EOMONTH
• Calculate working days with NETWORKDAYS and WORKDAY
• Apply date functions to Indian scenarios: age calculation, tenure, retirement, EMI dates

Understanding Excel's Date System

Excel stores every date as a serial number. January 1, 1900 = 1. January 2, 1900 = 2. Today (June 22, 2026) is serial number 46,189. This is why you can add and subtract dates — they're just numbers underneath.

Times are stored as decimal fractions of a day: 12:00 PM = 0.5, 6:00 AM = 0.25, 6:00 PM = 0.75.

TODAY, NOW & DATE Functions

TODAY() — Current Date

Example 1 — Days Until CBSE Board Exam

If board exams start on 15-Feb-2027:

=DATE(2027,2,15) - TODAY()

Example 2 — Employee's Age in Years

DOB: 15-Mar-1990: =INT((TODAY()-DATE(1990,3,15))/365.25)

Example 3 — Is It Due Today?

=IF(A2=TODAY(), "DUE TODAY!", IF(A2<TODAY(), "OVERDUE", "Upcoming"))

NOW() — Current Date & Time

Example 1 — Timestamp a Log Entry

=TEXT(NOW(), "DD-MMM-YYYY HH:MM:SS AM/PM")

Example 2 — Hours Since Last Update

=(NOW()-A2)*24 — where A2 contains the last update datetime

Example 3 — Current Time Only

=NOW()-TODAY() — returns only the time portion (as a decimal)

DATE() — Construct a Date

Example 1 — Construct Date from Separate Cells

A2=2026, B2=8, C2=15: =DATE(A2, B2, C2)

Example 2 — First Day of Current Month

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

Example 3 — Last Day of Previous Month

=DATE(YEAR(TODAY()), MONTH(TODAY()), 0)

DAY, MONTH, YEAR — Extract Components

Practical Use — Group Sales by Month

=MONTH(A2) extracts the month number, which you can use with SUMIF to sum sales by month.

HOUR, MINUTE, SECOND — Time Components

Example — Extract Shift from Login Time

=IF(HOUR(A2)<12, "Morning Shift", IF(HOUR(A2)<20, "Day Shift", "Night Shift"))

WEEKDAY — Day of the Week

Example 1 — Is It a Weekend?

=IF(OR(WEEKDAY(A2)=1, WEEKDAY(A2)=7), "Weekend", "Weekday")

Example 2 — Day Name

=TEXT(A2, "DDDD")

Example 3 — Next Monday

=A2 + (9 - WEEKDAY(A2, 2)) — calculates the next Monday from any date.

DATEDIF, DAYS, EDATE & EOMONTH

DATEDIF — Date Difference (The Hidden Gem)

Example 1 — Calculate Exact Age

DOB: 10-Mar-1990. As of today (22-Jun-2026):

=DATEDIF("10-Mar-1990", TODAY(), "Y") → 36 years

=DATEDIF("10-Mar-1990", TODAY(), "YM") → 3 months

=DATEDIF("10-Mar-1990", TODAY(), "MD") → 12 days

Complete age string:

=DATEDIF(A2,TODAY(),"Y") & " years, " & DATEDIF(A2,TODAY(),"YM") & " months, " & DATEDIF(A2,TODAY(),"MD") & " days"

Example 2 — Employee Tenure for Gratuity

Gratuity is payable after 5 years of continuous service in India:

Ramesh: 8 years → Yes | Sunita: 3 years → No

Example 3 — Aadhaar Age Verification

Minimum age for Aadhaar enrollment is 0 (even newborns can get it), but for certain services, age must be ≥ 18:

=IF(DATEDIF(A2, TODAY(), "Y")>=18, "Adult - Full Services", "Minor - Limited Services")

DAYS — Simple Day Difference

Example 1 — Project Duration

=DAYS("31-Dec-2026", "01-Apr-2026")

Example 2 — Days Since Last Order

=DAYS(TODAY(), B2) where B2 = last order date

Example 3 — Simple Subtraction Alternative

=DATE(2027,3,31) - DATE(2026,4,1)

EDATE — Move by Months

Example 1 — EMI Due Date (6 Months from Disbursement)

Loan disbursed: 15-Jan-2026. First EMI after 6 months:

=EDATE("15-Jan-2026", 6)

Example 2 — Passport Renewal Date

Passport issued: 20-May-2016. Valid for 10 years (120 months):

=EDATE("20-May-2016", 120)

Example 3 — Go Back 3 Months

=EDATE(TODAY(), -3)

EOMONTH — End of Month

Example 1 — Salary Credit Date (Last Working Day)

=EOMONTH(TODAY(), 0)

Example 2 — Invoice Due Date (End of Next Month)

=EOMONTH(A2, 1) — if A2 is invoice date, payment is due by end of next month.

Example 3 — Last Day of Previous Month

=EOMONTH(TODAY(), -1)

NETWORKDAYS & WORKDAY — Working Day Calculations

NETWORKDAYS — Count Working Days

Example 1 — Working Days in a Quarter

=NETWORKDAYS("01-Apr-2026", "30-Jun-2026")

Example 2 — With Indian Holidays

Create a holidays list: 26-Jan (Republic Day), 15-Aug (Independence Day), 02-Oct (Gandhi Jayanti), Diwali, Holi, etc.

=NETWORKDAYS("01-Jan-2026", "31-Dec-2026", HolidayList)

Example 3 — Project Timeline

Project start: 01-Jul-2026. End: 30-Sep-2026. How many actual working days?

=NETWORKDAYS("01-Jul-2026", "30-Sep-2026", Holidays!A2:A10)

WORKDAY — Find a Future/Past Working Day

Example 1 — Project Deadline (45 Working Days)

Project starts: 01-Jul-2026. Deadline in 45 working days:

=WORKDAY("01-Jul-2026", 45)

Example 2 — SLA Deadline (5 Business Days)

Customer complaint received: 15-Jun-2026. Must resolve in 5 business days:

=WORKDAY("15-Jun-2026", 5)

Example 3 — Go Back 10 Working Days

=WORKDAY(TODAY(), -10)

Date Arithmetic — Practical Scenarios

Calculate Retirement Date

In India, government retirement age is 60. If DOB is in A2:

=DATE(YEAR(A2)+60, MONTH(A2), DAY(A2))

Calculate Next Birthday

=IF(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))>=TODAY(), DATE(YEAR(TODAY()),MONTH(A2),DAY(A2)), DATE(YEAR(TODAY())+1,MONTH(A2),DAY(A2)))

This checks if this year's birthday has passed. If not, shows this year's date; otherwise, next year's.

Days Until Next Birthday

Wrap the above in: =NextBirthday - TODAY()

EMI Payment Schedule

For a 12-month EMI starting from disbursement date:

=EDATE(DisbursementDate, ROW()-1) — generates the 1st, 2nd, 3rd... EMI dates when dragged down.

Practice Exercises, MCQs & Interview Questions

Practice Exercises

Exercise 1 — Age Calculator

Create an age calculator: input DOB, output age in "X years, Y months, Z days" format using DATEDIF. Test with: (a) 15-Aug-1990, (b) 01-Jan-2000, (c) 25-Dec-1975.

Exercise 2 — CBSE Exam Countdown

Create a countdown dashboard showing days remaining until 5 exam dates. Use conditional formatting: Red if < 30 days, Yellow if 30-60, Green if > 60.

Exercise 3 — Employee Tenure Report

For 15 employees with joining dates, calculate: years of service, eligible for gratuity (5+ years), eligible for long service award (10+ years), retirement date (age 60).

Exercise 4 — Loan EMI Schedule

Loan disbursement: 01-Apr-2026, Tenure: 24 months. Generate all 24 EMI dates using EDATE. Mark which fall on weekends using WEEKDAY and show the adjusted date using WORKDAY.

Exercise 5 — Holiday Planner

List all Indian national holidays for 2026. Calculate total working days per month using NETWORKDAYS. Create a summary table: Month, Total Days, Working Days, Holidays, Weekends.

Exercise 6 — Project Timeline

Project has 5 phases, each requiring 15, 20, 10, 25, and 30 working days respectively. Start: 01-Jul-2026. Calculate start and end date of each phase using WORKDAY. Include Indian holidays.

Exercise 7 — Birthday Tracker

For 20 friends/family: Name, DOB. Calculate: Current age, Next birthday date, Days until next birthday, Day of the week their next birthday falls on. Sort by upcoming birthday.

Exercise 8 — Month-End Reporting

Using EOMONTH, generate a list of all month-end dates for 2026. Then calculate NETWORKDAYS between consecutive month-ends.

Exercise 9 — Date Extraction Challenge

Given dates, extract: Day, Month number, Month name (using TEXT), Year, Quarter (using CEILING(MONTH/3,1)), Day name. Create a date dimension table.

Exercise 10 — SLA Tracking

10 customer complaints with: Received Date, Priority (High=2 days, Medium=5 days, Low=10 days). Calculate: SLA Deadline (using WORKDAY), Current Status (Open/Closed), Days Overdue (if any).

Exercise 11 — Passport/License Renewal Tracker

Track 10 documents: Name, Document Type, Issue Date, Validity (years). Calculate: Expiry Date (EDATE), Days Until Expiry, Status (Valid/Expiring Soon/Expired), Renewal Date (30 days before expiry).

Exercise 12 — Fiscal Year Calculator

Indian fiscal year: April to March. Given any date, determine: (a) Which FY it belongs to (e.g., "FY 2026-27"), (b) Quarter (Q1=Apr-Jun, Q2=Jul-Sep, Q3=Oct-Dec, Q4=Jan-Mar), (c) Days remaining in current FY.

Hint for FY: =IF(MONTH(A2)>=4, "FY "&YEAR(A2)&"-"&RIGHT(YEAR(A2)+1,2), "FY "&YEAR(A2)-1&"-"&RIGHT(YEAR(A2),2))

MCQ Quiz — Chapter 7

Interview Questions

Mini Project & Chapter Summary

Coming Up Next: Part III — Lookup & Reference Functions

You now command 40+ essential Excel functions across math, logic, text, and dates. But real-world data often sits in different tables — a product list here, prices there, customer details elsewhere. In Part III, you'll master the lookup superpowers: VLOOKUP (the classic), HLOOKUP, INDEX-MATCH (the professional choice), and the revolutionary XLOOKUP. You'll build an automated invoice generator, a student report card that pulls data from multiple sheets, and a dynamic sales dashboard — all powered by lookup functions. These are the functions that separate a casual Excel user from a true professional.