Module 02: Make formulas work for you
Choose the right total, average, and count
Discover how blanks, zero, and text affect numeric summaries.
Windows desktop: Excel 2016, 2019, 2021, 2024, and Microsoft 365. Mac/web menus and shortcuts differ. No dynamic arrays required.
Useful first: Build your first formulas from cell references · Copy formulas while keeping shared inputs fixed
- Use SUM, AVERAGE, COUNT, and COUNTA for different questions.
- Predict the effects of zero, empty cells, and numeric text in a referenced range.
- Reconcile a numeric summary with the number of expected records.
Small data. A result you can check.
Format B7 as Text before pasting at A1. Leave B5 empty and retain its row. The final 30 must initially remain text.
View the raw practice data
Day Amount Day 1 10 Day 2 0 Day 3 20 Day 4 Day 5 pending Day 6 30
01Prepare a deliberately imperfect amount column
Create a Summary sheet. Format only B7 as Text before pasting at A1. B2:B4 are numeric 10, 0, 20; B5 is empty; B6 contains pending; B7 contains text 30.
Do not tidy this deliberately imperfect input yet. A plausible total can omit an amount stored as text. First discover which values contribute.
| Cell | Content | Type or meaning |
|---|---|---|
| B2 | 10 | Number |
| B3 | 0 | Number, genuine zero |
| B4 | 20 | Number |
| B5 | Empty | No entry |
| B6 | pending | Text status |
| B7 | 30 | Text, intentionally not numeric |
02Ask four different questions
Enter the four formulas in D2:D5. SUM totals numeric cells; AVERAGE divides their sum by their count. COUNT counts numbers; COUNTA counts cells containing information.
In this referenced range, SUM, AVERAGE, and COUNT ignore text 30 and pending but include genuine zero. The total is 30 across three numbers, so the average is 10. COUNTA is five.
| Output cell | Formula | Expected result |
|---|---|---|
| D2 | =SUM(B2:B7) | 30 |
| D3 | =AVERAGE(B2:B7) | 10 |
| D4 | =COUNT(B2:B7) | 3 |
| D5 | =COUNTA(B2:B7) | 5 |
=AVERAGE(B2:B7)Enter in D3. It averages numeric 10, 0, and 20, not all six source rows.
03Decide what a missing value means
Missing is not zero. B5=0 changes the initial count to four and average to 7.5; total stays 30. If you try this, clear B5 before continuing.
COUNTA also counts formulas returning empty text. Function behavior with directly typed arguments can differ from referenced cells; this lesson uses ranges only. Investigate error cells rather than assuming every summary function ignores them.
04Repair only what the source confirms
If the source confirms that B7 is thirty, change it to General and re-enter 30 as a number. Formatting alone is insufficient. D2:D5 become 60, 15, 4, and 5.
Keep unresolved rows visible. COUNT shows four numeric observations out of six records. Keep summaries outside B2:B7 and inspect AutoSum's proposed range.
Reconcile the initial summary, then apply two explicitly justified changes.
- Prepare B7 as Text before pasting, then enter the four formulas in D2:D5.
- Record the initial outputs and explain which cells are excluded.
- Convert only B7 to numeric 30 by changing to General and re-entering the value.
- For a separate what-if test, assume B5 is confirmed zero and enter 0 there.
Show the worked answer
Initially: SUM 30, AVERAGE 10, COUNT 3, COUNTA 5.
After correcting B7: SUM 60, AVERAGE 15, COUNT 4, COUNTA 5.
After additionally entering confirmed zero in B5: SUM 60, AVERAGE 12, COUNT 5, COUNTA 6.
The text pending in B6 still does not contribute to the numeric total or average.
Check your work- You can identify every cell included in each calculation.
- A blank is changed to zero only when its meaning supports that action.
- All four outputs agree with independent arithmetic and counts.
One question before you move on.
Ready for the next step?
Mark this lesson when you can explain the idea and reproduce the practice result.
Your checklist stays on this browser.Reference notes
These lessons use original examples. Check Microsoft’s documentation for details and platform-specific options.
Prepared September 13, 2026 · Column Harbor editorial team. Menu locations and available functions can vary by Excel version.