Today's Goals

  • Understand the shape of a function: =NAME(arguments)
  • Use SUM, AVERAGE, MAX, MIN and COUNT for daily statistics
  • Know range references and the AutoSum button
  • Recognize the #DIV/0! and #NAME? errors

Key Concepts

1. What a function looks like

A function is a formula Excel wrote for you: =NAME(arguments). In =SUM(E2:E31), SUM is the name and E2:E31 is the argument — the cells to calculate. Names are case-insensitive; a suggestion list pops up while typing, and Tab completes it.

2. Five functions cover most daily needs

  • SUM adds: =SUM(E2:E31)
  • AVERAGE: the mean of a range
  • MAX / MIN: highest and lowest values
  • COUNT counts numbers only; COUNTA counts all non-empty cells

Counting people needs COUNTA: names are text, so COUNT returns 0. That is the definition working, not a bug.

3. Range vs whole-column references

Arguments take ranges like E2:E31. When rows keep growing, =SUM(E:E) sums the whole column — but any note numbers below the data get swallowed too. Keep the statistics block separate from the data and write exact ranges.

4. AutoSum

Select the empty cell under a column, press Alt+= (or Home → AutoSum): Excel guesses the range and writes SUM. Enter confirms. A wrong guess is fixed by editing the range inside the parentheses — the button saves typing, not thinking.

Today's Code

A 10-row score sheet: names in column A, scores in B2:B11.

Formula Result Purpose
=SUM(B2:B11) 785 total of ten scores
=AVERAGE(B2:B11) 78.5 average score
=MAX(B2:B11) 95 highest
=MIN(B2:B11) 52 lowest
=COUNT(B2:B11) 10 numbers counted
=COUNT(A2:A11) 0 names are text

Why: SUM adds B2 through B11; COUNT skips text like "Zhang San", so the last row returns 0 by definition.

Exercises

  1. Get the total, average, max and min of B2:B31.
  2. Count the names in A2:A31.
  3. Sum only B2:B6, then compare with =SUM(B:B) and explain the difference.

Exercise Solutions

  1. =SUM(B2:B31), =AVERAGE(B2:B31), =MAX(B2:B31), =MIN(B2:B31) — one range shared by all four.
  2. =COUNTA(A2:A31). COUNT gives 0 — see Key Concept 2.
  3. The whole-column sum includes the total you just wrote below it, so it runs high. Write exact ranges or park statistics away from data.

Real-World Scenario

HR totals monthly meal allowances. D2:D41 holds amounts for 40 people; four report cells are due: total, average, max, headcount.

excel
=SUM(D2:D41)
=ROUND(AVERAGE(D2:D41),1)
=MAX(D2:D41)
=COUNTA(A2:A41)

ROUND(…,1) keeps one decimal: 87.33333 becomes 87.3. Once the four cells are built, pasting next month's data into the same range updates everything — build once, reuse monthly.

Common Errors and Troubleshooting

Symptom Cause Fix
#DIV/0! AVERAGE range holds no numbers Check which rows got selected
#NAME? Misspelled name like SUMM/AVG, or full-width parentheses Pick from the suggestion list; half-width only
Sum returns 0 Numbers stored as text (green triangles) Click the warning icon → Convert to Number
Result runs too large Range swallows the total or notes row Exact ranges; separate stats from data

Extension Exercises

  1. Find the minimum score with MIN, then Ctrl+F that value to locate its row.
  2. Add a "difference from average" column: put the average in B12, type =B2-$B$12 and drag. Watch the $ lock B12 while B2 walks down — absolute referencing, used heavily by VLOOKUP next lesson.

Summary

Functions are named formulas; arguments decide what gets calculated. SUM, AVERAGE, MAX, MIN and COUNT(COUNTA) cover most daily statistics; AutoSum is Alt+=. For errors: #NAME? means spelling or punctuation, #DIV/0! means no numbers in range. Next lesson: VLOOKUP.

From Online Toolbox (www.vba.net)