Search

Spreadsheet Mistakes 4 - Syntax

Updated: Oct 7


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:


#NAME?

  • 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


#NULL!

  • 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


#VALUE!

  • 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

17 views