Learning Objectives

  • Use logical functions (IF, AND, OR, NOT)
  • Apply lookup functions (VLOOKUP, HLOOKUP, INDEX, MATCH)
  • Calculate with statistical functions (SUM, AVERAGE, COUNT, MAX, MIN)
  • Use mathematical functions (ROUND, ABS, POWER, SQRT)
  • Apply financial functions (PMT, FV, PV, RATE)
  • Work with date and time functions (TODAY, NOW, DATE, DATEDIF)

Step-by-Step Guide

1) Logical Functions

  • IF, AND, OR, NOT for decision rules.

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

2) Lookup Functions

  • VLOOKUP/HLOOKUP for basic lookup
  • INDEX + MATCH for flexible and robust lookup

Example: =INDEX($E$2:$E$100, MATCH(A2, $A$2:$A$100, 0))

3) Statistical Functions

  • SUM, AVERAGE, COUNT, MAX, MIN
  • Use for summaries and KPI dashboard cells

4) Mathematical Functions

  • ROUND, ABS, POWER, SQRT
  • Standardize precision for reporting

5) Financial Functions

  • PMT, FV, PV, RATE

Example: =PMT(10%/12, 60, -500000) for monthly EMI estimate.

6) Date and Time Functions

  • TODAY, NOW, DATE, DATEDIF

Example: =DATEDIF(B2, TODAY(), "Y") for years of service.

Function Design Tips

  • Use named ranges for readability
  • Split long formulas into helper columns
  • Validate edge cases (blank cells, text in numeric fields)

Practice Task

Build a mini-dashboard with:

  • Pass/fail logic
  • Product price lookup
  • 5 KPI stats
  • EMI calculator
  • Tenure calculator using dates

📚 Continue to: Linking Workbooks