Spreadsheet Mistakes 4 - Syntax
Updated: Oct 7, 2020
Syntax errors occur when a function is incorrect. Functions are formulas built into the spreadsheet - SUM(), AVERAGE(), IF(), VLOOKUP().
You tell functions what to do by entering arguments between the parentheses. This is the function syntax:
=[Function Name](Argument 1, Argument 2, Argument 3...)
Arguments must be entered in the correct order or you will have an error.
Some arguments are optional. However it is best practice to specify even the optional arguments. If you do not, the spreadsheet will assign a default value. The default value may not be what you want. If so, you will have an error.
Spreadsheets send you a message when you have an error. These are the syntax error messages and the reasons why they occur:
Misspelled formula
Formula does not exist
Misspelled references and ranges
Text entered without enclosing double quotation marks
Range defined without a colon
#REF!
Invalid cell reference
Copy / paste cells above the cells referred by the formula, thus making an invalid cell reference
#DIV/0!
You are trying to divide a number by zero
You left the divisor cell empty
#N/A
You passed an unavailable value to the formula
Data is missing
Inappropriate lookup value being passed to a LOOKUP function
N/A or NA is part of the data set
You provide independent ranges – ranges that do not intersect – to the formula
Cell ranges are defined improperly (Example, missing a colon or using a space instead of mathematical operator)
#NUM!
You have a value – positive or negative – that is too large for Excel to handle
You passed the wrong data or data type to a function
The variables specified to a function are the wrong type
Example: You entered text instead of a number
########
The column is not wide enough to accommodate a large value
The result of your formula is too wide to fit in the cell
You have formatted a negative number as a date or time value, both of which are always supposed to be positive values.
Next: Spreadsheet Mistakes 5 - Miscellaneous
Previous: Spreadsheet Mistakes 3 - Formatting, Functions & Formulas