Basic Formulas and Cell Referencing¶

Formulas are how Excel does work. This file covers the core formulas every analyst uses daily and the single concept that trips up more beginners than any other: absolute vs relative cell references.

Learning Objectives¶

Use the core aggregation formulas
Write conditional logic with IF
Master absolute ($) vs relative cell references
Handle errors gracefully with IFERROR

The Core Aggregation Formulas¶

=SUM(B2:B100)            ' total
=AVERAGE(B2:B100)        ' mean
=MEDIAN(B2:B100)         ' median (robust to outliers)
=COUNT(B2:B100)          ' count of numeric cells
=COUNTA(B2:B100)         ' count of non-blank cells (incl. text)
=MAX(B2:B100)            ' largest value
=MIN(B2:B100)            ' smallest value
=ROUND(B2, 2)           ' round to 2 decimal places

Conditional Aggregation (The Analyst's Workhorse)¶

' Sum revenue where status is "completed"
=SUMIF(D2:D100, "completed", G2:G100)

' Sum with MULTIPLE conditions (more common in practice)
=SUMIFS(G2:G100, D2:D100, "completed", C2:C100, "Electronics")

' Count matching cells
=COUNTIF(D2:D100, "cancelled")
=COUNTIFS(D2:D100, "completed", C2:C100, "Books")

' Average with condition
=AVERAGEIF(C2:C100, "VIP", G2:G100)

For mid-level analysts

SUMIFS (with the S) is far more useful than SUMIF because real questions have multiple conditions. Note the argument order differs: SUMIF(range, criteria, sum_range) but SUMIFS(sum_range, range1, criteria1, ...). The sum range comes LAST in SUMIF and FIRST in SUMIFS — a common source of errors.

The IF Function¶

' Basic IF
=IF(G2 > 200, "Large", "Small")

' Nested IF (for multiple bands)
=IF(G2 > 300, "Large", IF(G2 > 100, "Medium", "Small"))

' IFS (cleaner, Excel 2019+)
=IFS(G2 > 300, "Large", G2 > 100, "Medium", TRUE, "Small")

' IF with AND / OR
=IF(AND(G2 > 100, D2 = "completed"), "High-value sale", "Other")
=IF(OR(D2 = "pending", D2 = "processing"), "In progress", "Done")

Absolute vs Relative References — The #1 Beginner Trap¶

When you copy a formula, references shift. $ locks them.

Reference	When copied	Use for
`A1`	Both row and column shift	Normal cell-by-cell calculation
`$A$1`	Neither shifts (fully locked)	A constant (tax rate, target)
`$A1`	Column locked, row shifts	Locking to a column
`A$1`	Row locked, column shifts	Locking to a row

The classic mistake

Tax rate is in cell B1. You write =A2 * B1 and copy it down 100 rows. Row 2 works, but row 3 becomes =A3 * B2 — B2 is empty, so the tax breaks. Fix: lock the tax cell with =A2 * $B$1. Now copying keeps $B$1 fixed while A2 shifts correctly.

' Calculating discounted price for many rows, with discount % in $B$1
=A2 * (1 - $B$1)     ' copy down — $B$1 stays locked, A2 shifts to A3, A4...

Press F4 to cycle reference types

While editing a formula, put your cursor on a reference and press F4 repeatedly to cycle: A1 → $A$1 → A$1 → $A1 → back to A1.

Error Handling¶

' Catch errors (e.g., division by zero, failed lookups)
=IFERROR(A2 / B2, 0)                         ' return 0 instead of #DIV/0!
=IFERROR(VLOOKUP(A2, Data, 2, FALSE), "Not found")

' Check for specific conditions
=IF(B2 = 0, "N/A", A2 / B2)                  ' avoid division by zero

Common errors and what they mean:

Error	Meaning
`#DIV/0!`	Division by zero
`#N/A`	Lookup found no match
`#VALUE!`	Wrong data type (text where number expected)
`#REF!`	Reference to a deleted cell
`#NAME?`	Misspelled function or named range

Text Functions (Quick Reference)¶

=CONCAT(A2, " ", B2)            ' or A2 & " " & B2  — join text
=LEFT(A2, 3)                    ' first 3 characters
=RIGHT(A2, 4)                   ' last 4 characters
=LEN(A2)                        ' character count
=TRIM(A2)                       ' remove extra spaces
=UPPER(A2) / LOWER(A2) / PROPER(A2)
=TEXT(A2, "£#,##0.00")          ' format number as text

Practice Exercises¶

Warm-up 1. Given a column of sales values, calculate the total, average, max, and min. 2. Write an IF formula that labels orders over £200 as "Large". 3. Use SUMIF to total revenue for completed orders only.

Main 4. Calculate a discounted price for 50 products using a single discount rate stored in one cell — copy the formula down correctly using $. 5. Use SUMIFS to total revenue for completed orders in the Electronics category. 6. Use IFERROR to wrap a division that might hit a zero denominator.

Stretch 7. Build a small "order classifier": one column with nested IF (or IFS) that buckets orders into Small/Medium/Large/Enterprise. 8. Combine first and last name columns into a full name, properly capitalised with PROPER.

Interview Questions¶

[Beginner] What is the difference between SUM and SUMIF?

[Beginner] What does the $ symbol do in a cell reference?

[Mid-level] Explain the difference in argument order between SUMIF and SUMIFS.

[Mid-level] You copy a formula down a column and the results are wrong from the second row onward. What's the likely cause?

[Senior] How would you make a complex spreadsheet model auditable and less error-prone for others to maintain?

Previous: 00-agenda | Next: 02-pivot-tables