Learning Objectives

  • Build nested IF status rules and boolean helpers (AND, OR)
  • Look up values with VLOOKUP and INDEX/MATCH
  • Aggregate with SUM, AVERAGE, COUNT, COUNTIF, SUMIF, AVERAGEIF
  • Use math helpers (ROUND, ABS) and syllabus trig examples (SIN, COS)
  • Calculate EMI with PMT and tenure with DATEDIF

Sample file: employee_payroll.csv

1) Logical Functions

Pass/fail with AND

=IF(AND(C2>=50,D2>=50),"Pass","Fail")

Nested IF for revenue bands (matches Model Lab Test style)

=IF(D2>25000,"High",IF(D2>10000,"Medium","Low"))

Tips:

  • Test boundary values (exactly 10000 / 25000).
  • Prefer helper columns over giant nested formulas when learning.

2) Lookup Functions

Assume table A2:D7 with Product in column 1 and Revenue in column 4.

Exact VLOOKUP

=VLOOKUP(B15,A2:D7,4,FALSE)

FALSE / 0 = exact match (always use this for names/IDs).

INDEX + MATCH (flexible; column order can change)

=INDEX(G2:G9,MATCH(B22,A2:A9,0))

Verify both methods return the same Net/Tax in practice tasks.

3) Statistical and Conditional Aggregates

Need Formula
Total units =SUM(B2:B7)
Average revenue =AVERAGE(D2:D7)
Count High status =COUNTIF(G2:G7,"High")
Sum revenue where High =SUMIF(G2:G7,"High",D2:D7)
Average net for IT =AVERAGEIF(B2:B9,"IT",G2:G9)

4) Math and Trigonometric Functions

Everyday math

=ROUND(A2,2)
=ABS(A2)
=POWER(A2,2)
=SQRT(A2)

Trig (syllabus familiarity) — Excel expects radians:

=SIN(RADIANS(30))   → 0.5
=COS(RADIANS(60))   → 0.5

Business use is limited; know the pattern for lab quizzes, then focus on IF/lookup/aggregates for real work.

5) Financial Functions

Monthly EMI for a loan of NPR 500,000 at 10% annual for 60 months:

=PMT(10%/12, 60, -500000)
  • Rate per period = annual rate ÷ 12
  • nper = number of months
  • Negative pv makes the result display as a positive payment

Also useful: FV, PV, RATE (same arguments family).

6) Date and Time Functions

=TODAY()
=NOW()
=DATE(2026,8,26)
=DATEDIF(B2,TODAY(),"Y")   // years of service

DATEDIF is available in Excel even though it may not appear in the formula autocomplete list.

Mini-Dashboard Practice

Using employee_payroll.csv:

  1. Compute Gross, Tax (absolute rate), Net, Status (nested IF)
  2. COUNTIF / SUMIF by Status
  3. Name lookup with both VLOOKUP and INDEX/MATCH
  4. EMI block for a sample loan
  5. Years of service from Join Date

Step-by-Step Lab Exercise: Student Result Sheet

  1. Enter this data in A1:C6:

    Name Theory Practical
    Sita 72 80
    Ram 45 60
    Hari 88 91
    Gita 50 49
    Bikash 63 55
  2. D2 Total: =B2+C2
  3. E2 Result: =IF(AND(B2>=50,C2>=50),"Pass","Fail")
  4. F2 Division: =IF(D2>=160,"Distinction",IF(D2>=120,"First","Second"))
  5. Fill D2:F2 down to row 6.
  6. Summary cells: =COUNTIF(E2:E6,"Pass"), =AVERAGE(B2:B6), =MAX(D2:D6).
  7. Lookup: in H2 type Hari; in I2: =VLOOKUP(H2,A2:F6,4,FALSE).

Expected results:

Name Total Result Division
Sita 152 Pass First
Ram 105 Fail Second
Hari 179 Pass Distinction
Gita 99 Fail Second
Bikash 118 Pass Second
  • Number passed: 3
  • Average theory: 63.6
  • Highest total: 179
  • VLOOKUP for Hari returns 179

EMI check: =PMT(10%/12,60,-500000) should return about Rs. 10,623.52 per month.

Common Errors and Fixes

Problem Likely cause Fix
#N/A from VLOOKUP Lookup value not found, or extra spaces in names Check spelling; use TRIM; confirm the first column of the range holds the lookup value
VLOOKUP returns the wrong row Last argument omitted or TRUE (approximate match) Use FALSE for exact match
#NAME? error Function name misspelled or text without quotes Check spelling; put text such as “Pass” in double quotes
PMT gives a negative or huge value Annual rate not divided by 12, or sign of loan amount Use rate/12, months for nper, and a negative pv
Boundary marks graded wrongly > used where >= was needed Test values exactly at each boundary

Key Takeaways

  • Combine IF with AND/OR for rules with several conditions.
  • Use exact-match lookups (FALSE or 0) for names and IDs.
  • COUNTIF, SUMIF, and AVERAGEIF summarise data by condition.
  • Check every formula with a few hand-calculated answers.

Exam-Style Questions

Viva (short answer)

  1. Write the syntax of the IF function.
  2. What is the difference between COUNT and COUNTIF?
  3. Why is FALSE used as the last argument of VLOOKUP?
  4. What are the arguments of the PMT function?
  5. What is the advantage of INDEX/MATCH over VLOOKUP?

Lab test tasks

  1. Create a result sheet for ten students that calculates total, pass/fail (both subjects at least 40), and division using nested IF.
  2. Use COUNTIF, AVERAGE, MAX, and MIN to summarise the results.
  3. Use VLOOKUP to display a student’s total when their name is typed in a cell.
  4. Calculate the monthly EMI for a loan of Rs. 800,000 at 12% annual interest for 5 years.

📚 Continue to: Data Management Basics