📎 This article includes 1 downloadable practice file ↓
In this article
A function is a ready-made formula with a name. Instead of =B2+B3+B4+…+B500 you write =SUM(B2:B500). This lesson covers the five you’ll use every single day, and the one detail that trips everyone up: how they treat blanks and text.
How a function is written
=FUNCTION(argument1, argument2, ...)
=SUM(B2:B11)The name, then brackets holding the arguments, usually a range. As you type =SU, Excel suggests functions; press Tab to accept one. A tooltip then shows what arguments it wants.
AutoSum: Alt + =
Click the cell under a column of numbers and press Alt + =. Excel writes =SUM(…) with the range filled in; press Enter. The arrow next to the AutoSum button (Home tab, Σ) offers Average, Count, Max and Min the same way.
The five
| Function | Does | Example (marks in B2:B11) |
|---|---|---|
| SUM | Adds | =SUM(B2:B11) |
| AVERAGE | Sum ÷ count of numbers | =AVERAGE(B2:B11) |
| COUNT | How many cells contain numbers | =COUNT(C2:C11) |
| MIN / MAX | Smallest / largest | =MAX(B2:B11) |
Several ranges are fine: =SUM(B2:B11, D2:D11), or a row: =SUM(B2:D2) for one student’s total.
The COUNT family
| Function | Counts |
|---|---|
| COUNT | Numbers only (and dates, which are numbers) |
| COUNTA | Anything that isn’t empty: numbers, text, errors |
| COUNTBLANK | Empty cells |
In the practice marks sheet, English has 9 marks and one “Absent”. =COUNT(D2:D11) gives 9, =COUNTA(D2:D11) gives 10. Comparing the two is a quick way to find text hiding in a number column.
Blanks, zeros and text in averages
- AVERAGE ignores blank cells and text. A student with “Absent” isn’t counted.
- AVERAGE includes zeros. If an absent student is entered as 0, the class average drops.
- Decide which you mean. For “average of those who appeared”, leave absentees blank or as text.
Second highest, third lowest: LARGE and SMALL
=LARGE(B2:B11, 2) ' second-highest mark
=SMALL(B2:B11, 3) ' third-lowestLARGE(range, 1) is the same as MAX.
Rounding the result
An average like 60.4782 looks messy. Wrap it: =ROUND(AVERAGE(B2:B11), 1) gives 60.5. Unlike removing decimals with the toolbar button, ROUND changes the actual value.
Quick checks without formulas
Select any range and look at the status bar: Average, Count and Sum appear instantly. Right-click it to add Min, Max and Numerical Count. Use it to double-check a total in an email before replying.
Where beginners go wrong
| Mistake | Result |
|---|---|
| Total row included inside the SUM range | Double counting; or a circular-reference warning |
| COUNT on a text column (names) | 0; use COUNTA |
| Zeros for missing data | Averages too low |
| Range that stops early (B2:B10 instead of B2:B11) | Last row left out; use Tables (Lesson 8) so ranges grow |
Practice
Download this lesson’s workbook below. The Marks sheet has 10 students with one blank and one “Absent” so you can see how each function treats them. Type your answers in the yellow column; the Check column turns green when you’re right, and the Answers sheet shows a working formula for every task.
📎 Practice files for this article
- 📗Lesson 5 practice workbookA class marks sheet with a blank and an "Absent": totals, averages, counts, highest and lowest.⬇ XLSX · 13 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.