Looking for comprehensive PGDCA (Post Graduate Diploma in Computer Applications) notes on Detailed Excel Formulas for the 2026–27 academic session? Access complete theory explanations, syntax breakdowns, step-by-step examples, short notes, long analytical answers, objective MCQs, and exam-oriented solutions covering Mathematical functions (SUM, AVERAGE, ROUND), Logical functions (IF, AND, OR, Nested IF), Lookup/Reference functions (VLOOKUP, HLOOKUP, XLOOKUP, INDEX-MATCH), Text functions (CONCATENATE, LEFT, RIGHT, MID), Date/Time functions, Financial formulas (PMT, NPV), and Error Handling (IFERROR) tailored for university diploma exams.

Master Excel with this comprehensive guide to essential Excel formulas, complete with exact syntaxes, practical variations, and real-world examples to boost your spreadsheet productivity.

  1. SUM
  1. AVERAGE
  1. COUNT
  1. COUNTA
  1. MAX/MIN
  1. SUMIF
  1. SUMIFS
  1. COUNTIF
  1. COUNTIFS
  1. IF
  1. IFS
  1. AND/OR
  1. IFERROR
  1. VLOOKUP
  1. HLOOKUP
  1. XLOOKUP
  1. INDEX
  1. INDEX MATCH
  1. MATCH
  1. TODAY/NOW
  1. DATEDIF/DAYS
  1. NETWORKDAYS
  1. ROUND/ROUNDUP/ROUNDDOWN
  1. LARGE/SMALL
  1. UNIQUE/FILTER/SORT
  1. SORT/FILTER

1. SUM

Basic Standard Sum

Variation A: Summing Disjoint (Non-Contiguous) Ranges

Variation B: 3D Sum Across Multiple Worksheets

2. AVERAGE

Basic Standard Average

Variation A: AVERAGEA (Including Logical Values & Text)

Variation B: Excluding Zeros from Average Calculation

3. COUNT

Basic Standard Count

Variation A: Counting Numeric Dates and Times

4. COUNTA

Basic Standard Count Non-Empty

Variation A: Counting Non-Empty Rows in Dynamic Columns

5. MAX and MIN

Basic Standard MAX & MIN

Formula 1: =MAX(D1:D4) -> Output: 85
Formula 2: =MIN(D1:D4) -> Output: 4

Variation A: MAXIFS and MINIFS (Conditional Max/Min)

Formula 1 (MAXIFS): =MAXIFS(B1:B4, A1:A4, “Tech”) -> Output: 300
Formula 2 (MINIFS): =MINIFS(B1:B4, A1:A4, “Tech”) -> Output: 150

PGDCA Excel Formulas Notes

2. Conditional Calculations

6. SUMIF

Basic Standard SUMIF

Variation A: Wildcard Matching (* and ?)

Variation B: Dynamic Operator Construction using Cell References

7. SUMIFS

Basic Standard SUMIFS

Variation A: Date Range Summing (Between Two Dates)

8. COUNTIF

Basic Standard COUNTIF

Variation A: Counting Blank and Non-Blank Cells

Formula 1 (Blanks): =COUNTIF(A1:A5, “”) -> Output: 2
Formula 2 (Non-Blanks): =COUNTIF(A1:A5, “<>”) -> Output: 3

Variation B: Identifying Duplicate Rows

9. COUNTIFS

Basic Standard COUNTIFS

Variation A: Multiple Numeric Range Checks

3. Logical & Conditional Formulas

10. IF

Basic Standard IF

Variation A: Returning Blank Cells ("") Instead of Zero

Variation B: Nested IF Statements

11. IFS

Basic Standard IFS

Variation A: Handling Fallback/Default Conditions

PGDCA VLOOKUP INDEX MATCH IF Formulas Important Questions 2026-27

12. Logical Operators: AND & OR

Basic Standard AND & OR

Variation A: Combining AND with OR in Logic

13. IFERROR

Basic Standard IFERROR

Variation A: Nested Fallback Calculations

4. Lookup & Reference Formulas

14. VLOOKUP

Basic Standard VLOOKUP

Variation A: Approximate Match Lookup (TRUE / 1)

Variation B: Dynamic Column Indexing with COLUMN()

15. HLOOKUP

Basic Standard HLOOKUP

Variation A: Horizontal Row Lookup across Headers

16. XLOOKUP

Basic Standard XLOOKUP

Variation A: Searching Bottom-to-Top (Reverse Lookup)

Variation B: Multi-Column Array Return

17. INDEX

Basic Standard INDEX

Variation A: Returning an Entire Row or Column Array

18. MATCH

Basic Standard MATCH

Variation A: Approximate Search Types (1 and -1)

19. INDEX MATCH Combination

Basic Standard INDEX MATCH

Variation A: Two-Way Matrix Lookup (Row & Column Intersection)

5. Date & Time Functions

20. TODAY and NOW

Basic Standard TODAY & NOW

Variation A: Dynamic Date Arithmetic

PGDCA Spreadsheet Functions and Syntax Solutions

21. DATEDIF and DAYS

Basic Standard DATEDIF & DAYS

Variation A: DATEDIF Interval Units ("YM", "MD", "YD")

Formula 1 (“YM”): =DATEDIF(“2024-01-15”, “2026-03-20”, “YM”) -> Output: 2
Formula 2 (“MD”): =DATEDIF(“2024-01-15”, “2026-03-20”, “MD”) -> Output: 5

22. NETWORKDAYS

Basic Standard NETWORKDAYS

Variation A: Custom Weekends with NETWORKDAYS.INTL

6. Rounding & Statistical Functions

23. Rounding Functions: ROUND, ROUNDUP, ROUNDDOWN

Basic Standard Rounding

Sample Input: 12.3456
Formula 1: =ROUND(12.3456, 2) -> Output: 12.35
Formula 2: =ROUNDUP(12.341, 2) -> Output: 12.35
Formula 3: =ROUNDDOWN(12.349, 2) -> Output: 12.34

Variation A: Rounding to Multiples/Whole Powers of 10

Formula 1 (Nearest Hundred): =ROUND(145870, -2) -> Output: 145900
Formula 2 (Nearest Thousand): =ROUND(145870, -3) -> Output: 146000

24. LARGE and SMALL

Basic Standard LARGE & SMALL

Formula 1: =LARGE(A1:A5, 2) -> Output: 70
Formula 2: =SMALL(A1:A5, 1) -> Output: 10

Variation A: Averaging Top N Values Dynamically

7. Dynamic Array Functions (Modern Excel)

25. UNIQUE

Basic Standard UNIQUE

Variation A: Extracting Items That Appear Exactly Once

26. FILTER

Basic Standard FILTER

Variation A: Compound Filtering (AND / OR Logic)

=FILTER(A1:C4, (B1:B4="Sales") * (C1:C4>85), "None")

PGDCA MS Excel Mathematical Logical Lookup Functions PDF Notes

Detailed Excel Formulas PGDCA Syllabus Question Answer

27. SORT

Basic Standard SORT

Variation A: Multi-Column Sorting

28. SORT / FILTER / UNIQUE Combinations

Basic Standard Combination

Variation A: Fully Dynamic Dashboard List

📊 PGDCA Exam Preparation Notice: This page provides curated PGDCA (Post Graduate Diploma in Computer Applications) Detailed Excel Formulas Study Material & Solutions updated for the 2026–27 academic session. This complete guide features functional formula syntax guides, absolute vs. relative cell referencing rules, practical spreadsheet examples, step-by-step calculation workflows, and high-yield exam questions designed to help you secure top marks in your semester examinations.

💡 Master PGDCA Spreadsheet Software & Data Analysis:

Leave a Reply

Your email address will not be published. Required fields are marked *