# Logical Errors

In my __blog post about spreadsheet syntax errors__, I explained that a syntax error occurs when your function is incorrect.

If your error is not a syntax error, it is a logical error. There are many types of logical errors, all of which can be prevented. Here is a list of logical errors that can occur in your spreadsheet.

*Image by *__Elisa Riva__* from *__Pixabay__

**Cell Reference**

You reference the wrong range OR

You reference only a portion of the needed data

NOTE: It is a syntax error if you reference a range that does not exist. When this happens, you will get a #REF! error

**Order of Precedence Not Followed**

Spreadsheet programs interpret how to calculate a formula by following the order of precedence shown in the following table.

If your formula results in an incorrect answer, you have not followed the order of precedence.

**Computation Components**

Performing calculations on data which do not agree

**Hardcoded Numbers**

You use a number instead of a cell reference in a formula

**Disbursed Variables / Assumptions**

You have your variables and assumptions scattered throughout the spreadsheet instead of consolidating them all on one worksheet tab.

**Readability**

You want the user to be able to quickly understand your spreadsheet and the point you are trying to make. If you don't the user will draw the wrong conclusion.

Simplify the content to be reviewed

People don’t have time to decipher and review spreadsheets so point people to the outcomes they have to analyze

**Hidden Sheets / Columns**

If you hide a sheet, column, or row as a security measure, to conceal sensitive information – DON’T!

It’s not secure

Hidden items can be changed by any user edits

It is better to hardcode the data and send the user a duplicate of the worksheet

**Lack of Formula Checks**

You should always calculate the answer for critical data in different ways in different places in your spreadsheet

Use a checksum to ensure your formula is correct

**Complex Formulas**

Avoid complex formulas - they are difficult to understand

Complex formulas increase the likelihood of logical - and syntax - errors

Instead of complex formulas, use multiple simple formulas with helper columns

Resist the urge to create heavily nested lengthy formulas

Use named ranges to ensure you use the correct data and so your formula is much easier for the user to understand

**Comment Sparingly**

If you label everything, then nothing is important

**Duplication Without Inspection**

Review your worksheet before you make copies

If your workbook has an error, then you only create and worsen a problem when you distribute copies

Have someone else review your file

**Other Practices Best Avoided to Ensure Simplicity and Understanding**

Not Using Named Ranges or Built-In Functions

Using VBA When Formulas Will Suffice

Too Many Columns in One Worksheet

Using Copy / Paste Instead of Connecting the Spreadsheet to the Data Source

File Names / Worksheet Names Don’t Make Sense

File Not Documented in File Properties

Not Understanding the Business Issues