- Practice
- About 12 min
- 5 practice questions
Spot the mistake: error values and common formula slips
Match error values to their causes and find the slip in a faulty formula: missing signs, wrong ranges, text numbers and bad function names.
If something looks wrong, please tell us.
Some questions show a wrong result and ask why. The answer is always one of a short list of causes. Learn the error values first, then the quiet mistakes that give wrong answers without any error message.
Error values and their usual causes
| Shown | Usual cause | Example |
|---|---|---|
| #DIV/0! | Dividing by zero or an empty cell | =A1/B1 where B1 is 0 |
| #NAME? | A function or name not recognised | =SUMM(A1:A3) instead of SUM |
| #VALUE! | Text where a number is needed | =A1+A2 where A2 holds the word ten |
| #REF! | A deleted or impossible cell reference | A column used by the formula is deleted |
| #N/A | A lookup finds no match | VLOOKUP for a code that is absent |
| #### | Column too narrow for the number | A wide number in a narrow column |
Quiet mistakes with no error message
- Missing equals sign: 5+3 typed without = is stored as text and shows 5+3.
- Range one row short: =SUM(A1:A4) when the data ends at A5 leaves the last item out.
- Numbers stored as text, shown left-aligned: SUM ignores them, so the total looks too small.
- Missing brackets: =A1+A2/2 divides only A2, but the average needs =(A1+A2)/2.
- Wrong reference type: a rate typed as C1 instead of $C$1 shifts when copied down.
A debugging routine
- Read the error value and match it to its cause in the table.
- Click the cell and read the formula in the formula bar, one reference at a time.
- Check that every referenced cell holds what you expect: a number, not text; the right row, not the one above.
- Test the formula on a tiny case whose answer you already know, then correct and re-check.
Worked examples
Example 1. A student types =SUMM(B2:B6) and sees #NAME?. The function name is misspelt. Correcting it to =SUM(B2:B6) removes the error.
Example 2. The cell C2 holds =A2/B2 and shows #DIV/0!. B2 holds 0, so division by zero is the cause. A guard such as =IF(B2=0,"",A2/B2) shows a blank instead of the error.
Example 3. A list of 5 numbers 10, 20, 30, 40, 50 lies in A1:A5 but the total formula is =SUM(A1:A4). It returns 100 instead of 150, with no error message. The range is one cell short, and the right formula is =SUM(A1:A5).
Example 4. Two subject marks, 60 and 80, are in A1 and A2. The formula =A1+A2/2 gives 60 + 40 = 100, which is not an average. The brackets fix it: =(A1+A2)/2 gives 140 / 2 = 70.
Example 5. The marks 50, 40 and 30 are in A1:A3, but the 40 was typed as text and is left-aligned. =SUM(A1:A3) shows 80, not 120, because SUM ignores the text entry. After the cell is converted to a number, the total becomes 50 + 40 + 30 = 120.
Example 6. Typing =A1+1 into cell A1 itself creates a circular reference, and the program warns that the formula refers to its own cell.
Example 7. A2 holds the word ten and A1 holds 5. =A1+A2 shows #VALUE!, because arithmetic cannot use a word. =SUM(A1:A2) gives 5 instead, because SUM skips the text. The two formulas treat the same cells differently, which is why a wrong total can appear without any error value.
Read the error code first; if there is none, check the equals sign, the brackets and the range.
Key takeaways. Each error value points to one kind of cause. When no error shows but the answer is wrong, suspect a short range, missing brackets or numbers stored as text.
Check your understanding
-
1. A formula shows #DIV/0!. The most likely cause is:
- A misspelt function
- A deleted column
- Text in a number cell
- Division by zero
Show answer
Answer: D. #DIV/0! means a division by zero or by an empty cell.
-
2. =SUMM(B2:B6) shows an error. Which one?
- #VALUE!
- #NAME?
- #REF!
- #N/A
Show answer
Answer: B. The program does not recognise the function name SUMM, so it shows #NAME?.
-
3. A1:A5 hold 10, 20, 30, 40, 50 but =SUM(A1:A4) is typed. What does it return?
- 150
- 90
- 100
- 50
Show answer
Answer: C. Only the first four values are added: 10 + 20 + 30 + 40 = 100. The last value is left out.
-
4. A1 = 60 and A2 = 80. What does =A1+A2/2 return?
- 100
- 70
- 140
- 90
Show answer
Answer: A. A2/2 = 40 is done first, then 60 + 40 = 100. The brackets in =(A1+A2)/2 would give 70.
-
5. Which error appears when a lookup cannot find a match?
- #NAME?
- #DIV/0!
- ####
- #N/A
Show answer
Answer: D. #N/A means a value is not available, as when a lookup finds no match.
Sign in to keep track of the lessons you finish.