BPMNK2033 · Chapter 2

Basic Excel Formulas & Functions

From raw numbers to real answers — summarise, decide, count and look up data using the Top-20 Vehicles 2021 dataset.

=SUM(D2:D21)

=MAX(D2:D21)

=IF(D2>E2,"GROWTH","DECLINE")

=COUNTIF(B2:B21,"Toyota")

=VLOOKUP("Honda Civic",C2:E21,2,FALSE)

Business Intelligence & Data AnalyticsSchool of Business Management, UUM

Dr. Khairol Anuar IshakSession A261 · Sep Sem 2026/2027

Navigate→ Space next · M menu · F full screen

Roadmap

Today's plan — 70 minutes

Short explanations, then you try every function live on these slides. Keep Excel open and follow along.

00–05

Warm-up & dataset

Meet the Top-20 vehicles data.

5 min

05–12

Formula anatomy

=, functions, arguments, ranges.

7 min

12–27

Summarise

SUM · MIN · MAX · AVERAGE + quick check.

15 min

27–42

Decide

IF · AND · OR + quick check.

15 min

42–50

Count

COUNT · COUNTA · COUNTIF.

8 min

50–62

Look up

VLOOKUP · HLOOKUP · errors.

12 min

62–70

Exit ticket

5-question check + your exercise.

8 min

By the end of this lecture

You will be able to…

1

Write formulas correctly

Use =, brackets, commas, cell references and ranges without errors.

2

Summarise data

Find totals, extremes and typical values.

SUMMINMAXAVERAGE

3

Make decisions

Label and flag records based on conditions.

IFANDOR

4

Count what matters

Count numbers, entries and records that meet a rule.

COUNTCOUNTACOUNTIF

5

Retrieve values

Pull a matching value from a table automatically.

VLOOKUPHLOOKUP

6

Fix common errors

Read and repair #NAME?, #VALUE!, #N/A and #REF!.

Why it matters in BI: every dashboard, KPI and report is built on formulas like these. Get the formula right and the insight follows.

Dataset · top20vehicles2021

Meet the dataset

The 20 best-selling vehicle models of 2021, with unit sales for 2021 and 2020.

Col	Field	Type
A	Rank (2021)	Number
B	Make (brand)	Text
C	Model	Text
D	Sales 2021 (units)	Number
E	Sales 2020 (units)	Number

Think first: Which models grew in 2021? How many sold more than 250,000? By the end of today, formulas will answer these in seconds.

Row 1 holds headers, so data sits in rows 2–21. Figures are approximate U.S. unit sales compiled from public industry reports, for teaching use.

Foundations

Anatomy of a formula

Click or hover each part.

=SUM(D2:D21)

=IF(D2>E2,"GROWTH","DECLINE")

=

Equals sign

Every formula starts with =. Without it, Excel treats what you type as plain text.

Foundations

Cell references & ranges

You type	Meaning
`D2`	One cell: column D, row 2
`D2:D21`	Range — the colon means “to”
`B2:E2`	A row range (one record)
`C2:E6`	A block: 3 columns × 5 rows
`$D$2`	Absolute — stays fixed when copied

Relative vs absolute: copy =D2>E2 down one row and Excel changes it to =D3>E3. Add $ to lock a reference: $D$2 never moves.

Type a range in the formula bar → the cells light up.

Summarise · 1 of 3

SUM — add it all up

=SUM(number1, [number2], …)

Adds values. Give it a range, single cells, or a mix: =SUM(D2:D21), =SUM(D2,D5,D9).

Total 2021 sales of the top 20: =SUM(D2:D21)
Your turn: total for 2020?
Did the top 20 grow overall? Subtract the two totals.

Shortcut: select the cell below a column and press Alt+= (AutoSum).

Summarise · 2 of 3

MIN & MAX — find the extremes

=MIN(range) =MAX(range)

MIN returns the smallest value; MAX returns the largest. The matching cell is highlighted in yellow.

Lowest 2021 sales in the top 20?
Highest 2020 sales?
Range (spread) = MAX − MIN

Notice: MIN/MAX return the number, not the model name. To fetch the name you need a lookup — coming later today.

Summarise · 3 of 3

AVERAGE — the typical value

=AVERAGE(number1, [number2], …)

Adds the numbers and divides by how many there are (the mean). Empty and text cells are ignored.

Average 2021 sales: =AVERAGE(D2:D21)
Tidy it: =ROUND(AVERAGE(D2:D21),0)
Compare with the middle value: =MEDIAN(D2:D21)

Analyst's eye: the mean is higher than the median because a few giants (F-Series, Ram, Silverado) pull it up. Always ask: is the average typical?

Quick check · 3 questions

Quick check #1 — summarising

Decide · warm-up

Logical tests — every answer is TRUE or FALSE

=equal to

<>not equal to

>greater than

<less than

>=greater or equal

<=less or equal

=D2>E2 asks: “Did this model sell more in 2021 than in 2020?”

Written once in F2, then filled down to F21. Hover a result to see how the references shift row by row.

Decide · 1 of 3

IF — choose between two outcomes

=IF(logical_test, value_if_true, value_if_false)

Did each model grow or decline in 2021?

=IF(D2>E2, "GROWTH", "DECLINE")

Label carefully: if 2021 > 2020, that is growth. Unit sales tell us growth or decline — not profit or loss (that needs revenue and cost).

Text needs straight double quotes "GROWTH". Single quotes 'GROWTH' → Excel rejects the formula. Curly “smart” quotes “GROWTH” (copied from Word/WhatsApp) → #NAME?

Decide · 2 of 3

AND — all conditions must be TRUE

=AND(logical1, [logical2], …)

Which models sold more than 250,000 in both 2021 and 2020?

=AND(D2>250000, E2>250000)

“Between” is an AND too: =AND(D2>=250000, D2<=400000)

Never type thousand separators in a formula. 250,000 is read as two arguments: 250 and 000. Try the last button to see.

Decide · 3 of 3

OR — at least one condition is TRUE

=OR(logical1, [logical2], …)

Which models sold more than 250,000 in either 2021 or 2020?

=OR(D2>250000, E2>250000)

Test 1	Test 2	AND	OR
TRUE	TRUE	TRUE	TRUE
TRUE	FALSE	FALSE	TRUE
FALSE	TRUE	FALSE	TRUE
FALSE	FALSE	FALSE	FALSE

Quick check · 3 questions

Quick check #2 — logic

Count · 1 of 2

COUNT & COUNTA — how many?

=COUNT(range) =COUNTA(range)

Function	Counts	B2:B21 (text)
`COUNT`	cells with numbers	0
`COUNTA`	cells that are not empty	20

Data-quality check: if COUNT(D2:D21) is less than 20, some sales figures were typed as text (e.g. '726004) or are missing.

Count · 2 of 2

COUNTIF — count with a rule

=COUNTIF(range, criteria)

Criteria	Counts cells that…
`">250000"`	are greater than 250,000
`"Toyota"`	equal Toyota (not case-sensitive)
`"T*"`	start with T (`*` = any characters)
`"<>Ford"`	are anything except Ford

Bonus — SUMIF: total 2021 sales for Toyota only: =SUMIF(B2:B21,"Toyota",D2:D21)

Look up · 1 of 3

VLOOKUP — find it, then read across

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_valueWhat to find — e.g. "Honda Civic"

table_arrayWhere — C2:E21 (search column first!)

col_index_numWhich column of the table to return (C=1, D=2, E=3)

FALSEExact match. Use it almost always.

Build it — pick a model Return

Look up · 2 of 3

VLOOKUP — four golden rules

1

Search column comes first

VLOOKUP only searches the left-most column of table_array. To find by model, start the table at column C: C2:E21, not A2:E21.

2

Count columns from the table, not from A

In C2:E21, column D is 2 and column E is 3. A number larger than the table width gives #REF!.

3

Use FALSE for exact match

Omitting it means approximate match, which needs sorted data and can quietly return the wrong row.

4

Lock the table before copying

Filling down? Use $C$2:$E$21 so the table doesn't slide. F4 toggles the $ signs.

Using Microsoft 365? XLOOKUP is the modern successor — it can look left, defaults to exact match, and doesn't need a column number. Master VLOOKUP first; it appears in almost every workplace workbook.

Look up · 3 of 3

HLOOKUP — same idea, sideways

=HLOOKUP(lookup_value, table_array, row_index_num, FALSE)

Searches the top row of the table, then reads down. Useful when categories run across columns — like this brand summary built with SUMIF in H1:N3.

VLOOKUP

Find in first column → read across to column n.

HLOOKUP

Find in first row → read down to row n.

Your turn · use the table above

Troubleshooting

Common errors — and how to fix them

#NAME?

Misspelt function, or text without quotes.

try: =SUMM(D2:D21)

#NAME?

Text must be in double quotes: "GROWTH".

try: =IF(D2>E2,GROWTH,…)

#VALUE!

Maths on text — e.g. multiplying a model name.

try: =C2*2

#N/A

Lookup value not found — check spelling and spaces.

try: =VLOOKUP("Civic",…)

#REF!

Column number bigger than the table (C:E has 3).

try: col_index 4

#DIV/0!

Averaging an empty range — nothing to divide by.

try: =AVERAGE(F2:F21)

Click any error card to reproduce it here, then fix the formula yourself and press Enter.

Practice

Formula playground

Try anything from today. Tick Fill down to copy your formula from F2 to F21.

How many models declined in 2021?
=SUM(E2:E21)… no! Try IF + fill down, then count.
What is Toyota's average 2021 sales? AVERAGEIF
What did the rank 1 model sell in 2020?
Growth % for each model: =(D2-E2)/E2
Why do AVERAGEIF and VLOOKUP stay in F2 only? Because of $ locking — read the message.

Supported: SUM, MIN, MAX, AVERAGE, MEDIAN, ROUND, ABS, COUNT, COUNTA, COUNTIF, SUMIF, AVERAGEIF, IF, AND, OR, NOT, VLOOKUP, HLOOKUP, and + − * / ^ & operators.

Exit ticket · 5 questions

Exit ticket — how much stuck?

Wrap-up

Key takeaways

SUMAdds values or ranges.

MIN / MAXSmallest / largest value.

AVERAGEThe mean — check it against the median.

COUNT / COUNTIFHow many — in total, or meeting a rule.

IFOne outcome if TRUE, another if FALSE.

ANDTRUE only when every test is TRUE.

ORTRUE when any test is TRUE.

V / HLOOKUPFind a key, return a matching value. Use FALSE.

📝 Your task this week

Complete the Chapter 2 Top-20 Vehicles Excel exercise and upload your .xlsx on the course site. You get your score and feedback immediately.

✅ Before you submit

No thousand separators inside formulas · text in "double quotes" · VLOOKUP with FALSE · check every result makes sense.