A Google Sheets formula shows an error when it cannot return a result: #DIV/0! means it divided by zero, #N/A from VLOOKUP means the value was not found, #VALUE! often means text where a number was needed, and #REF!, #NAME? and #NUM! flag other problems. Click the cell, check the formula and the cells it uses, or wrap it in IFERROR. ProfitDrawer's bookkeeping spreadsheets use IF and IFERROR tests so empty rows show blanks, not errors.
Error values Sheets uses (ERROR.TYPE returns these numbers):
| Error | No. | Common cause |
|---|---|---|
| #NULL! | 1 | other error |
| #DIV/0! | 2 | the divisor is 0 |
| #VALUE! | 3 | wrong type of input, for example text given to DATE |
| #REF! | 4 | a reference problem |
| #NAME? | 5 | a name Sheets does not recognize |
| #NUM! | 6 | a number out of range, for example a year above 10,000 in DATE |
| #N/A | 7 | a lookup value was not found |
Fixing it:
- Click the cell and read the formula in the formula bar. Referenced cells are highlighted in color.
- Check that those cells hold numbers or dates, not text, and that lookup values are spelled the same.
- If Sheets shows a Formula correction box, click Accept or Dismiss.
Hide expected errors: =IFERROR(B2/C2, "") shows a blank instead of an error. Google notes IFERROR catches every error, so make sure that is what you want; it can hide real mistakes. IF(C2=0,"",B2/C2) handles only the divide-by-zero case.
Source: ERROR.TYPE function: Google Docs Editors Help · Updated
Tools and spreadsheets for this
Related questions
- How do I use VLOOKUP or XLOOKUP for a price list?
- How do I calculate a percentage in a spreadsheet?
- How do I copy a formula down a whole column?
- How do I use SUMIF to total expenses by category?
- Do I need to be good with spreadsheets to use ProfitDrawer files?
- How do I open an Excel file in Google Sheets?
All questions on ProfitDrawer Answers →
General information, not tax, legal or financial advice. Check the rules for your state and your situation.