Practice by topic / Excel / Counting & Averaging
Counting and averaging are how you summarise a list: how many orders, how many late, what the typical value is. Most reports start here. These 10 exercises build up step by step, starting with count numeric scores only, count all filled-in responses, count how many grades were entered.
3 of 10 free · no sign-up needed · graded on the formula you write
A1:A6 hold test scores, but some cells contain text like "absent" instead of a number. Write a formula in A7 that counts only the numeric scores.
| A | |
|---|---|
| 1 | 85 |
| 2 | absent |
| 3 | 92 |
| 4 | 78 |
| 5 | absent |
| 6 | 88 |
| 7 | ? |
Use =COUNT(A1:A6) — it ignores non-numeric cells.
Right — COUNT only counts cells containing actual numbers, skipping the text entries.
Expected result in A7: 4
A1:A6 hold survey responses, a mix of numbers and text, with some cells left blank. Write a formula in A7 that counts every non-empty response regardless of type.
| A | |
|---|---|
| 1 | 5 |
| 2 | yes |
| 3 | |
| 4 | 3 |
| 5 | no |
| 6 | |
| 7 | ? |
Use =COUNTA(A1:A6) to count every filled cell.
Good — COUNTA counts any non-empty cell, whether it holds text or a number.
Expected result in A7: 4
B1:B5 hold grades where some students haven't been graded yet (blank cells). Write a formula in B6 that counts how many grades have actually been entered.
| A | B | |
|---|---|---|
| 1 | 90 | |
| 2 | ||
| 3 | 85 | |
| 4 | 77 | |
| 5 | ||
| 6 | ? |
Use =COUNT(B1:B5).
Right — COUNT only counts the cells that actually contain a numeric grade.
Expected result in B6: 3
A1:A5 hold order amounts, but one cell contains the text "cancelled" instead of a number. Write a formula in A6 that counts every non-blank entry, numeric or not.
| A | |
|---|---|
| 1 | 40 |
| 2 | cancelled |
| 3 | 60 |
| 4 | 25 |
| 5 | 80 |
| 6 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A7 hold a mix of prices and text placeholders like "TBD". Write a formula in A8 that counts only the actual priced items.
| A | |
|---|---|
| 1 | 12 |
| 2 | TBD |
| 3 | 8 |
| 4 | TBD |
| 5 | 15 |
| 6 | 20 |
| 7 | TBD |
| 8 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A6 hold attendance marks, some blank for days with no class. Write a formula in A7 that counts how many days have any record at all.
| A | |
|---|---|
| 1 | present |
| 2 | absent |
| 3 | |
| 4 | present |
| 5 | |
| 6 | present |
| 7 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →B1:B5 hold measurement readings, with a couple of cells still blank because the sensor hasn't reported yet. Write a formula in B6 that counts the completed numeric readings.
| A | B | |
|---|---|---|
| 1 | 22.5 | |
| 2 | ||
| 3 | 19.8 | |
| 4 | 25.1 | |
| 5 | ||
| 6 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A5 hold form field values, mixing text and numbers, with one field left empty. Write a formula in A6 that counts every filled field.
| A | |
|---|---|
| 1 | Alex |
| 2 | 30 |
| 3 | |
| 4 | yes |
| 5 | 5 |
| 6 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A6 hold product prices, with two cells containing the text "discontinued" instead of a price. Write a formula in A7 that counts only the still-priced products.
| A | |
|---|---|
| 1 | 15 |
| 2 | discontinued |
| 3 | 22 |
| 4 | 18 |
| 5 | discontinued |
| 6 | 30 |
| 7 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A5 hold a mix of IDs (numbers) and blank rows for unused slots. Write a formula in A6 that counts how many rows actually have data.
| A | |
|---|---|
| 1 | 101 |
| 2 | |
| 3 | 103 |
| 4 | 104 |
| 5 | |
| 6 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →