Core Functions

Chapter 1 previewed =SUM(A1:A10) just long enough to introduce ranges. This chapter is where functions stop being a preview and become the main event — the handful of functions covered here account for a huge share of everything a typical Excel user ever actually writes, and nesting them inside one another is how simple building blocks turn into genuinely useful logic.

What a Function Actually Is

A function is a named, pre-built calculation: a name (like SUM), followed by parentheses containing one or more arguments — the inputs the function needs to do its job — separated by commas. =A1+A2+A3 and =SUM(A1:A3) can calculate the exact same result, but the function version scales: adding 3 numbers with + is fine, adding 300 is not.

SUM — Adding a Range (or Several)

SUM adds up every numeric value in whatever you give it. A single range is the most common case, but you can pass several ranges and individual cells at once, separated by commas:

SUM silently ignores text and empty cells within its range rather than raising an error — it only adds up whatever it finds that's genuinely numeric.

AVERAGE — the Mean of a Range

The division is by the count of numeric values found, not by the total number of cells in the range. If B2:B8 (7 cells) contains only 5 actual numbers and 2 blank cells, AVERAGE divides by 5, not 7 — the blanks are excluded entirely rather than treated as zero. This is a common source of confusion, since it means a blank cell and a cell containing the number 0 genuinely produce different averages.

COUNT vs. COUNTA — Two Different Questions

These two functions look almost identical but answer different questions, and mixing them up produces confidently wrong results:

A practical rule of thumb: use COUNT when you specifically care about numeric entries (e.g. "how many students actually submitted a numeric score"), and COUNTA when you care about entries existing at all, regardless of type (e.g. "how many students entered anything, including a text note like 'absent'").

IF — Excel's Conditional Logic

IF takes three arguments: a condition to test, what to return if that condition is true, and what to return if it's false.

Any of Excel's comparison operators can be used as the condition: =, <> (not equal), >, <, >=, <=. The true/false results don't have to be text — they can be numbers, or even other formulas.

Nesting Functions Inside One Another

A single IF only distinguishes two outcomes. Real grading needs more than pass/fail — putting a second IF inside the "false" branch of the first lets you test a second condition only when the first one didn't match, building up as many bands as you need:

Read this from the outside in: "if B2 is 90 or above, A. Otherwise (so we already know B2 is below 90), check whether it's 80 or above — if so, B. Otherwise (below 80), check 70 or above — if so, C. Otherwise, F." Each nested IF only ever runs when every earlier condition already failed, which is exactly why the thresholds can be checked in descending order without a band overlapping the one before it.

Functions can nest into each other's arguments generally, not just IF inside IF — a SUM can be one of IF's results, an AVERAGE can be tested by a comparison, and so on. The rule is always the same: whatever the inner function returns becomes one plain value the outer function then uses, exactly as if you'd typed that value in by hand.

Hands-On Exercises