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.
- SUM
- AVERAGE
- COUNT
- COUNTA
- MAX/MIN
- SUMIF
- SUMIFS
- COUNTIF
- COUNTIFS
- IF
- IFS
- AND/OR
- IFERROR
- VLOOKUP
- HLOOKUP
- XLOOKUP
- INDEX
- INDEX MATCH
- MATCH
- TODAY/NOW
- DATEDIF/DAYS
- NETWORKDAYS
- ROUND/ROUNDUP/ROUNDDOWN
- LARGE/SMALL
- UNIQUE/FILTER/SORT
- SORT/FILTER
1. SUM
Basic Standard Sum
- Syntax:
=SUM(range1, [range2], ...)
- Sample Data:
A1:A4contains10,20,30,40.
- Formula:
=SUM(A1:A4)
- Output:
100
Variation A: Summing Disjoint (Non-Contiguous) Ranges
- Syntax:
=SUM(range1, range2, [constant1], ...)
- Explanation: Adds values from non-adjacent ranges or individual numbers in a single function call.
- Sample Data:
A1:A3(10,20,30),C1:C3(5,15,25).
- Formula:
=SUM(A1:A3, C1:C3, 100)
- Output:
205(60 + 45 + 100)
Variation B: 3D Sum Across Multiple Worksheets
- Syntax:
=SUM(First_Sheet:Last_Sheet!Cell_Or_Range)
- Explanation: Sums the same cell or range across multiple consecutive worksheets.
- Sample Data: Cell
A1inSheet1,Sheet2, andSheet3contains100,200, and300respectively.
- Formula:
=SUM(Sheet1:Sheet3!A1)
- Output:
600
2. AVERAGE
Basic Standard Average
- Syntax:
=AVERAGE(range1, [range2], ...)
- Sample Data:
B1:B4contains80,90,70,100.
- Formula:
=AVERAGE(B1:B4)
- Output:
85
Variation A: AVERAGEA (Including Logical Values & Text)
- Syntax:
=AVERAGEA(range1, [range2], ...)
- Explanation: Unlike standard
AVERAGEwhich ignores text/booleans,AVERAGEAevaluatesTRUEas 1,FALSEas 0, and text strings as 0.
- Sample Data (
A1:A4):10,TRUE,"N/A",30
- Formula:
=AVERAGEA(A1:A4)
- Output:
10.25((10 + 1 + 0 + 30) / 4)
Variation B: Excluding Zeros from Average Calculation
- Syntax:
=AVERAGEIF(range, criteria, [average_range])
- Explanation: Combines average logic with a condition to evaluate only non-zero entries.
- Sample Data (
A1:A4):100,0,200,0
- Formula:
=AVERAGEIF(A1:A4, ">0")
- Output:
150((100 + 200) / 2)
3. COUNT
Basic Standard Count
- Syntax:
=COUNT(range1, [range2], ...)
- Sample Data:
C1 = 15,C2 = "Apple",C3 = 42,C4 = [Blank].
- Formula:
=COUNT(C1:C4)
- Output:
2
Variation A: Counting Numeric Dates and Times
- Syntax:
=COUNT(range)
- Explanation: Excel stores dates and times as numbers, so standard
COUNTincludes them while ignoring text representations.
- Sample Data (
A1:A4):"2026-01-01"(as date number),"Pending"(text),45.5(number),"100"(text format)
- Formula:
=COUNT(A1:A4)
- Output:
2(Counts the date and45.5)
4. COUNTA
Basic Standard Count Non-Empty
- Syntax:
=COUNTA(range1, [range2], ...)
- Sample Data:
C1 = 15,C2 = "Apple",C3 = 42,C4 = [Blank].
- Formula:
=COUNTA(C1:C4)
- Output:
3
Variation A: Counting Non-Empty Rows in Dynamic Columns
- Syntax:
=COUNTA(column_range) - header_offset
- Explanation: Determines the number of active entries in an entire column to assist with dynamic range calculations.
- Sample Data (
Column A): Header inA1, followed by 12 customer records (A2:A13).
- Formula:
=COUNTA(A:A) - 1
- Output:
12
5. MAX and MIN
Basic Standard MAX & MIN
- Syntax:
=MAX(range)and=MIN(range)
- Sample Data:
D1:D4contains12,85,4,50.
Formula 1: =MAX(D1:D4) -> Output: 85
Formula 2: =MIN(D1:D4) -> Output: 4
Variation A: MAXIFS and MINIFS (Conditional Max/Min)
- Syntax:
=MAXIFS(max_range, criteria_range1, criteria1, ...)
=MINIFS(min_range, criteria_range1, criteria1, ...)
- Explanation: Finds the maximum or minimum value meeting specific conditional criteria.
- Sample Data:
A1:A4Category ("Tech","Tech","Office","Tech"),B1:B4Price (150,300,450,200).
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
- Syntax:
=SUMIF(criteria_range, criteria, [sum_range])
- Sample Data:
A1:A4Regions ("East","West","East","North"),B1:B4Sales (100,200,150,300).
- Formula:
=SUMIF(A1:A4, "East", B1:B4)
- Output:
250
Variation A: Wildcard Matching (* and ?)
- Syntax:
=SUMIF(criteria_range, "text*", [sum_range])
- Explanation: Uses wildcards where
*represents any number of characters and?represents a single character.
- Sample Data:
A1:A4("Widget Alpha","Gadget A","Widget Beta","Sprocket"),B1:B4(10,20,30,40).
- Formula:
=SUMIF(A1:A4, "Widget*", B1:B4)
- Output:
40(10 + 30)
Variation B: Dynamic Operator Construction using Cell References
- Syntax:
=SUMIF(criteria_range, "operator"&cell_reference, [sum_range])
- Explanation: Concatenates a logical operator (
>,<,<>) with a cell reference.
- Sample Data:
A1:A4Values (50,150,200,75),C1Threshold (100).
- Formula:
=SUMIF(A1:A4, ">"&C1)
- Output:
350(150 + 200)
7. SUMIFS
Basic Standard SUMIFS
- Syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
- Sample Data:
A1:A4Regions ("East","East","East","West"),B1:B4Status ("Paid","Pending","Paid","Paid"),C1:C4Sales (500,300,400,200).
- Formula:
=SUMIFS(C1:C4, A1:A4, "East", B1:B4, "Paid")
- Output:
900
Variation A: Date Range Summing (Between Two Dates)
- Syntax:
=SUMIFS(sum_range, date_range, ">=start_date", date_range, "<=end_date")
- Explanation: Restricts a sum to transactions occurring within a specified date window.
- Sample Data:
A1:A4Dates (2026-01-05,2026-01-15,2026-02-01,2026-02-10),B1:B4Amount (100,200,300,400).
- Formula:
=SUMIFS(B1:B4, A1:A4, ">=2026-01-01", A1:A4, "<=2026-01-31")
- Output:
300(100 + 200)
8. COUNTIF
Basic Standard COUNTIF
- Syntax:
=COUNTIF(range, criteria)
- Sample Data:
A1:A5contains"Pass","Fail","Pass","Pass","Fail".
- Formula:
=COUNTIF(A1:A5, "Pass")
- Output:
3
Variation A: Counting Blank and Non-Blank Cells
- Syntax:
- Blanks:
=COUNTIF(range, "")
- Non-Blanks:
=COUNTIF(range, "<>")
- Explanation: Uses
""to count empty cells or"<>"to count populated cells.
- Sample Data (
A1:A5):"Task 1","","Task 2","","Task 3"
Formula 1 (Blanks): =COUNTIF(A1:A5, “”) -> Output: 2
Formula 2 (Non-Blanks): =COUNTIF(A1:A5, “<>”) -> Output: 3
Variation B: Identifying Duplicate Rows
- Syntax:
=COUNTIF(expanding_range, current_cell)
- Explanation: Evaluates expanding cell references (e.g.,
A$1:A1) to flag occurrences greater than 1.
- Sample Data (
A1:A4):"Apple","Banana","Apple","Cherry"
- Formula (in Cell B1 copied down):
=COUNTIF(A$1:A1, A1)
- Outputs (
B1:B4):1,1,2,1
9. COUNTIFS
Basic Standard COUNTIFS
- Syntax:
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)
- Sample Data:
A1:A4Dept ("Sales","Sales","IT","Sales"),B1:B4Score (80,50,90,85).
- Formula:
=COUNTIFS(A1:A4, "Sales", B1:B4, ">=80")
- Output:
2
Variation A: Multiple Numeric Range Checks
- Syntax:
=COUNTIFS(range1, ">=min_val1", range2, ">=min_val2", ...)
- Explanation: Counts rows where values fall inside specific numeric boundaries across multiple criteria ranges.
- Sample Data:
A1:A4Age (22,35,45,29),B1:B4Income (45000,60000,85000,52000).
- Formula:
=COUNTIFS(A1:A4, ">=25", B1:B4, ">=50000")
- Output:
3
3. Logical & Conditional Formulas
10. IF
Basic Standard IF
- Syntax:
=IF(logical_test, value_if_true, value_if_false)
- Sample Data:
A1 = 72.
- Formula:
=IF(A1>=50, "Pass", "Fail")
- Output:
"Pass"
Variation A: Returning Blank Cells ("") Instead of Zero
- Syntax:
=IF(logical_test, "", formula_or_value)
- Explanation: Prevents formulas from displaying zeros when input data is missing.
- Sample Data: Cell
A1 = ""(Blank)
- Formula:
=IF(A1="", "", A1*1.1)
- Output:
""(Visually blank cell)
Variation B: Nested IF Statements
- Syntax:
=IF(test1, value1, IF(test2, value2, value3))
- Explanation: Evaluates multiple conditions sequentially by embedding
IFstatements within each other.
- Sample Data: Cell
A1 = 75
- Formula:
=IF(A1>=90, "Top", IF(A1>=70, "Average", "Low"))
- Output:
"Average"
11. IFS
Basic Standard IFS
- Syntax:
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)
- Sample Data:
A1 = 85.
- Formula:
=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", TRUE, "F")
- Output:
"B"
Variation A: Handling Fallback/Default Conditions
- Syntax:
=IFS(test1, val1, test2, val2, TRUE, fallback_val)
- Explanation: Uses
TRUEas the final test condition to catch any inputs that fail earlier tests.
- Sample Data: Cell
A1 = "Unknown"
- Formula:
=IFS(A1="North", 1, A1="South", 2, TRUE, 0)
- Output:
0
PGDCA VLOOKUP INDEX MATCH IF Formulas Important Questions 2026-27
12. Logical Operators: AND & OR
Basic Standard AND & OR
- Syntax:
=IF(AND(condition1, condition2), value_if_true, value_if_false)
=IF(OR(condition1, condition2), value_if_true, value_if_false)
- Sample Data:
A1 = 85(Exam),B1 = 90(Attendance).
- Formula:
=IF(AND(A1>=80, B1>=80), "Eligible", "Not Eligible")
- Output:
"Eligible"
Variation A: Combining AND with OR in Logic
- Syntax:
=IF(AND(cond1, OR(cond2, cond3)), value_if_true, value_if_false)
- Explanation: Evaluates compound conditions where some criteria are mandatory and others are alternative choices.
- Sample Data: Cell
A1 = "East"(Region),B1 = "Manager"(Role),C1 = 5(Years).
- Formula:
=IF(AND(A1="East", OR(B1="Manager", C1>=10)), "Eligible", "Ineligible")
- Output:
"Eligible"
13. IFERROR
Basic Standard IFERROR
- Syntax:
=IFERROR(value, value_if_error)
- Sample Data:
A1 = 100,B1 = 0.
- Formula:
=IFERROR(A1/B1, "Division Error")
- Output:
"Division Error"
Variation A: Nested Fallback Calculations
- Syntax:
=IFERROR(primary_formula, IFERROR(secondary_formula, fallback_value))
- Explanation: Executes an alternative secondary lookup or formula if the primary formula generates an error.
- Sample Data: Primary cell
A1 = 0, Secondary cellB1 = 10, TotalC1 = 100.
- Formula:
=IFERROR(C1/A1, IFERROR(C1/B1, 0))
- Output:
10
4. Lookup & Reference Formulas
14. VLOOKUP
Basic Standard VLOOKUP
- Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- Sample Data (
A1:B3):101|John,102|Sarah,103|Mike.
- Formula:
=VLOOKUP(102, A1:B3, 2, FALSE)
- Output:
"Sarah"
Variation A: Approximate Match Lookup (TRUE / 1)
- Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, TRUE)
- Explanation: Locates values in a sorted range that fall within numeric bands.
- Sample Data (
A1:B4):0|0%,10000|10%,50000|20%. Lookup value:35000.
- Formula:
=VLOOKUP(35000, A1:B4, 2, TRUE)
- Output:
"10%"
Variation B: Dynamic Column Indexing with COLUMN()
- Syntax:
=VLOOKUP(lookup_value, table_array, COLUMN(cell_ref), FALSE)
- Explanation: Replaces static column numbers with dynamic functions to allow formulas to be copied horizontally.
- Formula:
=VLOOKUP($A2, DataRange, COLUMN(B1), FALSE)
- Output: Dynamically returns column 2, auto-adjusting to column 3 when copied right.
15. HLOOKUP
Basic Standard HLOOKUP
- Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- Sample Data (
A1:C2): Row 1 ("Item","Price","Stock"), Row 2 ("Laptop",1200,15).
- Formula:
=HLOOKUP("Price", A1:C2, 2, FALSE)
- Output:
1200
Variation A: Horizontal Row Lookup across Headers
- Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, FALSE)
- Explanation: Locates a specific column header horizontally and retrieves data from a designated row beneath it.
- Sample Data (
A1:C2): Row 1 ("Q1","Q2","Q3"), Row 2 ($100,$200,$300).
- Formula:
=HLOOKUP("Q2", A1:C2, 2, FALSE)
- Output:
$200
16. XLOOKUP
Basic Standard XLOOKUP
- Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- Sample Data:
A1:A3Names ("John","Sarah","Mike"),B1:B3IDs (101,102,103).
- Formula:
=XLOOKUP(103, B1:B3, A1:A3, "Not Found")
- Output:
"Mike"
Variation A: Searching Bottom-to-Top (Reverse Lookup)
- Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], -1)
- Explanation: Sets
search_modeto-1to locate the last occurrence of an item in a list.
- Sample Data:
A1:A4Customer ("Acme","Beta","Acme","Delta"),B1:B4Status ("Old","Active","Newest","Active").
- Formula:
=XLOOKUP("Acme", A1:A4, B1:B4, "Not Found", 0, -1)
- Output:
"Newest"
Variation B: Multi-Column Array Return
- Syntax:
=XLOOKUP(lookup_value, lookup_array, multi_column_return_array)
- Explanation: Returns an entire range of multiple columns from a single matching row simultaneously.
- Sample Data: Lookup
ID = 101inA1:A3, returning both Name (B1:B3) and Department (C1:C3).
- Formula:
=XLOOKUP(101, A1:A3, B1:C3)
- Output: Spills two values:
["John", "Sales"]
17. INDEX
Basic Standard INDEX
- Syntax:
=INDEX(array, row_num, [column_num])
- Sample Data (
A1:B2):A1 = "Red",B1 = "Apple",A2 = "Yellow",B2 = "Banana".
- Formula:
=INDEX(A1:B2, 2, 2)
- Output:
"Banana"
Variation A: Returning an Entire Row or Column Array
- Syntax:
- Entire Row:
=INDEX(array, row_num, 0)
- Entire Column:
=INDEX(array, 0, column_num)
- Explanation: Setting row_num or col_num to
0makesINDEXreturn an entire row or column array for use in other functions.
- Sample Data (
A1:C3Grid):1|2|3,4|5|6,7|8|9.
- Formula:
=SUM(INDEX(A1:C3, 0, 2))(Sums Column 2)
- Output:
15(2 + 5 + 8)
18. MATCH
Basic Standard MATCH
- Syntax:
=MATCH(lookup_value, lookup_array, [match_type])
- Sample Data:
A1:A4contains"Gold","Silver","Bronze","Platinum".
- Formula:
=MATCH("Bronze", A1:A4, 0)
- Output:
3
Variation A: Approximate Search Types (1 and -1)
- Syntax:
=MATCH(lookup_value, lookup_array, 1)
- Explanation:
match_type = 1finds the largest value less than or equal to the target in an ascending list.
- Sample Data (
A1:A4sorted):10,20,30,40.
- Formula:
=MATCH(25, A1:A4, 1)
- Output:
2(Position of20)
19. INDEX MATCH Combination
Basic Standard INDEX MATCH
- Syntax:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
- Sample Data:
A1:A3Employee ID (101,102,103),B1:B3Salary ($50,000,$65,000,$80,000).
- Formula:
=INDEX(B1:B3, MATCH(102, A1:A3, 0))
- Output:
$65,000
Variation A: Two-Way Matrix Lookup (Row & Column Intersection)
- Syntax:
=INDEX(grid_range, MATCH(row_lookup_val, row_range, 0), MATCH(col_lookup_val, col_range, 0))
- Explanation: Uses two
MATCHfunctions insideINDEXto dynamically find both row and column positions.
- Sample Data (
A1:C3): HeadersB1:C1("2025","2026"), RowsA2:A3("Rent","Utilities"), ValuesB2:C3(Rent2025=1000,Rent2026=1200,Util2025=200,Util2026=250).
- Formula:
=INDEX(B2:C3, MATCH("Rent", A2:A3, 0), MATCH("2026", B1:C1, 0))
- Output:
1200
5. Date & Time Functions
20. TODAY and NOW
Basic Standard TODAY & NOW
- Syntax:
=TODAY()and=NOW()
- Formula 1:
=TODAY()$\rightarrow$ Output:10/04/2026
- Formula 2:
=NOW()$\rightarrow$ Output:10/04/2026 12:00 PM
Variation A: Dynamic Date Arithmetic
- Syntax:
- Add Days:
=TODAY() + num_days
- Subtract Time:
=NOW() - fractional_days
- Explanation: Performs relative date math by adding or subtracting days, hours, or weeks.
- Formula 1 (Due Date in 30 Days):
=TODAY() + 30
- Formula 2 (Time 12 Hours Ago):
=NOW() - 0.5
PGDCA Spreadsheet Functions and Syntax Solutions
21. DATEDIF and DAYS
Basic Standard DATEDIF & DAYS
- Syntax:
=DATEDIF(start_date, end_date, "unit")
=DAYS(end_date, start_date)
- Sample Data:
A1 = "2020-01-01",B1 = "2026-01-01".
- Formula:
=DATEDIF(A1, B1, "Y")
- Output:
6
Variation A: DATEDIF Interval Units ("YM", "MD", "YD")
- Syntax:
=DATEDIF(start_date, end_date, "YM")
- Explanation: Computes partial duration increments while ignoring years or months.
"YM": Difference in months, ignoring years.
"MD": Difference in days, ignoring months and years.
- Sample Data: Start =
"2024-01-15", End ="2026-03-20".
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
- Syntax:
=NETWORKDAYS(start_date, end_date, [holidays])
- Sample Data:
A1 = "2026-03-02"(Mon),B1 = "2026-03-06"(Fri),C1 = "2026-03-04"(Holiday).
- Formula:
=NETWORKDAYS(A1, B1, C1)
- Output:
4
Variation A: Custom Weekends with NETWORKDAYS.INTL
- Syntax:
=NETWORKDAYS.INTL(start_date, end_date, [weekend_code], [holidays])
- Explanation: Allows specification of non-standard weekend schedules (e.g., Sunday-only or Friday/Saturday).
- Sample Data: Start =
"2026-03-01"(Sun), End ="2026-03-07"(Sat). Weekend Code11= Sunday only.
- Formula:
=NETWORKDAYS.INTL("2026-03-01", "2026-03-07", 11)
- Output:
6
6. Rounding & Statistical Functions
23. Rounding Functions: ROUND, ROUNDUP, ROUNDDOWN
Basic Standard Rounding
- Syntax:
=ROUND(number, num_digits)
=ROUNDUP(number, num_digits)
=ROUNDDOWN(number, num_digits)
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
- Syntax:
=ROUND(number, -negative_digits)
- Explanation: Uses negative
num_digitsarguments to round numbers to tens (-1), hundreds (-2), or thousands (-3).
- Sample Input:
145,870
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
- Syntax:
=LARGE(array, k)and=SMALL(array, k)
- Sample Data:
A1:A5contains10,50,30,90,70.
Formula 1: =LARGE(A1:A5, 2) -> Output: 70
Formula 2: =SMALL(A1:A5, 1) -> Output: 10
Variation A: Averaging Top N Values Dynamically
- Syntax:
=AVERAGE(LARGE(array, {1, 2, ... k}))
- Explanation: Passes an array constant
{1,2,3}intoLARGEto retrieve the top 3 entries and averages them.
- Sample Data (
A1:A5):50,90,70,100,80.
- Formula:
=AVERAGE(LARGE(A1:A5, {1,2,3}))
- Output:
90((100 + 90 + 80) / 3)
7. Dynamic Array Functions (Modern Excel)
25. UNIQUE
Basic Standard UNIQUE
- Syntax:
=UNIQUE(array, [by_col], [exactly_once])
- Sample Data:
A1:A5contains"Red","Blue","Red","Green","Blue".
- Formula:
=UNIQUE(A1:A5)
- Output: Spills dynamic array:
"Red","Blue","Green"
Variation A: Extracting Items That Appear Exactly Once
- Syntax:
=UNIQUE(array, FALSE, TRUE)
- Explanation: Sets the 3rd parameter (
exactly_once) toTRUEto return only non-repeating items.
- Sample Data (
A1:A5):"A","B","A","C","B".
- Formula:
=UNIQUE(A1:A5, FALSE, TRUE)
- Output:
"C"
26. FILTER
Basic Standard FILTER
- Syntax:
=FILTER(array, include, [if_empty])
- Sample Data:
A1:A4Names ("John","Sarah","Mike","Anna"),B1:B4Dept ("Sales","IT","Sales","HR").
- Formula:
=FILTER(A1:A4, B1:B4="Sales", "None")
- Output: Spills dynamic array:
"John","Mike"
Variation A: Compound Filtering (AND / OR Logic)
- Syntax:
AND:=FILTER(array, (condition1) * (condition2), [if_empty])
OR:=FILTER(array, (condition1) + (condition2), [if_empty])
- Explanation: Uses multiplication (
*) forANDconditions and addition (+) forORconditions.
- Sample Data:
A1:A4Name ("Ann","Bob","Cal","Dan"),B1:B4Dept ("Sales","IT","Sales","IT"),C1:C4Score (80,90,95,70).
- Formula (Dept=”Sales” AND Score>85):
=FILTER(A1:C4, (B1:B4="Sales") * (C1:C4>85), "None")
- Output: Spills row:
["Cal", "Sales", 95]
PGDCA MS Excel Mathematical Logical Lookup Functions PDF Notes
Detailed Excel Formulas PGDCA Syllabus Question Answer
27. SORT
Basic Standard SORT
- Syntax:
=SORT(array, [sort_index], [sort_order], [by_col])
- Sample Data:
A1:A4contains"Banana","Apple","Dragonfruit","Cherry".
- Formula:
=SORT(A1:A4, 1, 1)
- Output: Spills sorted array:
"Apple","Banana","Cherry","Dragonfruit"
Variation A: Multi-Column Sorting
- Syntax:
=SORT(array, {col_index1, col_index2}, {order1, order2})
- Explanation: Passes array parameters into
sort_indexandsort_orderto sort by primary and secondary columns.
- Sample Data (
A1:B3):"East"|200,"West"|100,"East"|400.
- Formula:
=SORT(A1:B3, {1, 2}, {1, -1})
- Output: Sorts Column 1 Ascending (“East” first), then Column 2 Descending (
400before200).
28. SORT / FILTER / UNIQUE Combinations
Basic Standard Combination
- Syntax:
=SORT(UNIQUE(array))
- Sample Data:
A1:A5contains"Sales","IT","Sales","HR","IT".
- Formula:
=SORT(UNIQUE(A1:A5))
- Output:
"HR","IT","Sales"
Variation A: Fully Dynamic Dashboard List
- Syntax:
=SORT(UNIQUE(FILTER(array, criteria_range=criteria, [if_empty])))
- Explanation: Filters active records, extracts unique entries, and returns them alphabetically.
- Sample Data:
A1:A5Products ("TV","Radio","TV","Phone","Radio"),B1:B5Status ("In Stock","Out","In Stock","In Stock","In Stock").
- Formula:
=SORT(UNIQUE(FILTER(A1:A5, B1:B5="In Stock")))
- Output: Spills array:
"Phone","Radio","TV"
📊 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:
- Need clarification on VLOOKUP vs. XLOOKUP, Nested IF logic, cell referencing ($A$1), or INDEX-MATCH syntax in the Excel Formulas module? Drop your query in the comments section below!
- Save and bookmark this study guide for quick revision right before your semester examinations.
- Share this link with your fellow PGDCA batchmates and study groups to help everyone master spreadsheet applications!