0 comments
📌 Error messages that exist in Excel
When a formula cannot be calculated correctly, Excel displays an error code. Below are the most common errors:
Ad
| Error | Meaning | Most common cause |
|---|---|---|
| #N/D | Value not available | VLOOKUP don't find a value. |
| #VALUE! | Value Error | Text in a mathematical equation. |
| #DIV/0! | Division by Zero | =A1/0 ou empty cell. |
| #NAME? | Name Error | Digitation error (ex: =SUME insteady of =SUM). |
| #REF! | Reference Error | Cell removed or invalid reference. |
| #NUM! | Number Error | Number too large or impossible (ex: =SQRT(-1)). |
| #EMPTY! | Spill Error | Interval don't use matrix |
| ####### | Cell too small to appear value | Length of the cell not big enough |
| #DUMP! | Not enough space for the result | Something is written bellow the cell |
🔍 How to Interpret and Correct Errors?
#N/A (Value not found)
Cause: Functions such as VLOOKUP, HLOOKUP, or INDEX/MATCH do not find the value you are looking for.
Solution: Check if the value exists in the table.
Use IFERROR to display a custom message:
=IFERROR(VLOOKUP(A1, B1:C10, 2, FALSE); "Not found")
#VALUE! (Incompatible data)
Cause: Attempting to use text in mathematical operations or functions.
Solution: Check if the data is in the correct format.
Use ISNUMBER(), ISTEXT() or ISBLANK() to validate before calculating.
DIV/0! (Division by zero)
Cause: Division where the denominator is zero or empty.
Solution: Use IF to avoid division:
=IF(B1=0; "Error"; A1/B1)
#REF! (Invalid reference)
Cause: Deletion of a referenced cell or incorrect copying of formulas.
Solution: Check references in the formula. Use defined names to avoid broken references.
#NUM! (Number problem)
Cause: Invalid numbers (e.g. square root of negative).
Solution: Use IF to validate:
=IF(A1>=0; ROOT(A1); "Invalid")
🛠 Functions to Handle Errors in Excel
| Function | Use |
|---|---|
| IFERROR() | change any error for a value. Ex: =IFERROR(A1/B1; 0) |
| IF() | Test a condition before executing the formula Ex: =IF(B1>0; A1/B1; "Invalid") |
| ISERROR() | Verify if cell have error. Ex: =ISERROR(A1) → TRUE or FALSE. |
| ISNUMBER() | Verify if cell is a number. Ex: =ISNUMBER(A1) → TRUE or FALSE. |
| ISBLANK() | Verify if cell is empty. Ex: =ISBLANK(A1) → TRUE or FALSE. |
Exercises
| Product | Price | Stock | Sales |
|---|---|---|---|
| Apple | 5,00 | 10 | 8 |
| Banana | 3,00 | 0 | 5 |
| Orange | 4,00 | 15 | 12 |
| Avocado | 3,50 | 0 | 0 |
| Watermelon | Erro | 5 | 3 |
1. Calculate the unit selling price (Price / Sales). Handle error if Sales = 0.
2. Calculate availability in %(Inventory / (Inventory + Sales)). Check the new column for errors.
3. Search for "Pear" and if it does not exist, return "Does not exist".
4. Calculate total revenue (Price times sales), check if it is number before multiplying.
Sign in and join the conversation
— Comments0
Be the first to comment