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

📚 Continue to: Data Management Basics