Formulas and functions

A function is used inside a formula, so the two are not alternatives: =SUM(A1:A10)*0.18 is a formula that contains a function.

Totalling A1 to A10.

As a formula:

=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10

As a function:

=SUM(A1:A10)

Why the function is better. It is shorter and readable; it adjusts automatically when rows are inserted inside the range, whereas the long formula silently omits the new row; it ignores text and blanks instead of failing; and it scales — the same nine characters total ten cells or ten thousand.

• COUNT counts cells containing numbers only. Text, blanks and logical values are ignored.
• COUNTA counts cells that are not empty — numbers, text, dates, logical values, even a formula returning an empty string.
• COUNTBLANK counts cells that are empty.

Applying them to the range.

$$\text{=COUNT(A1:A6)}=2\text{=COUNTA(A1:A6)}=4\text{=COUNTBLANK(A1:A6)}=2$$

Note that COUNTA and COUNTBLANK together account for every cell in the range:

$$4+2=6✓$$

The examinable trap. To count students in a column of names, COUNT returns $0$, because names are not numbers. COUNTA is the correct function. Conversely, to count how many students actually sat an examination from a column of marks with blanks for absentees, COUNT is correct — it excludes the blanks automatically.

The IF function tests a condition and returns one of two values.

$$\text{=IF(logical\_test, value\_if\_true, value\_if\_false)}$$

• logical_test — any expression giving TRUE or FALSE, such as B2>=40
• value_if_true — returned when the test succeeds
• value_if_false — returned when it fails

Text results must be in quotation marks; numbers must not.

(a) One condition.

=IF(B2>=40,"Pass","Fail")

Note >=, not > — a student with exactly $40$ passes.

(b) Two conditions together. Combine with AND, which is TRUE only when every condition is TRUE:

=IF(AND(B2>=40,C2>=40),"Pass","Fail")

Use OR instead if passing either subject would suffice.

(c) Four bands — a nested IF. The false slot holds another IF, and the tests must run from the highest boundary downwards:

=IF(B2>=80,"A",IF(B2>=60,"B",IF(B2>=40,"C","F")))

Tracing $B2=65$: $65\ge 80$ FALSE → $65\ge 60$ TRUE → returns "B" and stops.

Why the order matters. Written from the bottom up — =IF(B2>=40,"C",IF(B2>=80,"A",…)) — a mark of $95$ satisfies the very first test and returns "C". Every mark above $40$ would be graded C. Descending order is required, not preferred.

Why no upper bound is needed. Once the first test has failed, the value is known to be below $80$, so AND(B2>=60,B2<80) adds nothing.

A cleaner alternative for many bands is IFS:

=IFS(B2>=80,"A",B2>=60,"B",B2>=40,"C",TRUE,"F")

VLOOKUP — vertical lookup — searches for a value down the first column of a table and returns a value from the same row of another column. It is how one sheet fetches data held in another, replacing manual copying.

$$\text{=VLOOKUP(lookup\_value, table\_array, col\_index\_num, [range\_lookup])}$$

1. lookup_value — what to search for. A value, a cell reference, or text in quotation marks.

2. table_array — the range to search. Its first column must be the one containing the lookup value; VLOOKUP cannot look leftward. Normally made absolute ($H$2:$J$20) so the formula can be filled down without the table drifting.

3. col_index_num — which column of the table to return, counted within the table starting at $1$ for its first column. If the table is H2:J20, then H is $1$, I is $2$, J is $3$ — not $8$, $9$, $10$.

4. range_lookup — the kind of match, and the argument that matters most.

TRUE against FALSE.

Why FALSE is usually safer. Two reasons, and the second is the serious one.

1. Most lookups are of identifiers, where an "approximate" item code or roll number is meaningless. There is no such thing as nearly the right student.

2. An approximate match on unsorted data fails silently. It does not report an error; it returns a value — just the wrong one. VLOOKUP with TRUE assumes ascending order and effectively stops as soon as it passes the target, so on unsorted data it returns whatever happened to be there. A plausible-looking wrong price is far more dangerous than a visible #N/A, because nobody investigates it.

Since TRUE is the default when the argument is omitted, the fourth argument should always be written explicitly.

Handling a genuine miss:

=IFERROR(VLOOKUP(B5,$H$2:$J$20,3,FALSE),"Not found")

When a lookup fails on a value that is visibly present, the cause is almost always invisible: a trailing space (fix with TRIM) or a number stored as text in one of the two places.

1. #DIV/0! — division by zero, or by a cell that is empty (an empty cell counts as zero in arithmetic).
Common case: =B2/C2 where C2 has not yet been filled in.
Remedy: guard the formula — =IF(C2=0,"",B2/C2) or =IFERROR(B2/C2,"").

2. #VALUE! — an argument of the wrong type, typically arithmetic on text.
Common case: =A1*2 where A1 contains "10 kg", or a number imported as text.
Remedy: find the text entry — it will be left-aligned in a column of right-aligned numbers — and correct it, or clean it with VALUE or TRIM.

3. #REF! — the reference points to a cell that no longer exists, because its row, column or worksheet was deleted.
Common case: deleting a column that a formula elsewhere depended on.
Remedy: the original address is unrecoverable, so the reference must be rewritten. Undoing the deletion immediately restores it.

4. #NAME? — Excel does not recognise a name in the formula: a misspelled function (=SUME(A1:A5)), text left unquoted (=IF(A1>5,Pass,Fail)), or an undefined range name.
Remedy: correct the spelling, and put quotation marks round text. Choosing the function from the drop-down list as you type prevents this entirely.

5. #N/A — "not available": a lookup found no match.
Common case: VLOOKUP for a code absent from the table, or present but with a trailing space, or stored as text on one side and as a number on the other.
Remedy: verify the value exists, clean it with TRIM, and wrap the lookup in IFERROR for the genuinely missing cases.

6. #NUM! — a numeric value that is impossible or out of range.
Common case: =SQRT(-4), or an iterative function that fails to converge.
Remedy: check the argument; guard with IF(A1>=0,SQRT(A1),"").

Also: #NULL! — an invalid range operator, usually a space typed instead of a colon (=SUM(A1 A5)); and #####, which is not an error at all but merely a column too narrow to display its number.

Two general points. Errors propagate — any formula referring to an error cell shows an error too, so one bad cell can spoil an entire report; and Formulas → Error Checking, with Trace Precedents and Evaluate Formula, is the quickest way to find the cell where the trouble started.

The cause: AVERAGE ignores blank cells entirely — it does not treat them as zero.

Let the eight marks that were entered total $496$.

What AVERAGE computes. It sees eight numeric cells and divides by eight:

$$\frac{496}{8}=62$$

What the teacher expected. That an absentee scores zero, so the divisor should be the full ten students:

$$\frac{496+0+0}{10}=\frac{496}{10}=49.6$$

Both figures are arithmetically correct. They answer different questions, and the spreadsheet cannot know which was meant:

• $62$ is the average mark of those who sat the examination
• $49.6$ is the average over the whole class, counting an absentee as zero

The formula for each.

1. Average of those who appeared — the default behaviour, and correct if a blank means "no data":

=AVERAGE(B2:B11)

2. Average over all ten students, absentees counting as zero. Divide the total by the full count rather than by the count of numbers:

=SUM(B2:B11)/COUNTA(A2:A11)

using the names column, which has ten entries, for the divisor. Or, if the class size is fixed:

=SUM(B2:B11)/10

Note that =SUM(B2:B11)/COUNT(B2:B11) would not work — COUNT also ignores blanks and returns eight, reproducing the $62$.

Which is right, and how to make the sheet say so. For most academic purposes the two are genuinely different statistics and both are worth reporting. Ambiguity is the real fault here, and it should be removed from the data rather than patched in the formula:

• If an absentee's mark is zero, enter $0$. Then AVERAGE gives $49.6$ by itself, and every other function agrees.
• If the mark is genuinely unknown — the student will sit a supplementary paper — leave it blank, and label the column heading "Average (present only)" so no reader is misled.
• Use a distinct marker such as "AB" if absence must be visible on the sheet. Being text, it is ignored by AVERAGE exactly as a blank is, but a reader can see why.

The general lesson. A blank cell is an absence of data, not a zero, and Excel's functions treat it that way consistently. Whenever a blank actually means something — zero, not applicable, not yet known, absent — that meaning must be recorded explicitly, because no formula can recover an intention that was never written down.