Topic 2 of 15, EHL Maths & Excel final (AP)
Σ
SUM, AVERAGE, MAX, MIN, LARGE, SMALL, COUNT, COUNTA
The basic functions are easy points in the exam, as long as you pick the one that matches the words of the question.
When the exam asks for it
- “total”, “add all” → SUM
- “mean”, “average” → AVERAGE
- “highest” / “lowest” → MAX / MIN
- “2nd largest”, “3rd highest” → LARGE
- “2nd smallest”, “3rd lowest” → SMALL
- “how many numbers” → COUNT
- “how many entries / non-empty cells” → COUNTA
Formulas
Total
=SUM(range)Average
=AVERAGE(range)Highest / lowest
=MAX(range) / =MIN(range)k-th largest
=LARGE(range, k)k-th smallest
=SMALL(range, k)Count numbers
=COUNT(range)Count non-empty cells
=COUNTA(range)Count text cells (mixed range)
=COUNTA(range)-COUNT(range)Worked example
B9fx
=SUM(B2:B8)| A | B | C | |
|---|---|---|---|
| 1 | Night | Room revenue (CHF) | |
| 2 | Mon | 320 | |
| 3 | Tue | 450 | |
| 4 | Wed | 280 | |
| 5 | Thu | 510 | |
| 6 | Fri | 390 | |
| 7 | Sat | 450 | |
| 8 | Sun | closed | |
| 9 |
Sheet1
⬇ Download practice ExcelExample, Practice and Answers sheets with working formulas. Free.
Step by step
=SUM(B2:B8)2,400=AVERAGE(B2:B8)400 (the text “closed” is ignored)=MAX(B2:B8)510=LARGE(B2:B8, 2)450=SMALL(B2:B8, 2)320=COUNT(B2:B8)6=COUNTA(B2:B8)7=COUNTA(B2:B8)-COUNT(B2:B8)1 text cell
⚠ Exam traps
- LARGE and SMALL count duplicates: in 450, 510, 450 the 2nd largest is 450, not a different number.
- COUNT only counts numbers. COUNTA counts everything that is not empty.
- Text-counting trap: COUNTA-COUNT gives the number of text cells in a mixed range, but it also counts TRUE/FALSE and error values if there are any.
- AVERAGE ignores text and empty cells, but a 0 is a number and is included.
Practice question
A range contains 12, 30, 18, 30, 25. What does =LARGE(range, 2) return?
Show answer
✓ 30. The two 30s are counted separately, so the 1st largest is 30 and the 2nd largest is also 30.
Stuck on this topic before your exam? We explain it one-to-one on your own Excel files, live online. Short crash courses before the EHL Maths & Excel final are possible on weekdays and weekends.
Book a lesson
Tell us your course, your exam date and the topics that worry you. We start with what matters most for your grade.
WhatsApp is preferred
The fastest way to book: send us a message and we usually answer the same day.
or email info@ytutor.net
Send a message
We answer by email or WhatsApp.