Microsoft Excel Mastery

Part II: Essential Excel Functions

Master Mathematical, Logical, Text, and Date/Time functions — with 140+ solved examples, real Indian datasets, and hands-on projects.

📐 40+ Formulas | 💻 4 Mini Projects | 📝 20+ MCQs | 🎯 12 Interview Questions

Chapter 4

Mathematical Functions

📊 Why Math Functions Matter in Every Indian Office

Whether you're a CA calculating GST returns, an HR manager computing salary breakdowns, or a teacher averaging CBSE marks for 500 students — Excel's mathematical functions save hours of manual computation. India's ₹3.5 lakh crore GST collection every month is processed using spreadsheets at the ground level before entering GSTN portals.

InfosysTCS PayrollCBSE SchoolsGST Returns

🎯 Learning Objectives

Use SUM, AVERAGE, MIN, MAX for basic calculations
Apply ROUND, ROUNDUP, ROUNDDOWN for precision control
Understand ABS, MOD, and POWER for advanced math
Master conditional functions: SUMIF, COUNTIF, AVERAGEIF and their multi-criteria variants
Use INT, CEILING, FLOOR for rounding variations
Solve 40+ real-world problems using mathematical functions

Section 4.1

SUM & AVERAGE Functions

Theory — The SUM Function

The SUM function is the most frequently used function in Excel. It adds up all the numbers you specify. Think of it as a calculator that can add thousands of numbers in a single keystroke. Every accountant in India — from a small shop in Chandni Chowk to the finance department at Reliance Industries — uses SUM daily.

📐 Formula Syntax

=SUM(number1, [number2], ...)

number1 (required): The first number, cell reference, or range to add. number2, ... (optional): Additional numbers/ranges, up to 255 arguments. SUM ignores text and logical values.

[Screenshot: SUM function being typed in the formula bar with cell references highlighted]

Example 1 — Sum a Simple Range (Monthly Sales)

A Flipkart seller wants to know total sales for Q1 (January–March):

A	B
Month	Sales (₹)
January	1,25,000
February	1,48,000
March	1,62,000

Formula in B5: =SUM(B2:B4)

Result: ₹4,35,000

Explanation: The function takes the range B2:B4 (all three months) and adds them: 1,25,000 + 1,48,000 + 1,62,000 = 4,35,000.

Example 2 — Sum Non-Contiguous Cells (Expense Report)

An employee at TCS has expenses in cells that are not next to each other:

A	B
Travel (B2)	8,500
Meals (B3)	2,200
Hotel (B4)	12,000
Courier (B5)	450
Cab (B6)	3,800

Formula: =SUM(B2, B4, B6) — to sum only Travel, Hotel, and Cab expenses.

Result: ₹24,300

Explanation: By passing individual cells separated by commas, you can pick specific items: 8,500 + 12,000 + 3,800 = 24,300.

Example 3 — 3D Reference SUM Across Sheets

A Reliance Jio store manager has monthly sales in separate sheets (Jan, Feb, Mar), with totals always in cell B10 of each sheet:

Formula: =SUM(Jan:Mar!B10)

Result: Adds B10 from every sheet between Jan and Mar

Explanation: A 3D reference lets you sum the same cell across multiple worksheets. This is powerful for monthly/quarterly consolidation. If Jan!B10 = 5,00,000, Feb!B10 = 6,20,000, Mar!B10 = 5,80,000, the result would be ₹17,00,000.

Press Alt + = to instantly insert a SUM formula for the selected range. Excel intelligently detects whether to sum the column above or the row to the left.

Theory — The AVERAGE Function

The AVERAGE function calculates the arithmetic mean — it adds all numbers and divides by the count. In Indian schools, teachers use this constantly: average marks of a class, average attendance percentage, or average score across subjects.

📐 Formula Syntax

=AVERAGE(number1, [number2], ...)

number1 (required): First number, reference, or range. number2, ... (optional): Additional values. AVERAGE ignores blank cells but includes cells containing 0.

Example 1 — Class Average (CBSE School)

A teacher at Delhi Public School wants to find the average Mathematics marks for 5 students:

A	B
Student	Maths Marks
Aarav	87
Priya	92
Rahul	76
Sneha	95
Vikram	83

Formula in B7: =AVERAGE(B2:B6)

Result: 86.6

Calculation: (87 + 92 + 76 + 95 + 83) ÷ 5 = 433 ÷ 5 = 86.6

Example 2 — Average Monthly Sales (Zomato Delivery Partner)

Month	Orders Delivered
Jan	342
Feb	298
Mar	376
Apr	415
May	389
Jun	401

Formula: =AVERAGE(B2:B7)

Result: 370.17

Example 3 — Average with Non-Contiguous Cells

Average only the first and last exam scores: =AVERAGE(B2, B6)

Result: (87 + 83) ÷ 2 = 85

AVERAGE vs. blank cells: If a cell is blank, AVERAGE ignores it (doesn't count it in the denominator). But if a cell contains 0, it IS included. So an empty cell and a cell with 0 give different averages! For instance, AVERAGE(10, 20, 0) = 10, but AVERAGE(10, 20, blank) = 15.