Meritshot

Chapter 3 of 15

Formulas & Functions Basics

Master Excel formulas — cell references, arithmetic, SUM, AVERAGE, COUNT, IF, and building reusable calculation logic.

Meritshot8 min read
ExcelFormulasFunctionsSUMAVERAGECOUNTIFCell References
All Excel Chapters

How Formulas Work

Every formula in Excel starts with =. When you press Enter, Excel evaluates the formula and shows the result in the cell. The formula itself is visible in the Formula Bar when the cell is selected.

Cell B2: =A2*1.18
Formula Bar shows: =A2*1.18
Cell displays: 88500  (if A2 = 75000)

Arithmetic Operators

OperatorMeaningExampleResult
+Add=10+515
-Subtract=10-55
*Multiply=10*550
/Divide=10/52
^Power=2^101024
%Percent=75000*10%7500
&Text join="Hello "&"World"Hello World

Operator Precedence (BODMAS)

Excel follows standard mathematical order:

  1. Brackets ()
  2. Exponent ^
  3. Multiply * and Divide / (left to right)
  4. Add + and Subtract - (left to right)
=2+3*4     → 14  (multiply first)
=(2+3)*4   → 20  (bracket first)
=10/2+3    → 8   (divide first)
=10/(2+3)  → 2   (bracket first)

Cell References

Formulas rarely use hard-coded numbers — they reference cells so results update automatically when data changes.

Relative References

Adjust when you copy the formula:

B2: =A2*1.10    (salary + 10%)
Copy B2 down to B3: =A3*1.10  (adjusts automatically)
Copy B2 down to B4: =A4*1.10

Absolute References

Fixed — don't change when copied. Use $ to lock:

B2: =A2*$C$1    (C1 contains the tax rate 0.18)
Copy B2 to B3:  =A3*$C$1   (A adjusts, C1 stays locked)
Copy B2 to B4:  =A4*$C$1

Press F4 while in the formula to cycle through reference types:

  • A1 → $A$1 → A$1 → $A1 → A1

Mixed References

Lock either the row or the column:

$A1  — column A locked, row adjusts
A$1  — row 1 locked, column adjusts

Classic use — multiplication table:

B2: =$A2*B$1
Copy to entire grid → rows use column A, columns use row 1

3D References (Across Sheets)

Reference a cell on another sheet:

=Sheet2!A1        → cell A1 on Sheet2
='Q1 Sales'!B5    → cell B5 on sheet named "Q1 Sales" (quotes needed for spaces)
=SUM(Jan:Dec!B5)  → sum cell B5 across all sheets from Jan to Dec

Essential Functions

Excel has over 500 functions. Here are the most important ones to master first.

SUM

Add up a range of numbers:

=SUM(A1:A10)          → sum of 10 cells
=SUM(A1:A10, C1:C10)  → sum of two separate ranges
=SUM(A1, A3, A7)      → sum specific cells
=SUM(A:A)             → sum entire column (use carefully — slow on large sheets)

SUMIF — Conditional Sum

Sum only cells that meet a condition:

=SUMIF(range, criteria, [sum_range])

=SUMIF(B2:B10, "Finance", C2:C10)
→ Sum salaries in C where department in B = "Finance"

=SUMIF(C2:C10, ">80000")
→ Sum values in C that are above 80,000

=SUMIF(B2:B10, "Tech*", C2:C10)
→ Sum where department starts with "Tech" (wildcard *)

SUMIFS — Multiple Conditions

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

=SUMIFS(C2:C10, B2:B10, "Finance", C2:C10, ">75000")
→ Sum salaries where dept = Finance AND salary > 75000

AVERAGE, MIN, MAX

=AVERAGE(C2:C10)     → arithmetic mean
=MIN(C2:C10)         → smallest value
=MAX(C2:C10)         → largest value
=MEDIAN(C2:C10)      → middle value (50th percentile)
=MODE(C2:C10)        → most frequent value

=AVERAGEIF(B2:B10, "Finance", C2:C10)   → average for Finance only
=AVERAGEIFS(C2:C10, B2:B10, "Finance", C2:C10, ">70000")  → multiple criteria

COUNT Functions

FunctionCounts
COUNT(range)Cells with numbers
COUNTA(range)Cells that are not empty (text, numbers, anything)
COUNTBLANK(range)Empty cells
COUNTIF(range, criteria)Cells matching one condition
COUNTIFS(range1, c1, range2, c2)Cells matching multiple conditions
=COUNT(C2:C10)       → how many cells have numeric values
=COUNTA(A2:A10)      → how many cells are non-empty (for names, text, etc.)
=COUNTBLANK(C2:C10)  → how many salary cells are empty

=COUNTIF(B2:B10, "Finance")             → employees in Finance
=COUNTIF(C2:C10, ">80000")             → employees earning above 80k
=COUNTIF(A2:A10, "P*")                 → names starting with P
=COUNTIFS(B2:B10, "Finance", C2:C10, ">75000") → Finance employees over 75k

IF — Conditional Logic

=IF(logical_test, value_if_true, value_if_false)

=IF(C2>80000, "Senior", "Junior")
→ "Senior" if salary > 80000, else "Junior"

=IF(C2>80000, C2*0.20, C2*0.15)
→ 20% tax for high earners, 15% otherwise

=IF(B2="Finance", "In scope", "Out of scope")

Nested IF

Chain IFs for multiple conditions:

=IF(C2>=90000, "Band A",
 IF(C2>=75000, "Band B",
 IF(C2>=60000, "Band C", "Band D")))

Excel 2019+ offers IFS which is cleaner:

=IFS(C2>=90000, "Band A",
     C2>=75000, "Band B",
     C2>=60000, "Band C",
     TRUE, "Band D")          ← TRUE is the "else" catch-all

IFERROR — Handle Errors Gracefully

=IFERROR(formula, value_if_error)

=IFERROR(A2/B2, "N/A")        → shows "N/A" instead of #DIV/0!
=IFERROR(VLOOKUP(...), "")    → blank instead of #N/A

Math Functions

=ROUND(C2, 0)        → round to 0 decimal places (nearest integer)
=ROUND(C2, -3)       → round to nearest thousand
=ROUNDUP(C2, 2)      → always round up
=ROUNDDOWN(C2, 2)    → always round down
=INT(C2)             → round down to integer (truncate decimals)
=CEILING(C2, 500)    → round up to nearest multiple of 500
=FLOOR(C2, 500)      → round down to nearest multiple of 500
=ABS(C2)             → absolute value (remove negative sign)
=MOD(10, 3)          → remainder: 1
=POWER(2, 10)        → 2^10 = 1024
=SQRT(144)           → 12

Building Formulas Step by Step

Example: Full Payroll Calculation

Data columns:
A = Employee Name
B = Department
C = Base Salary
D = Years of Experience

Formulas:
E2 = =C2 * IF(D2>=5, 1.15, IF(D2>=3, 1.10, 1.05))   → salary with experience bonus
F2 = =ROUND(E2 * 0.20, 0)                              → 20% tax
G2 = =E2 - F2                                          → net pay

H15 = =SUM(G2:G11)                                     → total net payroll
H16 = =AVERAGE(G2:G11)                                 → average net pay
H17 = =MIN(G2:G11)                                     → lowest net pay
H18 = =MAX(G2:G11)                                     → highest net pay
H19 = =COUNTIF(E2:E11, ">80000")                       → count above 80k gross

Auditing Formulas

Trace Precedents and Dependents

  • Formulas tab → Trace Precedents — shows blue arrows to cells the formula reads
  • Formulas tab → Trace Dependents — shows arrows to cells that use this cell
  • Formulas tab → Remove Arrows — clears the arrows

Show All Formulas

Press `Ctrl + `` (backtick) to toggle between showing formulas and values in all cells. Very useful for auditing a sheet.

Formula Error Types

ErrorCause
#DIV/0!Dividing by zero or empty cell
#VALUE!Wrong argument type (e.g., text where number expected)
#REF!Reference to a deleted or invalid cell
#NAME?Function name misspelled or unrecognised
#N/AValue not found (common in VLOOKUP)
#NUM!Invalid numeric result (e.g., square root of negative)
#NULL!Incorrect use of space as intersection operator
######Column too narrow to display the value (widen the column)

Practical Examples

Example 1: Grade Calculator

A = Student Name
B = Score (out of 100)
C = =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))
D = =IF(B2>=60,"Pass","Fail")

Example 2: Loan EMI Calculation

Excel's PMT function calculates loan payments:

=PMT(rate/12, months, -loan_amount)

=PMT(9%/12, 240, -5000000)
→ Monthly EMI for a ₹50 lakh loan at 9% over 20 years

Example 3: Running Total

B2 = =A2                    → first row: just the value
B3 = =B2+A3                 → add current to running total
(drag B3 down to B10)

Or using OFFSET for a dynamic running total:

=SUM($A$2:A2)   → sum from A2 to current row (anchor start, let end float)

Common Mistakes

1. Forgetting $ in absolute references

Wrong: =A2*C1  → when copied, C1 becomes C2, C3, etc.
Correct: =A2*$C$1  → C1 always stays C1

2. Circular references

A formula in A1 that references A1 → circular reference!
Excel warns you and shows 0 or iterates (check Enable Iterative Calculation in Options)

3. SUMIF criteria with text — forgetting quotes

Wrong:  =SUMIF(B:B, Finance, C:C)     → #NAME? error
Correct: =SUMIF(B:B, "Finance", C:C)  → quotes around text criteria

4. Hardcoding constants in formulas

Bad:   =C2*0.20    (what if tax rate changes?)
Better: =C2*$F$1  (F1 holds the tax rate — change in one place)

Practice Exercises

  1. Build a payroll sheet: calculate tax (20% for salary > 80,000, else 15%) and net pay for 5 employees.
  2. Use SUMIF to total salaries for each department separately.
  3. Use COUNTIFS to count employees in Finance earning over ₹75,000.
  4. Create a grade calculator: score → A/B/C/D/F using nested IF or IFS.
  5. Create a formula that shows an employee's hire year seniority bonus: 1% per year of service (column D has years of service).

Summary

In this chapter you learned:

  • All formulas start with =
  • Arithmetic operators: +, -, *, /, ^; follow BODMAS precedence
  • Relative references adjust when copied; absolute references ($A$1) stay fixed
  • Press F4 to cycle through reference types
  • SUM, SUMIF, SUMIFS — add values with optional conditions
  • AVERAGE, AVERAGEIF, AVERAGEIFS — calculate means
  • COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS — count cells
  • IF(test, true, false) — conditional logic; nest IFs or use IFS()
  • IFERROR(formula, fallback) — prevent error display
  • Common errors: #DIV/0!, #VALUE!, #REF!, #N/A, #NAME?
  • Use Trace Precedents/Dependents and `Ctrl+`` for formula auditing

Next up: Text Functions — manipulate, extract, clean, and combine text data.

Previous

Data Entry & Formatting

Next

Text Functions